Google Apps Script - How to get driving distance f

Good evening,

I have a spreadsheet where I record my daily driving mileage when working. The column headings are: Date, Point A, Point B, Point C, Point D, Point E, Point F, Point G, and Trip Mileage.

Currently, I manually use Google Maps to determine driving distance between points. What I'd like to see happen instead is for a script to pull the points data from my spreadsheet, determine distance between the points and total distance of each trip (i.e. row), and insert the total in the last column.

I believe I have this essentially all set up except I can't figure out how to get the distance data from Google Maps. From reading similar question, I sense someone might tell me to refer to the Maps Distance Matrix API. I've referred to this. I'm a beginner programmer, but it appears to me to be oriented toward apps, not Google docs. Is this the case? Or can it be made to serve my project?

Thank you in advance for all of your wonderful advice, comments, suggestions, and encouragement!!!

BTW, here is my code currently:

 function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var mileageLogSheet = ss.getSheetByName("Mileage Log");
  var dataRange = mileageLogSheet.getDataRange(); //Returns a Range  corresponding to the dimensions in which data is present. 
  var numRows = dataRange.getNumRows();  
  var numCol = dataRange.getNumColumns(); //numCol = 9.

  //Returns the range with the top left cell at the given coordinates (row, column) with the given number of rows and columns.
  var rangeToCompute = mileageLogSheet.getRange(10, 2, numRows-9, numCol-1); 
  var address1,
      address2;

  for(var rowCoord = 1; rowCoord < numRows-9; rowCoord++){

    for(var colCoord = 1; colCoord < 8; colCoord++){

      if(rangeToCompute.getCell(rowCoord, colCoord).getValue() != ""){
        address1 = rangeToCompute.getCell(rowCoord, colCoord).getValue();
        address2 = rangeToCompute.getCell(rowCoord, colCoord+1).getValue();

        var directions = Maps.newDirectionFinder()
        .setOrigin(address1)
        .setDestination(address2)
        .setMode(Maps.DirectionFinder.Mode.DRIVING)
        .getDirections();

        //Nope, this doesn't work!: 
        var distanceValue =  directions.routes[0].legs[0].distance.value; 

        var totalTripDistance;       
      }

    }
  } 

}

You can take use of the Google Maps WEB API's Direction API. Which give you a detail result on direction from point A to point B, which include the distance information you want.

To get started, you need an API KEY for using the Google Maps Direction API. Go to console.developers.google.com to create an account, create a project, enable the Directions API in your project and then create a credential (API KEY). You should able to create it as server API key and give it the address 0.0.0.0/0, which means all computer on earth, as long as you keep this project to yourself. For more information on creating API KEYS, you can take a look at this section.

Then in the spreadsheets, you need to first create the request URL by CONCAT your key, origin and destination. Here is how I did it:

=CONCAT(CONCAT(CONCAT(CONCAT(CONCAT("https://maps.googleapis.com/maps/api/directions/json?origin=",SUBSTITUTE(B2," ","+")),"&destination="),SUBSTITUTE(C2," ","+")),"&key="),$A$2)

*Note that you need to SUBSTITUTE spaces " " to pluses "+"

Then under the Tools > Script editor..., create an empty script and replace everything with the following code, and save:

function getDistance(url) {
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  var data = JSON.parse(json);
  var distance = data.routes[0].legs[0].distance.text;
  return distance; 
}

This is a customized function that parse the Google Maps Direction API JSON result and return the distance information. The JSON usually contains multiple routes and under the first route- routes[0], exist multiple legs and in the first leg- legs[0], exist a distance, which include a text and value field, which are a human readable field, and a integer number that represent the distance in meter.

return to the spreadsheets and you can call =getDistance(CellWithURL) to get the distance between two places.

I created an example here, hope it helps.

**ps. somehow it works without the API KEY too.. but you should apply for one for otherwise you violate the ToS.