From Point A to Point B: How to Use Google Apps Script to Calculate Distance and Time in Google Sheets

Welcome!

Imagine you’re responsible for transporting materials from one location to another for a logistics company. Accurately calculating the distance and time between two points is crucial for efficient logistics management, and having this information readily available can save time, money, and effort. However, doing it manually can seem like a daunting task. But don’t worry, there’s a solution!

In this blog post, I’ll show you how to use the Google Maps App with Google Apps Script to easily calculate the distance and time between any two locations in your Google Sheet. This solution is especially useful for logistics professionals who need to transport materials from one location to another. However, the applications of this tool extend beyond logistics and can be used for personal travel planning or any situation where you need to calculate distances. So, whether you’re a logistics professional or just someone who needs to calculate distances, keep reading to learn more!

Sample Google Sheet

The Google Sheet we will be using for this blog contains the following details.

If you want to work with the sheet, click here. The sheet is organized into 5 columns, with the first two columns containing the start and end locations, respectively. Once the distance and time have been generated using the code, a status column is updated to indicate that the calculation has been completed. This simple and efficient solution is perfect for logistics or transportation companies that need to quickly calculate distances and travel times between multiple locations.

Step1: Write the Automation Script

While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:

1. Click on Extensions and open the Script Editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor lets code.

Now that we have the Google Sheet ready and good to go, lets go ahead and write the code to generate the distance and time between the two locations.

function calculateDistances() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var numRows = sheet.getDataRange().getNumRows();
  var data = sheet.getRange("A2:B" + numRows).getValues();

Here I have started out by creating a new function called function calculateDistances() inside which we will be getting the spreadsheet using the getActiveSpreadsheet(). After getting the spreadsheet, we will be getting the individual sheet using the getSheetByName() function inside which we will be passing the name of the sheet as a parameter.

We are going to start off by getting the number of row using the getDataRange() function followed by getNumRows() function. Next we are going to get the first two columns that contains the start and end location columns. After getting the range we are using the getValues() function to get the values and store them in the data variable.

  for (var i = 0; i < data.length; i++) {
      var start = data[i][0];
      var end = data[i][1];
      var flag = data[i][4];

Here I have opened up a for loop that will iterate through each row of data. Inside this we are going to be retrieving the first column that contains the start location using data[i][0]. After that we get the second column that gets the end location using data[i][1]. Next we get the status column using data[i][4].

    if (start && end && flag !== "done") {
      var directions = Maps.newDirectionFinder()
        .setOrigin(start)
        .setDestination(end)
        .setMode(Maps.DirectionFinder.Mode.DRIVING)
        .getDirections();

Next we have a if loop that checks if the current row being processed has a start and end location followed by checking if the status column is not equal to done.

After this we get the directions to the map using the newDirectionFinder() inside which we set the origin and the destination. Next we declare the mode of travel that is going to be driving and in the end we get the directions using the getDirections() function.

      var distance = directions.routes[0].legs[0].distance.text;
      var duration = directions.routes[0].legs[0].duration.text;

Now that we have the directions its time to get the distance and duration that it will take to reach from the start location to the end location.

      sheet.getRange(i+2, 3).setValue(distance);
      sheet.getRange(i+2, 4).setValue(duration);
      sheet.getRange(i+2, 5).setValue("done");

    }
  }
}

Now that we have the distance and duration it will take to get from the start location to the end, its time to send the data back to the sheet. To do that we are going to use the setValue() function inside which we will pass the distance and duration variable as parameters. Once the values are set we set the status column for that particular row to done.

Our code is complete and good to go.

Step2: Check the Output

Its time to check the output and see if the code is able to get the distance and duration between the two locations. To do that go ahead and run the calculateDistances() function.

On running the code you should get an output like this in the Execution Log.

Now that we have no error in the execution log, if you switch back to the sheet you can see that the distance and duration has been pasted into the Google Sheet successfully.

Additionally, the status column has also been filled with done, which means the code has worked successfully.

Conclusion

In conclusion, using Google Apps Script and Google Maps App, we can automate the task of calculating the distance and time between two locations stored in a Google Sheet. This can be particularly useful for logistics companies that have a large number of locations and need to optimise their transportation routes. By simply running a script, we can generate the distance and time for all the locations, saving a significant amount of time and effort.You can get the code from the GitHub link given below.

Google-Apps-Script/GoogleMapsDistance.gs at master · aryanirani123/Google-Apps-Script

Feel free to reach out if you have any issues/feedback at aryanirani123@gmail.com.