Maps Service


Maps Service:


The Map service for Google Apps Script allows you to create maps 'on the Fly' from location descriptions , such as 'New York'.

A convenient application of this, would be to send a Bulk Email to Customers/Staff with an Event Location Map .

The following Spreadsheet Template includes the Apps Script to Achieve this. Click 'Use This Template' to create a copy for your Google Docs.


Map Mail Merge Spreadsheet Template includes the following:
  • Bulk Email (Using the Mail Service with a Spreadsheet) 
  • Dynamic Map (Adjustable Location Map using the Map Service).
  • Personalized Driving Directions (Each specific customer/staff location creates there unique driving directions)

This Template has many uses in Real Business Terms. It can be integrated using forms and Google Sites so it is fully automated and seamless to the User and the Event Administrator.

Real Business Applications:


Staff Meeting/Celebration
Fund Raising Event
Art Exhibition/Music Gig

etc...

The are many other real world applications for education and social events.

Also in real world marketing terms adds the ability for sponsor advertising.

Automated Business System:

The abiltity to customize the Apps Scripts built into templates, creates a powerful solution which can be adopted to indivdual business practice and improved on.




Mail Merge Functions:

function onOpen() { // Run the function when the document is opened
  var submenu = [{name:"Customize...", functionName:"customize"}, {name:"Send Emails", functionName:"send"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Invitations', submenu);  
}

function send() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Setup Sheet');
  var guests = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Guest List').getDataRange().getValues();
  
  // we need the following:
  var eventTitle = sheet.getRange('B4').getValue();
  var eventDescription = sheet.getRange('B5').getValue();
  var hostName = sheet.getRange('B6').getValue();
  var location = sheet.getRange('B7').getValue();
  var date = sheet.getRange('B8').getValue();
  var image = sheet.getRange('B9').getValue();
  var imageAttr = sheet.getRange('B10').getValue();

  var emailTemplate = sheet.getRange('B13').getValue();
  
  // replace what we can for now:
  emailTemplate = emailTemplate.replace('${Event Title}', eventTitle);
  emailTemplate = emailTemplate.replace('${Event Description}', eventDescription);
  emailTemplate = emailTemplate.replace('${Host Name}', hostName);
  emailTemplate = emailTemplate.replace('${Location}', location);
  emailTemplate = emailTemplate.replace('${Date}', date);
  emailTemplate = emailTemplate.replace('${Image}', image);
  emailTemplate = emailTemplate.replace('${Image Attribution}', imageAttr);
  
  // Now send one to each person on the Guest List
  for (var g=3; g<guests.length; ++g) {//loop
    var name = guests[g][0];
    var emailBody = emailTemplate.replace('${First Name}', name);
    var emailAddress = guests[g][2];
    
    var geo = getMapDirections_(guests[g][3], location);
    emailBody = emailBody.replace('${Map}', geo.map);
    emailBody = emailBody.replace('${Directions}', geo.directions);

    try {
      MailApp.sendEmail(emailAddress, eventTitle, 'Please view in HTML capable email client.', {htmlBody: emailBody}); 
    } catch(e) {
      Browser.msgBox(name + "'s email address " + emailAddress + " is not a valid.");
    }
  }    
}

function customize() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Setup Sheet');
  var data = sheet.getRange("A4:B10").getValues();
  
  // create the app
  var app = UiApp.createApplication().setTitle("Customize Invitations").setWidth("600");
  var vpanel = app.createVerticalPanel().setStyleAttribute('border-spacing', '10');
  var handler = app.createServerClickHandler('eventHandler_');
  var instructions = app.createLabel("Please enter your event details in the fields provided below.  (Simple html is accepted).");

  var inputGrid = app.createGrid(7, 2);
  for (var i=0; i<data.length; ++i) {
    inputGrid.setWidget(i,0,app.createLabel(data[i][0]).setStyleAttribute('float', 'right')); 
    var text;
    if (i==1)
      text = app.createTextArea().setValue(data[i][1])
                          .setStyleAttribute('resize', 'none')
                          .setHeight('50');      
    else
      text = app.createTextBox().setValue(data[i][1]);  
    handler.addCallbackElement(text);
    inputGrid.setWidget(i,1,text.setWidth('450').setName('input'+i));    
  }
  
  var btnGrid = app.createGrid(1, 2).setStyleAttribute('float', 'right');
  btnGrid.setWidget(0,0,app.createButton('Save',handler).setId('SAVE'));
  btnGrid.setWidget(0,1,app.createButton('Cancel', handler).setId('CANCEL'));

  vpanel.add(instructions).add(inputGrid).add(btnGrid);

  // put the vpanel in the form...
  app.add(vpanel);
  SpreadsheetApp.getActiveSpreadsheet().show(app);  
}

