Calculate Distance Between Two Addresses

[Derek_Clark]

I categorized this post under “support” because I suspect it can be accomplished without any additional functionality from Airtable but, if not, it may be more appropriate as a “feature request.”

Is it possible to have a field that calculates the distance (in miles/kil) between two address fields? I am trying to create a base to log miles driven for business since the IRS requires a milage log to take advantage of the “standard mileage deduction.”

[Elias_Gomez_Sai]

I don’t know any formula function to to this. Also, I think it can’t be done with Blocks.

[W_Vann_Hall]

It appears to be a simple Zapier task but requires a paid Zapier account. (I say ‘appears’ because I was able to build a three-stage Zap — trigger on new Airtable record; issue request to Google Maps Distance Matrix API and receive response; update Airtable record. Component testing worked fine, but to perform an end-to-end systems test, I need a paid Zapier account.)

Essentially, I defined an Airtable table with three fields: {Origin}, {Destination}, and {Distance}. ({Origin} and {Destination} are single-line text; {Distance} is number.)

I then configured a three-step Zapier webhooks zap:

Trigger

Airtable account, ‘Distance test’ base, [Test] table
Trigger on new record

Action

Zapier webhook Get action.
URL: https://maps.googleapis.com/maps/api/distancematrix/json
Query string parameters:
units | imperial
origins | {Origin}
destinations | {Destination}
key | [My Google API key]

Send as JSON: no
Unflatten: yes

Action #2

Airtable account, ‘Distance test’ base, [Test] table
Action: Update record
Record: Use a custom value
Airtable: New record: Record ID
Set {Distance} to Zapier ‘Get’: Rows elements distance value

This sets {Distance} to the distance between the origin and destination addresses in meters; you’ll need to convert to miles for the IRS.

[Derek_Clark]

Hmmm, ok thanks for the detailed response! I also don’t have a paid Zappier account and this issue isn’t quite worth it at this time.

[Derek_Clark]

That’s what I thought. I think it would require a custom function option.

[tassoart]

Does Zapier allow you to share zaps? Because I would love to test this but all that code scares me :rofl:

[W_Vann_Hall]

I don’t think so – well, except among members of a workgroup. I think I later built and tested such a Zap – you might find it documented in another post of mine. I’m in the [slow] process of sorting through the 300+ bases in my workspace and 1200+ Community posts and attempting to put the useful ones into some sort of resource base I can publish. I’ll see if I can find the distance code – which could easily be tied to one of the 60+ tables in one of my ‘scratch’ code testing bases – and share it.

[Christiaan_Huiz]

Thanks a lot for the instructions. I used it and tested it (I took a paid zapier account to test it) and it seems to work, however I wonder if I understood right that entering two addresses in AirTable should result in the distance and the duration (I added the duration).

It does so for the tests generated by Zapier, but when I type an address, nothing happens. I am assuming something incorrectly? Please see below.

Any suggestions that might solve this issue are very welcome!

[Christiaan_Huiz]

I came back to check and ‘it worked’, however the address are rewritten. That is a bit confusing. And there is something wrong with the distance and the timing (a small village in The Netherlands to Brussels in Belgium). Any the conclusion is that setting up a link between Google Maps and AirTable with Zapier works, but for the moment not as I had in mind.

[Christiaan_Huiz]

The issue about the address (to avoid it gets rewritten) I solved by adding a few extra steps, but the distance and timing problems remain.

question
I wonder how we can check the correctness of the distance and duration provided by the API? What extra steps do I need ?

As you can see the Royal houses of The Netherlands (The Hague) and Belgium (Brussels) only need one hour. Even for a royal that is fast…