0

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.

  • Since question this involves the use of an external API and not Google Apps Script regular or advanced services I think that this question belongs to Stack Overflow as is very likely that the solution will be the use a JavaScript method or something specific to that API rather than something specific from Google Sheets/ Google Apps Script unless you want to use a Google Sheets function to be used on a formula. – Rubén May 14 at 0:31
  • but Google Sheets should have the ability to convert text to a number, no? if you can give me that then this will be solved that way, so it is still relevant to Google Sheets in this context. How to convert the text of a cell with 8.8e-7 to the number 0.00000088 so it can be used in calculations? – mdkb May 15 at 3:40
  • Please edit your question to add that clarification. – Rubén May 15 at 4:07
1

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.

-1

You need either to set the column width to such value that imported value does not need to be auto-shrunk by Google Sheets, or you can use one of round formulas (ROUND, ROUNDUP, ROUNDDOWN) to trim off decimal places.

In some cases, you could force the imported value to be imported as a text string and then just multiply it by 1 like:

=TO_TEXT(IMPORT...("...", "..."))*1

or like:

=VALUE(TO_TEXT(IMPORT...("...", "...")))

  • Setting the cell width made no difference. ROUNDDUP function gives the correct solution when I mouse over the formula in the cell I see 0.0000088 in blue, but then it gives the error - "Function ROUNDDOWN parameter 1 expects number values. But '8.8e-7' is a text and cannot be coerced to a number." TO_TEXT just seems to copy the scientific notation, so I still get '8.8e-7' in the new cell. VALUE - All variations just keep ending with the error: "VALUE parameter '8.8e-7' cannot be parsed to number." – mdkb May 15 at 3:41
  • apologies for the error in the above comment, I meant "ROUNDDOWN" – mdkb May 15 at 3:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.