Google GeoChart: LatLong format changes when using a formula?

Question

This is pretty weird. I have a Geochart that is pulling data from a Google Sheet. I am using Latitude and Longitude to identify the location of the markers as it is way faster and precise that using ISO 3166-1 or country names.

Now, this is my script: JSFIDDLE

As you can see, it works. BUT since the content of the Google Sheet I am pulling the data from is supposed to be dynamic, meaning that the list of countries could change in the future (the order or the number of countries listed), I'd like to output the Latitude and Longitude values with a formula (instead of having fixed values):

IF($H$2:$H = "AD";"42.546245";
IF($H$2:$H = "AE";"23.424076";
IF($H$2:$H = "AF";"33.93911";
IF($H$2:$H = "AG";"17.060816";
etc

The result is the same but for some weird reason (I guess related to the locale of the document), if I point my Geochart to this second Google Sheet with the same exact content of the first one but with the only change being the way in which the Latitude and Longitude values are pulled (with the formula above instead of manually typed), the script wouldn't like that and the map would show Italy in the US (for example!):

You can try by yourselves, changing the commented out query to the second Google Sheet (and commenting back the first one).

You'll see that the map loaded is way slower and the markers are all in wrong positions (and only 3 get loaded instead of >30). You can also take a look at the spreadsheets directly, the links are in the fiddle.

Answer 1

in the formula, the reason comma , works and period . doesn't

is because the spreadsheet is using "France" as the Locale

change the Locale to "United States", then save and re-open the spreadsheet

afterwards, change the commas to periods and it will work

=ARRAYFORMULA(
IF($H$2:$H = "AD",42.546245,
IF($H$2:$H = "AE",23.424076,
...

to change the Locale, go to --> File, Spreadsheet settings...

Locale is the first setting on the first tab -- General

---

although google charts support different locales by using the language setting in the load statement
the data used to draw the charts must originate from the 'en' locale
the language setting merely formats the values accordingly

leaving the spreadsheet set to the France Locale,
then loading google charts using language: 'fr' will not work...