Google Sheets ImportXML Issues – Troubleshooting ImportXML Function

google sheetsimportxml

My goal is to retrieve the "Clôture de la veille" number (90,050 €) from this webpage : https://www.easybourse.com/action-cours/renault/intraday/FR0000131906-25

In Google Sheets, when using the IMPORTXML formula, it returns 0€ instead of 90,050€. Here is my formula: =value(IMPORTXML("https://www.easybourse.com/action-cours/renault/intraday/FR0000131906-25","//span[@id='cours_dernier_veille']"))

Can you please explain to me why Google Sheets is returning a wrong value?

Best Answer

IMPORTXML is only able to read values on the source code of the page but the value you are looking for isn't there, it's added by something that modify the DOM (document object model).

Related Solutions

Google Sheets – ImportXML with XPath Not Returning Table Entry

It's because of <tbody>. There is no <tbody> in the HTML source. Browser puts it there (on its own; nothing to do with JavaScript on the page) because it believes it should be there, according to HTML spec.

Just remove /tbody from the path:

=IMPORTXML( "https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/keyStatistics.jhtml?stockspage=keyStatistics&symbols=aapl" , "//div[@id='audit-integrity'][1]/table/tr[10]/td[2]")

And in general: when XPath doesn't work, debug by removing all of the selectors, then adding one by one until the formula breaks...

Google Sheets – How to Cast Cell Value from Object to Number

Short answer

How can I convert a cell value to a number?

On Google Sheets durations are just a display format for date values, so when a custom function takes a duration it's treated as a date object.

Explanation

Doing arithmetic operations

When we are doing arithmetic operations on dates/times/duration, we should bear in mind the following:

Google Sheets
- Use day as the unit
- Use December 30, 1899 0:00:00 as the zero.
- Doesn't handle timezones, but spreadsheets has a timezone setting.
Google Apps Script / JavaScript
- Use milliseconds as the unit
- January 1, 1970 0:00:00 in UTC as the zero.
- Date object includes timezone.
- The Script project has a timezone setting.

Display format

Google Sheets
- Has a duration format ([h]:mm:ss.00).
Google Apps Script / JavaScript
- Has the Utilities Service and formatdate method.
- Hasn't a built-in duration format.

Transfer of dates between Google Sheets and Google Apps Script

When a date value is transferred from Google Sheets to Google Apps Script or vice versa it is automatically converted considering the timezone of the spreadsheet.

Solution

As Google Sheets and Google Apps Scripts duration/date handling is different the simplest solution is to do the conversion from date/duration to number on the origin application.

On Google Sheets we could use TO_PURE_NUMBER to do this conversion:

=daysHrsMins(TO_PURE_NUMBER(A1))

For certain use cases it could be convenient to use a JavaScript library like moment.js

References

Related Q&A from this site:

Related Q&A from Stack Overflow

How can I do time/hours arithmetic in Google Spreadsheet?