Calculate Delivery Time & Distance in Airtable using Google Maps API

In this guide, we will calculate the delivery time & distance between two locations using the Google Maps API and the Data Fetcher Airtable app. We'll first get a Google Maps API key, then set up an API request that fetches the time and distance between pairs of locations. Finally, we'll see how to schedule this to run automatically.

Install Data Fetcher

Create Output Table

  • Create a table in your base called 'Orders'.
  • Add a field called 'Origin' with type 'Single line text'.
  • Add a field called 'Destination' with type 'Single line text'.
  • Add a field called 'Distance with type 'Single line text'.
  • Add the pairs of addresses/locations in the 'Origin' and 'Destination' fields.
Screenshot 2021-07-30 at 12.07.54.png
  • Create a new Grid view called 'To process' with the following filters:
    • The 'Origin' field is not empty.
    • The 'Destination' field is not empty.
    • The 'Distance' field is empty.
Screenshot 2021-07-30 at 12.51.28.png

Get a Google Maps Distance Matrix API Key

  • Follow these steps to create a project with a billing account and the Distance Matrix API enabled.
  • Then follow these steps to create an API key for the Distance Matrix API.
  • Note that the distance matrix is a paid API from Google. Check out this page to understand the costs involved.

Calculate Delivery Distance & Time in Airtable

  • On the home screen of the Data Fetcher app, click 'Create request'.
  • On the create request screen, enter the request name 'Calculate Distance & Time'.
  • Based on the distance matrix API documentation the base URL for all API requests is 'https://maps.googleapis.com/maps/api/distancematrix/json' . Enter this URL in the URL field on the create request screen:

https://maps.googleapis.com/maps/api/distancematrix/json

  • Add a parameter called origins. For the value we want to use a reference to the 'Origin' column in the 'Orders' table, so enter ***Orders*Origin***.
  • Add a parameter called destinations. For the value we want to use a reference to the 'Origin' column in the 'Orders' table, so enter ***Orders*Destination***.
  • Add a parameter called units with the value imperial. This means the text representation of the distance will be in miles and feet. If you would rather use kilometers and meters you can set this to 'metric'.
  • Add a parameter called key with the value of your API key from the previous step.

The full URL should now be:

https://maps.googleapis.com/maps/api/distancematrix/json?units=imperial&origins=***Orders*Origin***&destinations=***Orders*Destination***&key=YOUR_API_KEY

  • Set the output table to the 'Orders' table by clicking 'Use current'.
  • Set the output view to 'To process'.

Screenshot 2021-07-29 at 14.24.59.png
  • Click 'Advanced' to open the advanced settings.
  • Under 'Run on Multiple Records', click 'Use current' to set the table to 'Orders', then select the 'To process' view. This means an API request will run for every record in our 'To process' view, add the distance and time, and move it out of the view so it does not get updated next time we run the request.
Screenshot 2021-07-30 at 12.27.05.png

  • Click 'Run'. You'll see a loading message while the request runs, then the response field mapping modal will open. This is where we select which fields from the API response to keep and how they should map to the fields in the output table. Click 'Show response' on this modal to see the actual response.
  • Click 'Filter all' to deselect all fields, then select the following fields and data types:

Response Field

Data type

Use table field

Or, create field:

rows.elements.1.distance.text

Single line text

Distance

rows.elements.1.distance.value

Number

Distance (meters)

rows.elements.1.duration.text

Single line text

Duration

rows.elements.1.duration.value

Number

Duration (seconds)

Screenshot 2021-07-30 at 12.31.01.png

  • Click 'Confirm'. Data Fetcher will create the new fields in the output table, then the request will run again and the delivery distance and time for each pair of locations will be fetched into Airtable
  • Click 'Save'.

Calculate Delivery Time & Distance Automatically

At the moment you have to calculate the delivery time & distance by manually clicking the 'Run' button. Let's set them to update automatically by using Data Fetcher's scheduled requests feature.

  • On the create request screen in Data Fetcher, scroll to 'Schedule'.
  • You will need a paid account to use this feature, so click the button to upgrade your account if you haven't already. After upgrading, click the 'I've done this' button.
  • Add your Airtable API key if you haven't already.
  • Turn on 'Schedule this API request'.
  • Set how often you want the request to run.
  • Click 'Save'.
Screenshot 2021-07-28 at 16.07.36.png