Calculate Delivery Time & Distance in Airtable using Google Maps API

Jul 30, 2021Andy Cloke

In this guide, we'll calculate the time & distance between pairs of locations using the Google Maps API and the Data Fetcher Airtable app. This tutorial is 100% no-code. We'll create a Google Maps API key, then use it to pull in time & distance. Finally, we'll see how to schedule this to run automatically.

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'. This will be a text representation of the distance, but we will also import a numerical distance in metres.
  • Add the pairs of origins/locations in the 'Origin' and 'Destination' fields.
Screenshot 2021-07-30 at 12.07.54.png
  • Create a Grid view in the 'Orders' table 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.
  • You should get shown an API key after that step, but if not 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.

Install Data Fetcher

Install Data Fetcher from the Airtable app marketplace. After the app launches, sign up for a free Data Fetcher account by entering a password and clicking 'Sign up for free'.

Group 1Data Fetcher Sign Up .png

Calculate Delivery Distance & Time in Airtable

On the home screen of the Data Fetcher app, click 'Create your first request'. Requests in Data Fetcher are how you import data to or send data from your Airtable base.

click-create-your-first-request.png

On the create request screen in Data Fetcher, for Application, select 'Google Maps'.

google-maps-application-selected.png

Next, enter your API Key from the previous step.

google-maps-authorization-api-key.png

For Endpoint, select 'Calculate distance and time between start and end locations'.

google-maps-distance-endpoint.png

Enter a Name for your request, e.g. 'Import Distance & Time'. Then click 'Save & Continue'.

google-maps-calculate-distance-save-continue.png

We want to use the values in the 'Origin' field for Origin location. Under Origin location, click the + button to insert a reference to the table.

google-maps-calculate-distance-add-icon.png

A modal will open where we can create the table reference. For Field, select 'Origin'.

google-maps-calculate-distance-field.png

For Run for every record in view, select 'To process'.

google-maps-calculate-distance-to-process.png

Click 'Confirm'.

google-maps-calculate-distance-confirm.png

The Origin location now has a reference to our table.

google-maps-calculate-distance-origin-reference.png

Do the same for Destination location to create a reference to the 'Destination' field.

google-maps-calculate-distance-destination-reference.png

Select the Units for the text representation of the distance. e.g. 'Metric' to get a value expressed in km.

google-maps-calculate-distance-units.png

Optionally set the Transportation mode you want to use. If you do not set this, 'Driving' will be used.

google-maps-calculate-distance-transportation-mode.png

If you selected 'Transit' for Transportation mode, you can set the type of transit (public transport) by opening 'More options for this Google Maps endpoint' and setting the Transit mode.

Click 'Save & Run'.

google-maps-save-run.png

The request will run and the Response field mapping modal will open. This is where you set which fields to import from the Google Maps API and how they should map to your output table. For each Google Maps API field, you can either import or filter it. For an imported field, you can set whether to map them to an existing field or create a new field.

Make sure you import 'Distance', 'Distance (metres)', 'Duration' & 'Duration (seconds)'. Then click 'Save & Run'.

google-maps-response-field-mapping.png

Data Fetcher will create any fields that need to be created in the output table, then run the request and calculate the delivery time & distance for every pair of locations in the 'Orders' table.

google-maps-calculate-distance-imported-data.png

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.

In Data Fetcher, scroll to Schedule and click 'Upgrade'.

table-1-schedule-upgrade-button.png

A new tab will open where you can select a plan and enter your payment details to upgrade.

Screenshot 2021-12-29 at 10.25.19.png

Return to the Data Fetcher app and click "I've done this".

table-1-schedule-ive-done-this-button.png

Click 'Add API key' in order to add your Airtable API key. You can find your Airtable API key here.

Group 11(1).png

Enter your Airtable API key and click 'Save'.

save-airtable-api-key.png

Enable Schedule this request.

schedule-data-fetcher-request.png

Select how often you want the request to run, e.g. 'Every 15 mins'. Click 'Save' The request will now run on the schedule and calculate the delivery times automatically.

Group 11(2).png

Related Posts

How to Import Google Ads Data to Airtable

How to Import Google Ads Data to Airtable

May 26, 2022Rosie ThrelfallGoogle
How to Import Google Analytics Data to Airtable

How to Import Google Analytics Data to Airtable

May 23, 2022Rosie ThrelfallGoogleGoogle Analytics