Zapier: Map Entire Table of Addresses with Latitude/Longitude via Google Maps API


#1

Does anyone have a Zapier workflow that ingests an Airtable table and then creates a Google Map that includes all records?

In a magical world, there’d be a way to layer multiple views, updated dynamically, on one map.

Thanks in advance!!!


#2

Did you find a way to integrate maps? Would love to figure this out too. Thanks


#3

Anyone have a solution?


#4

If you have Blocks you can just use the Maps block to do this.

If you need something more specific, though, you can use Zapier and the Google Maps API to do this pretty easily. You can either do it on new records, or if you have a bunch of records (or don’t want to risk Zapier running before you finish typing one in) you can create a view that filters out anything that doesn’t have a specific state (e.g. a checkbox called “Zapier” so that records in your normal view only get added to the filtered view if that checkbox is checked, thereby preventing Zapier from seeing any new records until you’re ready for it to see them).

Actually, if you’re just trying to make a map with a bunch of locations using geocoded addresses (lat/lon coordinates) then you don’t even need the Google Maps API. You can just build a URL directly in Airtable like this:

  1. Start with: https://www.google.com/maps/dir/
  2. Append the latitude and longitude coordinates to the URL in this format: lat,long//lat,long//lat,long//lat,long

Basically, latitude and longitude are separated by a comma (as per the usual) and each set of coordinates is separated by two forward slashes. A single forward slash will tell Google Maps you want directions between all of these places. Two, however, will just make them points on the map (and the first one the “origin” with the familiar red marker). If you already store your geocoded addresses in the lat,lon format in a text field (or use a formula field to combine them that way if not: latitude&’,’&longitude is the basic formula, btw, presuming those are your field names for each), you can just make a new table in your base that links to the table with addresses and uses a rollup field to combine them with the ARRAYJOIN() function using // (the two forward slashes) as your separator. I think you can just prepend the Google Maps URL to that by putting it in quotes and sticking an & between it and the ARRAYJOIN() function but I’m not sure if rollups are that flexible. This is slightly easier to do with the same approach while using the Airtable API, and you can do a lot more with that URL afterwards, but if you want to keep it in Airtable you have to take those extra steps—at least, I think that’s the case. It’d be nice if you could keep it all in one table but I haven’t figured out a way to roll up all the records in the same table and I’m pretty sure Airtable told me that can’t be done about three months ago. But, if they implement field summary functions for formula fields one of these days that’ll change. :slight_smile:

But, if you’re intent on using the Google Maps API and Zapier to do something else with those geocoded addresses you can just make an action that triggers on a new record (or a new record in a view) and use the Zapier Webhooks action to process the API request. You’d want to use a GET action because the Google Maps API uses GET. You generally need geocoded location data to query the API, but the API can generate that for you if you don’t have it (like the original poster). If you need to geocode your address(es) first, you can just use two webhook actions to do this. The zap would look like this:

  1. Airtable -> New Record (or New Record in View)
  2. Zapier Webhooks -> GET: Google Maps API call to Geocode an Address
  3. Zapier Webhooks -> GET: Google Maps API call [X] using geocoded location data from the previous call
  4. Profit???

If you don’t need to geocode, just leave out #2. For the final step, you just add an action that does something with the processed data. I came across this post when looking to see if Zapier could handle geocoding without an extra call, just to keep things simple, but it can’t (which is why I am posting this—I figure someone else might search for this information someday). I wanted a way to find the hospital closest to any given address so my step #3 call uses the geocoded address to look that up, then add it into Airtable. But we’re dealing with Zapier here, so if you want to send the result elsewhere you can. So, #3 and (especially) #4 depend on what you’re trying to accomplish.

