Google GeoChart: LatLong format changes when using a formula?
Asked Answered
S

1

8

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!):

enter image description here

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

enter image description here

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.

Steffie answered 25/5, 2017 at 16:7 Comment(3)
try removing the quotes, lat / lng needs to be a number not a string --> IF($H$2:$H = "AD";42.546245;Debt
@Debt I get a Formula parse error if I remove the quotesSteffie
the formula parse error was because he didn't like dots but was okay with commas. So I changed the formula for both Latitude and Longitude to IF($H$2:$H = "AD";1,601554; IF($H$2:$H = "AE";53,847818; IF($H$2:$H = "AF";67,709953; ... but the result was the same.. markers in wrong positions , really slow and only 3 displayed on the map.Steffie
D
2

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...

Debt answered 31/5, 2017 at 14:19 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.