1

Original Title:

How to use Google Sheets formula to import data (using e.g. IMPORTXML) without dropping off characters in results?

Google Sheet example in thrid update below.

I currently use the formula below to pull the price of a cryptocurrency from the Coingecko website. The formula works fine with prices that are not too long. However, this cryptocurrency has a very low and hence long value. Long as in the number of characters.

E.g. the price currently is $0.000000000086801 (18 characters)

However using the IMPORTXML it shows $0.000000000087 (15 characters)

In other words, it's missing the three digits at the end of the price. I'm guessing this might be related to some limitation of displaying a maximum of 15 characters. Though I would've thought that's only applicable to number values — IMPORTXML is displaying the prices as text.

Is there a solution or workaround using IMPORTXML (or another formula/way) to enable Google Sheets to display the full/exact price as shown on the website without dropping off characters/digits?

Formula: =IMPORTXML("https://www.coingecko.com/en/coins/nekocoin", "//div[contains(@data-controller,'coins-information')]//span[contains(@data-coin-symbol,'nekos')]")

Update 1

The formula below manages to pull text longer than 15 characters (albeit including spaces) so not sure if this is an issue related to a character limit...

=IMPORTXML("https://www.coingecko.com/en/coins/bnb","//h2[@class='tw-text-lg px-3 md:tw-text-2xl tw-font-bold tw-text-gray-900 dark:tw-text-white dark:tw-text-opacity-87']")

Returns the result: "BNB Price Statistics" (20 characters including spaces)

However, the problems still stands when pulling the price.

Update 2

It's very possible to display a long number as a text. So if IMPORTXML is showing a text result by default, it shouldn't have a digit limit...

numerical cell value formatted as text has no digit limit

Update 3

Google Sheet below is editable. I've already added a suggested solution but no luck so far.

https://docs.google.com/spreadsheets/d/1KdXXGskUhFzH4NvRiCOA8ft_-Vl6NPjh1uGJzO15w4A/edit?usp=sharing

Update 4 Following brief discussion with @player0, it seems IMPORTXML was pulling a shorter price as it pulls data from non-javascript version of the webpage. This explains why it imported a text with character limitations.

Having checked this question here (ImportXML - Javascript? Imported content is empty) and this question here (Google Sheets importXML Returns Empty Value) it doesn't seem possible. Though there might be some sort of workaround? (What is the JavaScript equivalent of ImportXML?)?

It might be easier to just find a website that produces the price on the non-JS version of their webpage.

Rubén
  • 34,714
  • 9
  • 70
  • 166
questioner56
  • 13
  • 3

2 Answers2

1

try:

=REGEXEXTRACT(IMPORTXML("https://crypto.com/price/nekocoin", 
 "//h2/span"), "\d+.\d+")
player0
  • 124,011
  • 12
  • 67
  • 124
  • 2
    Thank you once again player0! I'm now marking your updated answer as correct – questioner56 Jul 02 '22 at 08:45
  • your other version that worked: ```=REGEXEXTRACT(IMPORTXML("https://crypto.com/price/nekocoin", "/html/body/div[1]/div[3]/div/div/div[3]/div[1]/div[1]/div[1]/div/div[1]/h2/span"), "\d+.\d+")``` – questioner56 Jul 02 '22 at 10:49
-1

parseFloat of string longer than 16 characters i think it is problems for 17 character itself on javascript, the library inherit this property too

anh phan
  • 21
  • 6
  • Thank you Anh Phan, can you show/explain how this would work in Google Sheets? I couldn't find a ParseFloat formula...is this done using App Script, if yes, can you give an example? – questioner56 Jul 01 '22 at 15:28
  • @questioner56 https://forum.openoffice.org/en/forum/viewtopic.php?t=34043 i think the open office use the double type for floating point and the function you are calling use that type too. it is the limit of the type so you need the bigger type for percision and not searching forum for that problems yet but you can try – anh phan Jul 01 '22 at 16:55
  • I've just tested using IMPORTXML on text that's longer than 15 characters and it manages to show it. ```=IMPORTXML("https://www.coingecko.com/en/coins/bnb","//h2[@class='tw-text-lg px-3 md:tw-text-2xl tw-font-bold tw-text-gray-900 dark:tw-text-white dark:tw-text-opacity-87']")``` – questioner56 Jul 01 '22 at 20:29
  • @questioner56 it is about the type of object which it store in memory. The type have limit and function which return the type int will have limit of int type, same for other type. Is there away to convert text to number in google sheet, try to use but i am guessing that it will return the same result. – anh phan Jul 02 '22 at 06:38
  • Thank you Anh Phan for your suggestion. Maybe I should highlight that IMPORTXML results are already formatted as text by default. That's why in the first instance, this character/digit limit issue shouldn't be happenening in the first place. I've even used TEXT formula (to convert to text, although it already is text) but it doesn't resolve the issue. It seems almost as if the digits were dropped off during import not display. – questioner56 Jul 02 '22 at 07:17