Anyway, I hope this helps (someone)! It may sound kind of complicated, but it’s quite simple. The whole process is just hard to explain (or, at least, sounds a little convoluted when explaining) because I don’t really know the exact reason for wanting to do this. In some cases the solution is very elegant and simple, and in other cases it’s a bit more encumbered—but only due to the number of steps! All steps are straightforward! Just make sure you have a Google Maps API key set up properly if you’re going to use it. (It’s kind of hard to skip over that step without knowing, but you never know!)


#5

For occasional use, you can use BatchGeo - just add an extra column at the top of your data with column headers and copy and paste your address columns. It doesn’t address your need for automation, but I found it handy for a one-time map of our membership to see what local neighborhoods we weren’t reaching.

You don’t even need lat/long data, it works perfectly well with addresses.


#6

Hello,

Have you personally tried any of these suggestions on how to get the latitude/longitude to populate in a column if there is an address provided? Is there any way you could provide some screenshots how this should be done. Like you mentioned it’s sounds really complicated. I need this feature for programming amenities maps in buildings directories where I only have to put in the address and the longitude and longitude will automatically populate so I don’t have to go to https://www.latlong.net/ to find it. Please me know if your could help with the screenshots of how to do this. Thank you I really appreciate all of your help. Best Regards,
Teresa


#7

Oh wow, I almost forgot I wrote that! :stuck_out_tongue: It sounds a lot more complicated than it actually is because Google likes to make everything that way. The Maps API isn’t too difficult to use, though, so don’t be intimidated by it. If you’re not getting it, there’s at least an equal chance that’s just bad documentation on Google’s part and not your fault. Saying that doesn’t make it any easier, though, but it’s not designed in a way that’s easy to explain either.

Anyway, yes, I’ve done this myself but it has been awhile. Let me see if I can dig up some screenshots for you…

Okay, I found a relevant zap and took some screenshots. I don’t actually get lat/long data with it but it comes in all the same. You can see in four screenshots how this works in Zapier.

Basically, you just grab a new Airtable record (generally you want to do this from a specific view so you can add a “Ready for Zapier” checkbox it filters on so no records get sent to Zapier before you’ve finished entering the necessary information) and then use the information in it for Zapier’s built-in Webhooks action. It’ll figure out what to do with the response from Google for you and you just need to tell it what you want to do with the latitude and longitude information (in the test screenshot—the last one—I scrolled down a tiny bit so you could see what the lat/long info looks like). From there you just create an Airtable action to update a record, tell it to update the record that triggered the zap in the first place (this is a little unintuitive, but when it asks you which record you want to update you have to choose “Use a Custom Field” or something along those lines and tell it to use the record ID of the record from step one—the trigger). Aside from setting the record, you only need to tell Zapier where to put the lat/long info (i.e. which field in the Airtable record—probably one you named something like latitude/longitude).

Once you’ve done that you just need to test the zap to make sure it works and turn it on. Then it’ll just do everything for you from then on. :slight_smile:

I hope that helps!
– Adam

P.S. My zap has a lot more steps because it looks up multiple things each time it runs. Yours will not require so many steps—probably just the Airtable new record trigger, the Webhook “GET” action, and the Airtable update action. I think the harder part is configuring Google’s Cloud API system to actually allow for calls. It tends to be easier with Maps if you’re just doing this kind of stuff, but it’s always more of a convoluted process than it has to be and there isn’t really a good way to walk you through that visually. If you get stuck on the Google side of things, you might want to check this out: https://zapier.com/blog/updates/1440/lob-verify-address-actions. That should prevent you from making an API call through Zapier in the first place. I’ve never used Lob, but it looks like you get 300 requests for free each month and since Zapier has an integration you might find it easier than making a direct API call. (Personally I find the API calls easier to work with than a lot of Zapier integrations, but that’s probably because I enjoy programming on my free time and it’s a more familiar format to me.)


#9

Hello,

I am following your directions and it looks like everything will work great but the only thing is that Google now wants to charge for the API key so I was wondering if I could use Bing maps instead because I have their API key. Is there a different URL that I can type in instead of https://maps.googleapis.com/maps/api/place/textsearch/json that perhaps Bing maps provide. Thank you in advance for all of your help.

