0

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?

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
1
  • 1
    The 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.
    – jcfrei
    Jan 26, 2022 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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