Google-sheets – Google Sheets API call creates scientific numbers that I cannot then run calculations on

google sheetsgoogle-apps-script

I am using API in Google Sheets to call prices of cryptocurrencies vrs Bitcoin
from URL: https://api.coinpaprika.com/v1/tickers?quotes=BTC

In some cases I am getting scientific notation instead of the x.xxxxxxxx number value. (e.g. Verge coin price (in bitcoin) is currently pulling in 9.4e-7 instead of 0.00000094)

I cannot then do any formula work on the imported data because it is not converting back to the number, and I have so far found no way to convert it or stop it being imported as a scientific number.

I have tried formatting the import cells to text, but that still pulls the scientific number in from the API call (I have contacted coinpaprika support to see if they know a way around it).

What I need is a way to convert that scientific number into x.xxxxxxxx number format so that I can then use it in a formula to calculate the value (in bitcoin) of my holdings in a particular cryptocurrency.

Best Answer

I found the problem, and the solution.

ImportJSON is calling the API using the function ImportJSON(url, query, parseOptions) from a script. It seems this was automatically truncating the pulled data. So I added the parseOption to stop it truncating the numbers, and it needed the NoInherit option as well in order to work on my sheet.

=ImportJSON("https://api.coinpaprika.com/v1/tickers?quotes=BTC",,"noInherit,noTruncate")

Changing that in the script now pulls bitcoin price in the format x.xxxxxxxx instead of scientific notation, and I can now use it for calcuations in the Google Sheet.

Related Topic