Best Regards,

Teresa


#11

Hi Teresa,

First of all, great job on getting this far already!

Second, if this situation turns out to be unresolvable I want to make sure you saw my PS on my last post because I suggested an alternative to Google (called Lob) that has a direct integration with Zapier so you don’t have to bother with an API call. I just haven’t used it before so I don’t know much more than what I said about it.

Third, the best I can do is guess why you’re hitting an error and my guess is that the API key isn’t associated with the right permissions or a billing account. Even when you’re on the free trial, Google sometimes wants a credit card associated with the account anyway. (I have yet to figure out what determines this since it seems arbitrary in my experience.) Without seeing your account, it’s really hard to know what the issue is unfortunately. But I’d be surprised if it was anything other than these two things and it’s most likely permissions-related. Basically, with Google Cloud APIs you have to associate each service with a project and that project has to have specific permissions in order to run. It’s super annoying, but companies like Google have to do this to keep things as secure as possible. I just wish they’d write better documentation than they usually do. So, it’s with some hesitation that I point you in the direction of their relevant documentation:

https://developers.google.com/maps/documentation/maps-static/get-api-key

Make sure you’ve followed all the steps there (aside from installing any developer tools if it says to—you’re not writing code here so you don’t need those). If you did set up billing, by the way, you will need a digital signature to accompany the call. If you have not set up billing yet, I would recommend against doing that unless nothing else works. If you did set up billing, you probably just need to include a digital signature with your request through Zapier.

In regards to costs, you should get 25,000 free requests from Google before they charge you. But yes, if you want to use Bing I’m sure you can. Any API should work fine and I don’t see how Bing Maps could function without providing lat/long data so I have to imagine it’d work just fine in this case. I just haven’t used that API, personally, so I don’t want to assume anything incorrectly. So, I can’t tell you what API endpoint (the URL) to use for Bing Maps but searching for “Bing Maps API endpoint” should turn up something useful. You may also find it in their documentation:

https://msdn.microsoft.com/en-us/library/ff701713.aspx?f=255&MSPPError=-2147217396

That said, if it’s easier to just stick with Google you might actually be able to get better instructions on getting a functional key for Zapier by following instructions from Airtable. :stuck_out_tongue: It’s a little roundabout, but because the same key is used for the Maps Block there are instructions in the Airtable Help Center that are much easier to follow. You can find them here (just scroll down the page a little to get to that section):

You can also find a simplified set of the instructions by just creating a Map Block and going through the steps to set it up. If the Block works, the same key should work with Zapier as well. That may be an easier way to solve the issue.

I hope this gets you the rest of the way! You’re almost there, so don’t worry—you’ll make it. :slight_smile:

  • Adam

#12

Hello Adam,

Thank you for all of your help! I finally did it. I did it with Google API and everything works perfect! J

Best Regards,

Teresa


#13

Awesome! And you’re welcome. I’m happy I could help. :blush:


#14

Let me chime in here with a :thumbsup: for the Airtable Map Block. It is a nearly painless way to integrate Airtable with Google Maps. I used it for the first time recently for my San Francisco Tenant Buyouts base in Airtable Universe and was delighted with the results. I especially liked the feature where I could vary the size of the map indicator based on the value of another field — in my case, I used the per-tenant amount of buyout as the determining value.

A couple of caveats, though. The dataset as downloaded from SFData enclosed latitude and longitude values in parentheses, as such — (37.743953743370575, -122.42143054619228) — which kept the Block from working. I knocked out a quick formula that stripped off the offending characters — SUBSTITUTE(SUBSTITUTE({LatLong},'(',''),')','') — and everything went fine. Also, the base as published in Airtable Universe will not display maps until you enter your own Google Mapping API key in the Block configurations.

Here’s one of the maps from that base, color-coded by ZIP code and with marker size based on the per-tenant buyout amount.