Scrape Google Maps in Google Sheets
Learn how to scrape Google Maps data directly into Google Sheets using Google Apps Script. This tutorial covers setting up the API key, writing the script, and customizing your queries for effective data retrieval.
This is a Google Sheet with Google Maps results. These are the Google Maps results that I got, but I didn't actually copy and paste anything. I'm gonna show you how to do this with App Script , but first, let me show you how this works and what's so cool about this. Basically, I can do a Google Maps search in Google Sheets and get the results. This is literally the results, right? Full Circle bookstore. Full Circle bookstore. I did bookstores in Oklahoma City. All right, let's, let me show you live how this works. So I'm gonna go to my app script and I'll walk through this later in the video. But right now I wanna change this query to bookstore in Austin, Texas. And all I'm gonna do is save that and hit run. I have my API key already. I'm gonna show you how to get that or I'll show you where to get that.
Not all the steps because it's, it's fairly up to you to have a Google account. But let's go look. Here's book people. Here's first Light Bookshop, black Pearl Books. Let's compare this. Now, uh, I don't know if I did that in real time, but it was like just a moment or two to get that this data here, it has place IDs if I want to do a deeper. Search of like their data , but I also have their address, their rating, and I'll show you some other things you can add to this code. Basically, you can try to get as much information as you want. But let's see. Bookstores, Austin, Texas, let's do that. Google Map search and here. Book People, black Pearl Books. Half Price Books. Did we get those? Yes. Book people, black Pearl books. Half price book, they're here. Right? So the Google Maps API and the Google sheet are working.
It is getting the exact things we want. So let me walk through the code a little bit then I'll show you how to get your API key. Basically the, the first three things are just. The data like the API key that we need, we're gonna have a query here. We can put this query into our sheet if we want. Right now I have it in the code. I'm gonna maybe show you how to put that in the sheet. Um, this URL is the Google Maps, URL, and it's just saying what's the query ? What's the API key? Go send those. Get the data back, and the data back is gonna be gotten with URL fetch, app fetch. This is Google Ops scripts. Way to go out into the internet, go to a website and pull back whatever is there. Then we're gonna take that data , which it's an API. So we're gonna get back J-S-O-N-J-S-O-N.
So we could put that into the sheet, but we don't really want to, we wanna parse through that, meaning we wanna look through all of that JSON, and pick out the little bits of data that we actually want. So we're gonna use JSON parse, and now we're gonna get data that's really easy to parse. And I'm gonna skip this sheet right here, this part. But basically they're gonna go to data that results for each and for every single result, we're gonna go to it as if it were a place. We're gonna name that as a variable place. We're gonna take that thing, get the.name there. So JSON allows us to do this really nice formatting to get the data we want the name, the formatted address, the rating, we want the user total ratings place ad. And if there are none. It has this double pipe, which means, or basically if it can't get
a rating, it'll just put a blank. Um, this is really nice because sometimes when code is running and it gets to a null, it might stop. So it's nice that it does that. Basically, it's gonna go to the sheet called results here. So if you have a different sheet, call it something else here, write that name here, or go and just create a sheet called Results, and it's going to pen literally these words. So, name, address, rating, user ratings, place ID as headers . Then it's gonna place all of these things. And that's it. That is the, all of the code over here. I'm working on another part, which is taking, uh, the ID that you have and trying to get the phone number with it because it has a, a little bit extra layer of complexity. But this works as is, uh, to get just these queries.
Take a query , get it into your Google sheet. I wanna show you where to set that up. So basically you can go on developers.google.com and this is how to set up the places API. Uh, if you want to Google for that, and it'll bring you to this where you need two things. You need an account with billing and you need a project. I'm not gonna show you how to do either of those in this video. That's for you to deal with on your own with Google accounts. But once you do have that, click this enable the API and it'll bring you here to places API and you can enable this and then manage it. And when you manage it, you just create a key. It has some other things that you can do. It offers you like the idea of a step through steps to
create like a map yourself. But don't, you don't need any of that, just the API key. Grab it, bring it back here. If you're watching this on Better sheets.co. Down below, I have put this entire sheet available for you to copy in a click. Just copy it to your Google Drive. This API Key will have been deleted by the time you use it. So I wanna show you a couple of edits that I'm gonna do to this, this script, so that it might be easier for you to use in your sheet. Because right now you have to come to App Script To really use this, you have to put the API key here. You have to put the query here. So I'm going to copy all of this and actually I'm gonna delete this Enrich with phone numbers, and I'll just do this another time. You might see a follow up video search places in sheet, and I'm
gonna put a query here and I'm gonna delete this query from here. So this is variable, and basically I wanna say, okay, put in this function . And get the results, not the results here. So let's do this one second. Actually, I'm not gonna put the query right there. I'm gonna do something else. Okay. So basically what I'm gonna do is I'm gonna add this to our sheet up here at the top. In here, and I want, I don't wanna clear the sheet either. I don't want to get the sheet, create a sheet by name. I want to, I want to, if no sheet, uh, I want to get this, move it up here and call it query . Do, do, do. Basically take the query you want, make it a sheet name , and if there
is none, then we're gonna create one. We're going to insert that sheet, we're gonna get, uh, set name, and it's going to be the query that we get. We need up here, uh, at the very top. Actually, let's put this all at the top. Cons, UI equals spreadsheet , app dot, get ui. And this is a different response. This is like we're asking. Search response query response. Query response is UI prompt. And we're just going to ask it for, uh, Google Maps. Search is gonna be like the name enter, uh, query . And we're gonna take the UI and create a button set. Okay. And cancel. So if response do get, oh, sorry. This response is get selected button. And if it's not equal to UI button, okay, meaning it's
cancel, then we're gonna return. We're gonna just skip all of the rest. But if it's not, not okay, if that makes sense, we're gonna get a query SQL to. Query response. Do get response, text, trim, so if there's any space, extra spaces or anything. And we're going to get the sheet called query . And if there is none, then we are going to set a name query . Uh, we're going to create a a name, sorry, create a sheet. Name it that query . And then we're going to run all of this and we're gonna put it in the sheet called Query . So let's save this. And I wanna do one more thing called Function on Open. And on Open. We're going to create a menu. You can go to Better sheets co slash snippets, and here we have an open.
We have an on open menu. Here's the one function one. We can copy this if you wanna create this kind of thing, which just says create a menu. It's called custom menu . I'm gonna call this Google Maps not menu, and we're going to take the search places in sheet, put that function there, and then we're gonna call it. Search Google Maps. There you go. We've set up this unopen. We've set up this function that's going to run. When we click that button, let's close our app script . Once we've saved it, we're going to refresh our sheet. Once we refresh our sheet, that may means it closes it and opens it again. And now we have Google Maps. And now I have a Google Maps menu up here. I can click and search Google Maps. It's going to ask me to enter a query .
So what's the query ? Book stores in Baton Rouge, Louisiana. Click okay. And now it's gonna create a sheet and it says Our property of null. Okay, so we have to, um, go back to our app script and edit something. So what I think we need to do is change our const sheet up here. To a variable. So, or let, so let's try variable, um, because cons means it's gonna stay the same the whole time. So let's try that Now, let's delete our sheet here so that we don't have it. Let's do Google Maps. Let's do the same query . We need to do let then, well, that's gonna have the exact same sheet, so let's make sure we delete that sheet. So what I'm gonna do is instead just assume that the sheet doesn't exist. So don't put this if, so, we're just gonna insert a sheet with query and then we're
gonna say, let the sheet equal this sheet. Uh, we can do variable here. It doesn't matter. So let's save this and let's dismiss and try again. Bookstore in Baton Rouge and here we go. It's done. So we are creating the new sheet, we are running the script and getting back results. Pretty cool, right? This was all able to be done in Google App script with a single API key, uh, with. The Google maps API pretty awesome. I wanna show you two, uh, some more deeper cooler stuff about this, but go down in the description below, um, if you're watching this somewhere other than better sheets and ask questions. Okay? So I want to try to move this API key somewhere else.
So what you might wanna do is take this API key out of here and put something like spreadsheet app, get active spreadsheet . Get sheet by name. Call it API key or API. Yeah, key, uh, GI range . And we're gonna say one comma one, which is also in quotes, a one. But I like to use this row and column kind of syntax . So one, one is gonna be your A one and get value. So make sure you go back to your sheet. Create a sheet called API key and put your API key right here in a one. We can also delete all of the other stuff. And there you go. Make sure you don't share this sheet with anyone you don't want using your API key. Of course, again, by the time you watch this video, I'm gonna roll this API key.
It won't exist anymore, but that little code there, let's save it and let's run it again. And just to make sure that the API key is working correctly, that it's not in the script itself. It's actually in the Sheet bookstore in San Frame Cisco. So here's the query in the sheet and it's running it. Awesome. Pretty cool, right? We can do. So if you're watching this on better sheets, go down below, get the sheet now. The sheet that I put in there is going to be copyable into your, uh, Google Drive, and you'll be able to use your own API key. If you're not watching this on better sheets, then become a member today to grab this or go, uh, back in the video and pause and, and copy the code yourself. I think it'll be very interesting to see how others use it. And also if you're not watching this on ko, uh, then ask any questions you want.
You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.