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?
1 Answer
It looks that Google Sheets sometimes thinks that 2385.1
is a date as 177418
is the serial number for 2385-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.
-
1The locale setting lead me down the right path. If it's one where numbers use a comma for the decimal point then it will wrongly parse certain numbers. If I send a CSV file (with commas as delimiter) it's going to split numbers. The solution was to change the locale to one which uses dots.– jcfreiJan 26, 2022 at 2:10