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=3g<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(emailAddresseventTitle'Please view in HTML capable email client.'{htmlBodyemailBody})
    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(72);
  for (var i=0i<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(12).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=0i<7++i
      data.push([e.parameter['input'+i]]);
    
    sheet.getRange('B4:B10').setValues(data);
    app.close();    
  }
  else
    app.close();

  return app;
}

function getMapDirections_(startend{
  // 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(500350);

  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 in directions.routes{
    for (var in directions.routes[i].legs{
      for (var 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=0i<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>"