Google map’s amalgamation with Google Sheets

Suppose you wanted to find out the road distance in Kilo Meters and Miles between every city on the list to the other city on the list. Manually this can be done but as the list grows in size the task will start to become tedious.

Along with this, you wanted to find out the GPS coordinates of address and update them in a spreadsheet.

And we wanted to know the step by step driving instructions for moving from one address to another.

Google Map APIs along with extensibility of google sheets can help to solve these problem.

Following are the steps:

  • Create a spreadsheet on Google Sheet with following columns otherwise open file from here.

  • Open Script editor, to open Script Editor select Tools->script Editor… add following script in editor and save it. Function comments are provided to explain what that particular function provides.
 /**  
  * A custom function that converts meters to miles.  
  *  
  * @param {Number} meters The distance in meters.  
  * @return {Number} The distance in miles.  
  */  
 function metersToMiles(meters) {  
  if (typeof meters != 'number') {  
   return null;  
  }  
  return meters / 1000 * 0.621371;  
 }  
 /**  
  * A custom function that converts meters to kilometers.  
  *  
  * @param {Number} meters The distance in meters.  
  * @return {Number} The distance in miles.  
  */  
 function metersTokms(meters) {  
  if (typeof meters != 'number') {  
   return null;  
  }  
  return meters / 1000;  
 }  
 /**  
  * A custom function that gets coordinates & complete address of location.  
  *  
  * @param {String} a valid address.  
  * @return {String} complete Address and coordinates in format [%s]{%s:%s}.  
  */  
 function coordinates(location) {  
 var response = Maps.newGeocoder().geocode('Times Square, New York, NY');  
  for (var i = 0; i < response.results.length; i++) {  
   var result = response.results[i];  
   return '['+result.formatted_address+']' +'{'+ result.geometry.location.lat +':'  
   +result.geometry.location.lng+'}';  
  }  
 }  
 /**  
  * A custom function that gets the driving distance between two addresses.  
  *  
  * @param {String} origin The starting address.  
  * @param {String} destination The ending address.  
  * @return {Number} The distance in meters.  
  */  
 function drivingDistance(origin, destination) {  
  var directions = getDirections_(origin, destination);  
  return directions.routes[0].legs[0].distance.value;  
 }  
 /**  
  * A special function that runs when the spreadsheet is open, used to add a  
  * custom menu to the spreadsheet.  
  */  
 function onOpen() {  
  var spreadsheet = SpreadsheetApp.getActive();  
  var menuItems = [  
   {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}  
  ];  
  spreadsheet.addMenu('Directions', menuItems);  
 }  
 /**  
  * Creates a new sheet containing step-by-step directions between the two  
  * addresses on the "Settings" sheet that the user selected.  
  */  
 function generateStepByStep_() {  
  var spreadsheet = SpreadsheetApp.getActive();  
  var settingsSheet = spreadsheet.getSheetByName('Settings');  
  settingsSheet.activate();  
  // Prompt the user for a row number.  
  var selectedRow = Browser.inputBox('Generate step-by-step',  
    'Please enter the row number of the addresses to use' +  
    ' (for example, "2"):',  
    Browser.Buttons.OK_CANCEL);  
  if (selectedRow == 'cancel') {  
   return;  
  }  
  var rowNumber = Number(selectedRow);  
  if (isNaN(rowNumber) || rowNumber < 2 ||  
    rowNumber > settingsSheet.getLastRow()) {  
   Browser.msgBox('Error',  
     Utilities.formatString('Row "%s" is not valid.', selectedRow),  
     Browser.Buttons.OK);  
   return;  
  }  
  // Retrieve the addresses in that row.  
  var row = settingsSheet.getRange(rowNumber, 1, 1, 2);  
  var rowValues = row.getValues();  
  var origin = rowValues[0][0];  
  var destination = rowValues[0][1];  
  if (!origin || !destination) {  
   Browser.msgBox('Error', 'Row does not contain two addresses.',  
     Browser.Buttons.OK);  
   return;  
  }  
  // Get the raw directions information.  
  var directions = getDirections_(origin, destination);  
  // Create a new sheet and append the steps in the directions.  
  var sheetName = 'Driving Directions for Row ' + rowNumber;  
  var directionsSheet = spreadsheet.getSheetByName(sheetName);  
  if (directionsSheet) {  
   directionsSheet.clear();  
   directionsSheet.activate();  
  } else {  
   directionsSheet =  
     spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());  
  }  
  var sheetTitle = Utilities.formatString('Driving Directions from %s to %s',  
    origin, destination);  
  var headers = [  
   [sheetTitle, '', ''],  
   ['Step', 'Distance (Meters)', 'Distance (Miles)']  
  ];  
  var newRows = [];  
  for (var i = 0; i < directions.routes[0].legs[0].steps.length; i++) {  
   var step = directions.routes[0].legs[0].steps[i];  
   // Remove HTML tags from the instructions.  
   var instructions = step.html_instructions.replace(/<br>|<div.*?>/g, '\n')  
     .replace(/<.*?>/g, '');  
   newRows.push([  
    instructions,  
    step.distance.value  
   ]);  
  }  
  directionsSheet.getRange(1, 1, headers.length, 3).setValues(headers);  
  directionsSheet.getRange(headers.length + 1, 1, newRows.length, 2)  
    .setValues(newRows);  
  directionsSheet.getRange(headers.length + 1, 3, newRows.length, 1)  
    .setFormulaR1C1('=METERSTOMILES(R[0]C[-1])');  
  // Format the new sheet.  
  directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');  
  directionsSheet.getRange('A1:2').setFontWeight('bold');  
  directionsSheet.setColumnWidth(1, 500);  
  directionsSheet.getRange('B2:C').setVerticalAlignment('top');  
  directionsSheet.getRange('C2:C').setNumberFormat('0.00');  
  var stepsRange = directionsSheet.getDataRange()  
    .offset(2, 0, directionsSheet.getLastRow() - 2);  
  setAlternatingRowBackgroundColors_(stepsRange, '#ffffff', '#eeeeee');  
  directionsSheet.setFrozenRows(2);  
  SpreadsheetApp.flush();  
 }  
 /**  
  * Sets the background colors for alternating rows within the range.  
  * @param {Range} range The range to change the background colors of.  
  * @param {string} oddColor The color to apply to odd rows (relative to the  
  *   start of the range).  
  * @param {string} evenColor The color to apply to even rows (relative to the  
  *   start of the range).  
  */  
 function setAlternatingRowBackgroundColors_(range, oddColor, evenColor) {  
  var backgrounds = [];  
  for (var row = 1; row <= range.getNumRows(); row++) {  
   var rowBackgrounds = [];  
   for (var column = 1; column <= range.getNumColumns(); column++) {  
    if (row % 2 == 0) {  
     rowBackgrounds.push(evenColor);  
    } else {  
     rowBackgrounds.push(oddColor);  
    }  
   }  
   backgrounds.push(rowBackgrounds);  
  }  
  range.setBackgrounds(backgrounds);  
 }  
 /**  
  * A shared helper function used to obtain the full set of directions  
  * information between two addresses. Uses the Apps Script Maps Service.  
  *  
  * @param {String} origin The starting address.  
  * @param {String} destination The ending address.  
  * @return {Object} The directions response object.  
  */  
 function getDirections_(origin, destination) {  
  var directionFinder = Maps.newDirectionFinder();  
  directionFinder.setOrigin(origin);  
  directionFinder.setDestination(destination);  
  var directions = directionFinder.getDirections();  
  if (directions.routes.length == 0) {  
   throw 'Unable to calculate directions between these addresses.';  
  }  
  return directions;  
 }  
 /**  
  * A custom function that gets the driving distance between two addresses.  
  *  
  * @param {String} origin The starting address.  
  * @param {String} destination The ending address.  
  * @return {Number} The distance in meters.  
  */  
 function drivingDistance(origin, destination) {  
  var directions = getDirections_(origin, destination);  
  return directions.routes[0].legs[0].distance.value;  
 }  
 /**  
  * A special function that runs when the spreadsheet is open, used to add a  
  * custom menu to the spreadsheet.  
  */  
 function onOpen() {  
  var spreadsheet = SpreadsheetApp.getActive();  
  var menuItems = [  
   {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}  
  ];  
  spreadsheet.addMenu('Directions', menuItems);  
 }  
 /**  
  * Creates a new sheet containing step-by-step directions between the two  
  * addresses on the "Settings" sheet that the user selected.  
  */  
 function generateStepByStep_() {  
  var spreadsheet = SpreadsheetApp.getActive();  
  var settingsSheet = spreadsheet.getSheetByName('Settings');  
  settingsSheet.activate();  
  // Prompt the user for a row number.  
  var selectedRow = Browser.inputBox('Generate step-by-step',  
    'Please enter the row number of the addresses to use' +  
    ' (for example, "2"):',  
    Browser.Buttons.OK_CANCEL);  
  if (selectedRow == 'cancel') {  
   return;  
  }  
  var rowNumber = Number(selectedRow);  
  if (isNaN(rowNumber) || rowNumber < 2 ||  
    rowNumber > settingsSheet.getLastRow()) {  
   Browser.msgBox('Error',  
     Utilities.formatString('Row "%s" is not valid.', selectedRow),  
     Browser.Buttons.OK);  
   return;  
  }  
  // Retrieve the addresses in that row.  
  var row = settingsSheet.getRange(rowNumber, 1, 1, 2);  
  var rowValues = row.getValues();  
  var origin = rowValues[0][0];  
  var destination = rowValues[0][1];  
  if (!origin || !destination) {  
   Browser.msgBox('Error', 'Row does not contain two addresses.',  
     Browser.Buttons.OK);  
   return;  
  }  
  // Get the raw directions information.  
  var directions = getDirections_(origin, destination);  
  // Create a new sheet and append the steps in the directions.  
  var sheetName = 'Driving Directions for Row ' + rowNumber;  
  var directionsSheet = spreadsheet.getSheetByName(sheetName);  
  if (directionsSheet) {  
   directionsSheet.clear();  
   directionsSheet.activate();  
  } else {  
   directionsSheet =  
     spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());  
  }  
  var sheetTitle = Utilities.formatString('Driving Directions from %s to %s',  
    origin, destination);  
  var headers = [  
   [sheetTitle, '', ''],  
   ['Step', 'Distance (Meters)', 'Distance (Miles)']  
  ];  
  var newRows = [];  
  for (var i = 0; i < directions.routes[0].legs[0].steps.length; i++) {  
   var step = directions.routes[0].legs[0].steps[i];  
   // Remove HTML tags from the instructions.  
   var instructions = step.html_instructions.replace(/<br>|<div.*?>/g, '\n')  
     .replace(/<.*?>/g, '');  
   newRows.push([  
    instructions,  
    step.distance.value  
   ]);  
  }  
  directionsSheet.getRange(1, 1, headers.length, 3).setValues(headers);  
  directionsSheet.getRange(headers.length + 1, 1, newRows.length, 2)  
    .setValues(newRows);  
  directionsSheet.getRange(headers.length + 1, 3, newRows.length, 1)  
    .setFormulaR1C1('=METERSTOMILES(R[0]C[-1])');  
  // Format the new sheet.  
  directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');  
  directionsSheet.getRange('A1:2').setFontWeight('bold');  
  directionsSheet.setColumnWidth(1, 500);  
  directionsSheet.getRange('B2:C').setVerticalAlignment('top');  
  directionsSheet.getRange('C2:C').setNumberFormat('0.00');  
  var stepsRange = directionsSheet.getDataRange()  
    .offset(2, 0, directionsSheet.getLastRow() - 2);  
  setAlternatingRowBackgroundColors_(stepsRange, '#ffffff', '#eeeeee');  
  directionsSheet.setFrozenRows(2);  
  SpreadsheetApp.flush();  
 }  
 /**  
  * Sets the background colors for alternating rows within the range.  
  * @param {Range} range The range to change the background colors of.  
  * @param {string} oddColor The color to apply to odd rows (relative to the  
  *   start of the range).  
  * @param {string} evenColor The color to apply to even rows (relative to the  
  *   start of the range).  
  */  
 function setAlternatingRowBackgroundColors_(range, oddColor, evenColor) {  
  var backgrounds = [];  
  for (var row = 1; row <= range.getNumRows(); row++) {  
   var rowBackgrounds = [];  
   for (var column = 1; column <= range.getNumColumns(); column++) {  
    if (row % 2 == 0) {  
     rowBackgrounds.push(evenColor);  
    } else {  
     rowBackgrounds.push(oddColor);  
    }  
   }  
   backgrounds.push(rowBackgrounds);  
  }  
  range.setBackgrounds(backgrounds);  
 }  
 /**  
  * A shared helper function used to obtain the full set of directions  
  * information between two addresses. Uses the Apps Script Maps Service.  
  *  
  * @param {String} origin The starting address.  
  * @param {String} destination The ending address.  
  * @return {Object} The directions response object.  
  */  
 function getDirections_(origin, destination) {  
  var directionFinder = Maps.newDirectionFinder();  
  directionFinder.setOrigin(origin);  
  directionFinder.setDestination(destination);  
  var directions = directionFinder.getDirections();  
  if (directions.routes.length == 0) {  
   throw 'Unable to calculate directions between these addresses.';  
  }  
  return directions;  
 } 

 

Features provided by above script

  1. A new function “DRIVINGDISTANCE” that will calculate driving distance between two addresses. It will give distance in meters.
  2. A new function “METERSTOMILES” that will convert distance in meters to distance in miles.
  3. A new function “METERSTOKMS”, it will convert distance in meters to distance in kms.
  4. A new function “COORDINATES” to return formatted address of location and its coordinates.
  5. A new menu option , Directions->Generate step by step… that will generate step by step driving instructions in new worksheet.

 

One Reply to “Google map’s amalgamation with Google Sheets”

Leave a Reply

Your email address will not be published. Required fields are marked *