We have a simple REST endpoint that returns a number as a string. The number is rounded to two decimals and has a point, like this: 2385.1
Google Sheets then sometimes transforms this into 177418 or some other nonsensical value.
The endpoint has Content-Type set to to test/csv but we've also tried text/plain with the same effect. Changing the format of the cell didn't fix the problem.
Any ideas how we can get Google Sheets to represent the number consistently?
Google Sheets – IMPORTDATA Function Randomly Converts Numbers
google sheetsimportdata
Related Topic
- Google Sheets – Keep Leading Zero with IMPORTDATA
- Google Sheets – Custom Script in Pivot Table Value Formula Error
- Google-sheets – Google Sheets automatically converts a string to a number
- Google Sheets – Combining IMPORTRANGE, SUMIFS, QUERY with Mixed Content
- Google Sheets – Run ImportData Only When Sheet is Open
- Google Sheets – How to Import JSON from URL Using Existing Formulas
Best Answer
It looks that Google Sheets sometimes thinks that
2385.1is a date as177418is the serial number for2385-10-01 00:00:00.You could try changing the spreadsheet locale (File > Settings > Locale).
Another option is to use the URL Fetch Service from Google Apps Script to read the value from your REST endpoint. One way to use is to create a custom function to be used in the same way that IMPORTDATA, but you will have control about how the value is wrote to Google Sheets.