function eventHandler_(e) {
  var app = UiApp.getActiveApplication();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Setup Sheet');
  if (e.parameter.source == 'SAVE') {
    var data = [];
    for (var i=0; i<7; ++i) 
      data.push([e.parameter['input'+i]]);
    
    sheet.getRange('B4:B10').setValues(data);
    app.close();    
  }
  else
    app.close();

  return app;
}

function getMapDirections_(start, end) {
  // Generate personalized static map with directions.
  var directions = Maps.newDirectionFinder()
      .setOrigin(start)
      .setDestination(end)
      .getDirections();

  var currentLabel = 0;
  var directionsHtml = "";
  var map = Maps.newStaticMap().setSize(500, 350);

  map.setMarkerStyle(Maps.StaticMap.MarkerSize.SMALL, "red", null);
  map.addMarker(start);
  map.addMarker(end);
    
  var r1 = new RegExp('<div style="font-size:0.9em">', 'g');
  var r2 = new RegExp('</div>', 'g');
  var points = [];
  for (var i in directions.routes) {
    for (var j in directions.routes[i].legs) {
      for (var k in directions.routes[i].legs[j].steps) {
        var step = directions.routes[i].legs[j].steps[k];
        var path = Maps.decodePolyline(step.polyline.points); 
        points = points.concat(path);
        var text = step.html_instructions;
        text = text.replace(r1, '<br>');
        text = text.replace(r2, '<br>');
        directionsHtml += "<br>" + (++currentLabel) + " - " + text;
      }
    }
  }
 
  
  // be conservative, and only sample 100 times...
  var lpoints=[];
  if (points.length < 200)
    lpoints = points;
  else {
    var pCount = (points.length/2);
    var step = parseInt(pCount/100);
    for (var i=0; i<100; ++i) {
      lpoints.push(points[i*step*2]);
      lpoints.push(points[(i*step*2)+1]);
    }
  }      

  // make the polyline
  if (lpoints.length>0) {
    var pline = Maps.encodePolyline(lpoints);
    map.addPath(pline);
  }

  var obj = {};
  obj.map = "<h1 align=center><img src=\"" + map.getMapUrl() + "\" /></h1>";
  obj.directions = directionsHtml;
  return obj;
}
​

Html Email Template:

