Google Sheets' IMPORTDATA randomly converts number

Question

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?

Answer 1

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.