My Maps to Google sheets

[William Rems]

I posted this in google sheets forum but think it's probably better to post here...

I’m trying to add pins to a Google MyMaps map and have the latitude and longitude information added to new rows in Google Sheets. I used Chatgpt to generate some javascript code to pull the data from mymaps to sheets. It looks like it should work. The XML in the KML looks good too, so it seems like apps script is able to get the data about the pins but not actually loop through the xml and add each placemark to new rows.

Here’s the google sheet with the code added I’ve been using:

https://docs.google.com/spreadsheets/d/1JKp0DBWjXNqd0N13EHupKF035niJ4Z591NbkyZfCtyA/edit?usp=sharing

Here’s the MyMaps link to the public map:

https://www.google.com/maps/d/edit?mid=14yvPE_vD1qsK2aSr7XPcPFTIf4CdDZQ&usp=sharing

Here’s the KML file that seems to be received from MyMaps:

https://www.google.com/maps/d/kml?mid=14yvPE_vD1qsK2aSr7XPcPFTIf4CdDZQ&forcekml=1

Here’s the Chatgpt script:

function importMapData() {

  // Replace "YOUR_MAP_ID" with the ID of your Google My Maps map

  var mapId = "14yvPE_vD1qsK2aSr7XPcPFTIf4CdDZQ";

  

  // Replace "Sheet1" with the name of the sheet where you want the pins to appear

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  // Retrieve the KML data from the Google My Maps API

  var url = "https://www.google.com/maps/d/kml?mid=" + mapId + "&forcekml=1";

  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});

  

  if (response.getResponseCode() == 200) {

    var xml = response.getContentText();

    Logger.log("XML content: " + xml);

    var document = XmlService.parse(xml);

    var root = document.getRootElement();

    var placemarks = root.getChildren("Placemark");

    // Write the data to the Google Sheet

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

      var placemark = placemarks[i];

      var title = placemark.getChild("name").getText();

      var description = placemark.getChild("description").getText();

      var point = placemark.getChild("Point").getChild("coordinates").getText().split(",");

      Logger.log("Point: " + point);

      var longitude = point[0].trim();

      var latitude = point[1].trim();

      var row = [title, description, latitude, longitude];

      sheet.appendRow(row);

      Logger.log("Added row: " + row);

    }

  } else {

    Logger.log("Error retrieving KML data: " + response.getResponseCode() + " " + response.getContentText());

  }

}

Any help is greatly appreciated!

[cwl...@gmail.com]

Hi William,

From what I can see, everything looks great!! But I think your code is incomplete. I think you may be missing a final step of uploading the modified KML file back to GMaps. 
Unfortunately, it looks like you can not edit existing maps, but only create new ones; (and only images at that): 🥴
Class Maps

Allows for direction finding, geocoding, elevation sampling and the creation of static map images.