"<div><p style=""font-size:10pt; line-height:115%; margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><table cellspacing=""0"" cellpadding=""0"" style=""border-collapse:collapse; margin:5pt; ""><tbody><tr><td style=""background-color:#66cccc; border-bottom-color:#9999ff; border-bottom-style:solid; border-bottom-width:2.25pt; border-left-color:#9999ff; border-left-style:solid; border-left-width:2.25pt; border-right-color:#9999ff; border-right-style:solid; border-right-width:2.25pt; border-top-color:#9999ff; border-top-style:solid; border-top-width:2.25pt; padding:6pt; vertical-align:middle; width:468pt;""><p style=""margin:0pt; text-align:center""><span style=""color:#ffffff; font-family:Garamond; font-size:18pt; font-style:italic; font-weight:bold; text-decoration:none"">${Event Title}</span></p><br>
<p style=""margin:0pt; text-align:center""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><p style=""margin:0pt; text-align:center""><img src=""${Image}"" width=""300"" height=""300"" alt="""" style=""border:none""></p><p style=""margin:0pt; text-align:center""><span style=""color:#ffffff; font-family:Garamond; font-size:8pt; font-style:normal; font-weight:normal; text-decoration:none"">${Image Attribution}</span></p><p style=""margin:0pt""><br><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><p style=""margin:0pt""><span style=""color:#ffffff; font-family:Garamond; font-size:18pt; font-style:italic; font-weight:normal; text-decoration:none"">Dear ${First Name},</span></p><br><p style=""margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><p style=""margin:0pt""><span style=""color:#ffffff; font-family:Garamond; font-size:18pt; font-style:italic; font-weight:normal; text-decoration:none"">${Event Description}</span></p><p style=""margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><p style=""margin:0pt""><br><span style=""color:#ffffff; font-family:Garamond; font-size:18pt; font-style:italic; font-weight:normal; text-decoration:none; float: right; width: 40%"">${Host Name}</span><br></p></td></tr><tr><td style=""background-color:#ffff99; border-bottom-color:#9999ff; border-bottom-style:solid; border-bottom-width:2.25pt; border-left-color:#9999ff; border-left-style:solid; border-left-width:2.25pt; border-right-color:#9999ff; border-right-style:solid; border-right-width:2.25pt; border-top-color:#9999ff; border-top-style:solid; border-top-width:2.25pt; padding:4.95pt; vertical-align:top; width:468pt""><p style=""margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><table cellspacing=""0"" cellpadding=""0"" style=""border-collapse:collapse; margin-left:0.25pt""><tbody><tr><td style=""border-bottom-color:#ffff99; border-bottom-style:solid; border-bottom-width:0.5pt; border-left-color:#ffff99; border-left-style:solid; border-left-width:0.5pt; border-right-color:#ffff99; border-right-style:solid; border-right-width:0.5pt; border-top-color:#ffff99; border-top-style:solid; border-top-width:0.5pt; padding:5pt; vertical-align:top; width:229pt""><p style=""margin:0pt""><span style=""color:#666666; font-family:Garamond; font-size:14pt; font-style:italic; font-weight:normal; text-decoration:none"">Where:</span></p></td><td style=""border-bottom-color:#ffff99; border-bottom-style:solid; border-bottom-width:0.5pt; border-left-color:#ffff99; border-left-style:solid; border-left-width:0.5pt; border-right-color:#ffff99; border-right-style:solid; border-right-width:0.5pt; border-top-color:#ffff99; border-top-style:solid; border-top-width:0.5pt; padding:4.95pt; vertical-align:top; width:229pt""><p style=""margin:0pt""><span style=""color:#666666; font-family:Garamond; font-size:14pt; font-style:italic; font-weight:normal; text-decoration:none"">When:</span></p></td></tr><tr><td style=""border-bottom-color:#ffff99; border-bottom-style:solid; border-bottom-width:0.5pt; border-left-color:#ffff99; border-left-style:solid; border-left-width:20pt; border-right-color:#ffff99; border-right-style:solid; border-right-width:0.5pt; border-top-color:#ffff99; border-top-style:solid; border-top-width:0.5pt; padding:5pt; vertical-align:top; width:229pt""><p style=""margin:0pt""><span style=""color:#666666; font-family:Garamond; font-size:14pt; font-style:italic; font-weight:normal; text-decoration:none"">${Location}</span></p></td><td style=""border-bottom-color:#ffff99; border-bottom-style:solid; border-bottom-width:0.5pt; border-left-color:#ffff99; border-left-style:solid; border-left-width:20pt; border-right-color:#ffff99; border-right-style:solid; border-right-width:0.5pt; border-top-color:#ffff99; border-top-style:solid; border-top-width:0.5pt; padding:5pt; vertical-align:top; width:229pt""><p style=""margin:0pt""><span style=""color:#666666; font-family:Garamond; font-size:14pt; font-style:italic; font-weight:normal; text-decoration:none"">${Date}</span></p></td></tr></tbody></table><p style=""margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><p style=""margin:0pt; text-align:center""><span style=""color:#666666; font-family:Garamond; font-size:14pt; font-style:italic; font-weight:normal; text-decoration:none"">${Map}</span></p><p style=""margin-left:45pt""><span style=""color:#666666; font-family:Garamond; font-size:14pt; font-style:italic; font-weight:normal; text-decoration:none"">${Directions}</span></p><p style=""margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p></td></tr></tbody></table><p style=""font-size:10pt; line-height:115%; margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p><p style=""font-size:10pt; line-height:115%; margin:0pt""><span style=""font-family:'Garamond'; font-size:10pt""> </span></p></div>"