Mymaps location data lost in Google Earth

[Geoffrey Jones]

Hi Everyone,

I am designing a unit for my high school students for next term using sheets, mymaps and Google Earth.

1. Data pulled from the web is placed into a spreadsheet (importHTML)
2. This data, using city names, is placed onto mymaps.
3. This map layer is exported as KML into Google Earth.

My demo works great with COVID data but the location data is lost when I import it in to Google earth. I only get the information placed as a slide rather than placemark. I have spent several hours trying to resolve this without success. Any advice? (I can only use the New Google Earth and do not have the individual lat/log data for each location- this mymaps provides).

Thank you in advance for your help.

Geoff

[Josh Williams]

Hello Geoff,

TL;DR Google Earth for Web does not geocode address like Google Earth Pro or My Maps. 

There are ways around this. (See video below) 

Usual disclaimers...

1. I understand these are not eloquent solutions. The Earth team makes these.
2. In typical Google fashion, these solutions work as of now, but I have know idea for how long. Only Google knows...or doesn't know...or can change their mind at any point. This is the way:) 
3. For the App script, I am not sure of the # limits Google has set for their geocoder function. At least for free use. There are ways to do this with their paid "places api". However, from my experience, this can get expensive for a teacher. I had to disable this API from my website because schools were constantly searching up locations via a search box; costing me hundreds of dollars. Probably part of the reason why the task you are trying to do is so cumbersome.

12 minute how to GeoCode Addresses from My Maps and Google Sheets

https://youtu.be/kK30IWiLBos

App Script

function GEOCODE_GOOGLE(address) {

  if (address.map) {

      return address.map(GEOCODE_GOOGLE)

  } else {

      var r = Maps.newGeocoder().geocode(address)

      for (var i = 0; i < r.results.length; i++) {

          var res = r.results[i]

          return res.geometry.location.lat + "," + res.geometry.location.lng

      }

  }

}

Google Sheet Formula example

=if(ISBLANK(A2),,GEOCODE_GOOGLE(A2)) 

Please let me know if you need me to break this down any further or need any help with the lesson. 

Have a great semester!

Josh