Search code examples
google-mapsgoogle-apps-scriptgoogle-sheetsgeocode

Generate Google Map from Addresses in Google Sheet

I'd like to create a Google Map that pulls addresses from a google sheet. I've figured out how to generate a map search of a single address (see below), but can't figure out how to get my map to plot multiple addresses.

 function sendMap2() {
 var sheet = SpreadsheetApp.getActive().getSheetByName('Summary');
 var address = sheet.getRange('C15:C17').getValue();
 var mapUrl = "https://www.google.com/maps/search/?api=1&query=";
 var mapQuery = encodeURIComponent(address);
 GmailApp.sendEmail('emailaddress@gmail.com', 'Map', 'Map Link: '+mapUrl+mapQuery );
}

Solution

  • Problem:

    You are using getValue, which will only return the top-left cell in the range you specify (that is, C15).

    Solution:

    If you want to get all the addresses in the range, you have to use getValues instead. This will return a two-dimensional array of values, which I'd suggest you to transform to a simple array of addresses. You can then iterate through each address in this array, appending each array to the body of the email message with concat().

    It could be something along the following lines:

    function sendMap2() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Summary');
  var values = sheet.getRange('C15:C17').getValues();
  var addresses = [];  
  for (var i = 0; i < values.length; i++) {
    addresses = addresses.concat(values[i]);
  }
  var body = "";
  for (var j = 0; j < addresses.length; j++) {
    var mapUrl = "https://www.google.com/maps/search/?api=1&query=";
    var mapQuery = encodeURIComponent(addresses[j]);
    body = body.concat('Map Link: ' + mapUrl + mapQuery + '\n');
  }
  GmailApp.sendEmail('your-email', 'Map', body);
}

    UPDATE:

    If you want to point to several addresses in a single URL, you can use this instead:

      var mapUrl = "https://www.google.com/maps/dir/";
  var body = 'Map Link: ' + mapUrl;
  for (var j = 0; j < addresses.length; j++) {
    var mapQuery = encodeURIComponent(addresses[j]);
    body = body.concat(mapQuery + '/');
  }
  GmailApp.sendEmail('your-email', 'Map', body);
}

    Reference: