Issue with BigQuery saving results as Google Sheets: some floating points number are messed up

Google Cloud Data Analytics
1

Hello,

I am from Brazil where we use comma “,” as decimal separator and a point “.” as thousands separator.

When using the BigQuery console (URL Removed by Staff) and saving query results as “Google Sheets” some floating point numbers get “messed up”. One example, a value like “5322,508745079” appears on the sheet as “5322508745079” and when I apply a format to it, it turns to

“5.322.508.745.079”. I tried to change my settings on the google cloud console (URL Removed by Staff) but I was unable to workaround it.

I have changed the language to English, have chosen the number format corresponding to a decimal separator as a point. I have tried thousands separator as comma and without thousands separator to no avail.

I guess that only numbers with “many” significant digits are effected but I don’t have a reproducible example.

Someone here have faced a similar issue?

Thanks.

2

Thanks for sharing the details—this kind of intermittent error can be really frustrating. Another common cause is when the Google Sheets API becomes overloaded, especially if multiple users or queries are running against the same sheet simultaneously googlecloudcommunity.com+13googlecloudcommunity.com+13googlecloudcommunity.com+13.

You might try:

  • Limiting concurrent access avoid multiple edits or queries at the same time

  • Exporting the sheet to a native BigQuery table (via Apps Script, Dataflow, etc.) to remove reliance on the Sheets API

3

Happens to a lot of users - BigQuery exports and the numbers look weird in Sheets.

Here’s why:

  • BigQuery spits out decimals with dots (like 3.14)
  • But Google Sheets looks at your account’s locale, not BigQuery’s
  • So if your locale’s Brazil or something, it flips it - uses commas for decimals

Here’s how to fix it fast:

  • In your Google Sheet, hit File > Settings > Locale
  • Change it to United States (or any place that uses dots for decimals)
  • Hit Save, refresh the sheet

Now re-export from BigQuery, and the numbers should look normal.

Also, if your numbers have a lot of decimal places, use this in your SQL:

FORMAT(‘%.6f’, your_column)

That makes it a string, so Sheets doesn’t mess it up. It’s super annoying, but this usually fixes it.

Still having issues? Drop a note - I’ve got a few more tricks.