Help

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

4931 0
cancel
Showing results for 
Search instead for 
Did you mean: 

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!!!

21 Replies 21
Carrie_Leigh
4 - Data Explorer
4 - Data Explorer

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

Greg_Trepanier
4 - Data Explorer
4 - Data Explorer

Anyone have a solution?

Adam_Dachis
6 - Interface Innovator
6 - Interface Innovator

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. :slightly_smiling_face:

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!)

Douglas_Snook
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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

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.

Screenshot 2018-07-30 13.04.51.png

Screenshot 2018-07-30 13.04.59.png

Screenshot 2018-07-30 13.05.09.png

Screenshot 2018-07-30 13.05.29.png

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. :slightly_smiling_face:

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.)

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

Adam_Dachis
6 - Interface Innovator
6 - Interface Innovator

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. :slightly_smiling_face:

  • Adam

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

Adam_Dachis
6 - Interface Innovator
6 - Interface Innovator

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

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.

buyout_map

Hugo_van_Schaik
4 - Data Explorer
4 - Data Explorer

Is there a way to get google street view images into Airtable and push those as attachments to cards in Trello?

I used to use ZeeMaps that synced with Google Sheets (which you can zap to airtable). ZeeMaps is a dynamic map with either push button sync or sync every few hours. The layers are custom as well as the map markers. I loved it because I could reshape/resize markers based on column data for situations where I had multiple markers (projects) at the same location.

However, to avoid the extra $19.99 I am using the AirTable map block and using two screens to go back and forth to filter my data (layers). However, w/out further manipulation to the data I cannot see multiple pins at the same location.
Now I am searching for a map API to incorporate my google calendar events on a dynamic map that is part of My Maps on not the app itself.

robert_cowling
4 - Data Explorer
4 - Data Explorer

@Adam_Dachis this is an old thread but I found your solution to be very helpful. I am new to Zapier and Airtable and am trying to geocode a bunch of existing records. The solution does work on a single record or a new record but how do I instruct zapier to run through every existing record in my airtable and geocode each one? When I goto update the airtable in zapier, I can select a record under ‘Customize Record’ but I want to do it for all existing records in the table.Thanks very much!

That’s a limitation of Zapier. If records already exist, they just won’t trigger. It’s why I prefer using the API for most things like this, but that’s obviously not a viable solution for everyone. There are a couple of workarounds to overcome this limitation. First, you can copy and paste the records, then delete the old ones so everything looks new to Zapier. This is the fastest and simplest option if you’re just trying to accomplish this once. Everything should remain intact after pasting and if anything goes wrong you can hit undo. Alternatively, you can have Zapier look for new records in a specific view and filter it on a checkbox field you create for that view. Then you can check the box on an existing record and Zapier should see it as a new addition. Once Zapier sees it in that view, however, it’ll never process it again. It’s not a good workaround if you’ll need records to update.

If you need to change records and have them reprocessed, you can create a zap in Zapier that runs at a set interval. I can’t remember offhand what the trigger is called but it’s probably “timer” or something like that. It’s the Zapier equivalent of a cron job. Basically, you tell it how often to run and then set it up to search Airtable for records that match something specific. You can create a status field in Airtable (with a single select or even just a checkbox) and then have Zapier search for records in that table with a checkbox. If it finds one, have it run the lat/long API call and update the record. As part of the record update, make sure to have Zapier uncheck the box/update the status field so it doesn’t keep trying to re-process the same record over and over again. If you have a lot of records they’ll update at a fairly slow pace, but this will work well for when you have occasional updates.

You can also do both of these things with date and time information (see here).

If you need to speed things along, you can also make a zap that triggers on a webhook. You can set it up so you pass the webhook URL that Zapier gives you the ID of the record you want to update. You basically just make a formula field in Airtable that creates a URL with that information and then you can just click that link in the record you want to update and Zapier will perform the operation instantly. This is kind of a long operation to explain, but I finished a guide on how to do this (for another purpose) a little while ago and I’d be happy to share the link once it’s published if you’re interested.

None of these solutions are perfect and, hopefully, there will be a better way to handle existing records with Zapier in the future. It’s very easy to do with the API so it’s definitely possible to implement in Zapier (because it’s basically a GUI for APIs). When I can find the time, I’ve been working on a simplified way of making Airtable API calls. It works great if you can write basic PHP code but I’m hoping to make a GUI that can handle this kind of stuff. I don’t have the resources to support something like that, but if it’s good enough I’ll share it anyway and maybe the community will pitch in and help make it better.

But for now, hopefully one of these options will work for you!

Just used the copy, paste and delete solution. Works great. Thanks a lot!

Glad to hear it! :slightly_smiling_face:

Moe
10 - Mercury
10 - Mercury

If you would like to generate a map out of your Airtable records and embed it on your website, we’ve built a tool that allows you to do just that.

RnJ
8 - Airtable Astronomer
8 - Airtable Astronomer

@Moe, any plans to make an extension to convert address to lat/long?