00:00 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.
00:08 I'm gonna show you how to do this with a full circle bookstore. Full circle bookstore. I did bookstores in Oklahoma City.
00:30 Alright, 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.
00:36 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.
00:50 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.
01:01 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.
01:12 It's 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.
01:23 Basically, you can try to get as much information as you want. But let's see. Bookstores. Austin, Texas. Let's do that Google Maps search.
01:35 And here, book people. Black. you Pearl Books. Half Price Books. Do we get those? Yes. Book people. Black Pearl Books.
01:41 Half Price Books. They're here, right? So, the Google Maps API and the Google Sheet are working. It is getting the exact things we want.
01:48 So, let me walk through the code a little bit. Then I'll show you how to get your API key. Basically, the first three things are just the data.
01:56 Like, the API key that we need. We're gonna have a query here. We can put the- this query into our sheet if we want.
02:03 Right now, I have it in the code. I'm gonna maybe show you how to put that in the sheet. Umm, this URL is the Google Maps URL.
02:10 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.
02:20 This is Google Apps Scripts way to go out into the internet. Go to- do a website and pull back whatever is there.
02:27 Then we're gonna take that data, which it's an API. So we're gonna get back JSON, J-S-O-N. So we could put that into the sheet, but we don't really want to.
02:38 We want to parse through that, meaning we want to look through all of that JSON and pick out the little bits of data that we actually want.
02:44 So we're gonna use JSON.parse. And now we're gonna get data that's really- Really? Easy to parse. And I'm gonna skip this sheet right here, this part, but basically they're gonna go to data.results for each.
02:58 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.
03:04 We're gonna take that thing, get the dot name. There. So JSON allows us to do this really nice formatting to get the data.
03:12 We want the name, the format. Had it addressed, the rating. We want the user total ratings, place ID. And if there are none, it has this double pipe, which means OR.
03:20 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.
03:33 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.
03:44 And it's going to append literally these words. So name, address, rating, user ratings, place ID, as headers, then it's gonna place all of these things.
03:53 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, You have, and trying to get the phone number with it, because it has a little bit extra layer of complexity.
04:07 But this works as is, uh, to get just these queries, take the query, get it into your Google sheet. I want to show you where to set that up.
04:18 So basically, you can go on developers.google.com. And this is how to set up the Places API. API. Uh, if you want to Google for that.
04:27 And it'll bring you to this, where you need two things. You need an account with billing. And you need a project.
04:36 I'm not going to show you how to do either of those in this video. That's for you to deal with on your own, if Google accounts.
04:44 But once you do have that, click this Enable the API. And it'll bring you here. here. To Places API. And you can enable this.
04:52 And then manage it. And when you manage it, you just create a key. It has some other things that you can do.
04:58 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.
05:06 Just the API key. Grab it. Bring it back here. If you're watching this on bettersheets.co, down below, I have put this.
05:13 This entire sheet available for you to copy and click. Just copy it to your Google Drive. This API key will have been deleted by the time you use it.
05:22 So I want to show you a couple of edits that I'm going to do to this, this script. So that it might be easier for you to use in your sheet.
05:34 Because right now you have to come to App Script to really use this. You have to- Put the API key here.
05:38 You have to put the query here. So I'm going to copy all of this. And actually I'm going to delete this in rich with phone numbers and I'll just do this another time.
05:46 You might see a follow up video. Search places in sheet. And I'm going to put a query here. And I'm going to delete this query from here.
05:56 So this is variable. And basically I'm going to say, okay, put in this function. . And get the results. Not the results here.
06:09 So, let's do this one second. Actually I'm not going to put the query right there. I'm going to do something else.
06:20 Okay, so basically what I'm going to do is I'm going to add this to our sheet. Up here, at the top, in here.
06:28 And I want to, I don't want to clear the sheet either. I don't want to get this, create a sheet by name.
06:36 I want to, uh, I want to, if, no sheet, uh, I want to get this, move it up. Up here, and call it query.
06:53 Do, do, do. Basically, take the query you want, make it a sheet name, and if there is none, then we're going to create one.
07:10 We're going to insert that sheet. We're going to get, uh, uhh, uhh, uhh, set name, and it's going to be the query that we get.
07:25 We need up here, uhh, the very top. Actually, let's put this all at the top. const ui equals spreadsheet app dot git ui.
07:45 And this is a different response. This is like, we're asking search response, query response. Query response is ui dot prompt.
07:59 And we're just going to ask it for, uh, google maps. Search is going to be like the name. Enter, uh, query.
08:14 And we're gonna take the ui and create a button set. Okay, and cancel. So if response. Pawns. Dot git. Oh, sorry, this response is git selected button.
08:46 and And if it's not equal to ui dot button dot okay, meaning it's cancel, then we're gonna return. We're gonna just skip all of the rest.
09:02 But if it's not, not okay. If that makes sense, we're gonna get a query. It's equal to, query response dot get response text dot trim.
09:19 So if there's any sp- extra spaces or anything. And we're going to get the sheet called query.
09:34 And if there is none, then we're going to set the name query. theory. Uhh, we're going to create a name, sorry, create a sheet.
09:43 Name it that query. And then we're going to run all of this. And we're going to put it in the sheet called query.
09:52 So let's save this. And I want to do one more thing called function on open. And on open we're going to create a menu.
10:01 You can go to better sheet- Zucco slash snippets. And here we have on open. We have an on open menu.
10:15 Here's the one function one. We can copy this if you want to create this kind of thing. Which it just says.
10:26 Create a menu. It's called custom menu. I'm going to call this Google Maps. Not menu. And we're going to take the search places in sheet.
10:35 Put that function there. And then we're going to call it search Google Maps. There you go. We've set up this on open.
10:43 We've set up this function that's going to run when we click that button. Let's close our Apps Script once we've saved it.
10:49 We're going to, We're going to refresh our sheet. Once we refresh our sheet, that means it closes it and opens it again.
10:56 And now we have Google Maps. And now I have a Google Maps menu up here. I can click in search Google Maps.
11:03 It's going to ask me to enter a query. So what's the query book stores in, Baton Rouge Louisiana.
11:19 Click OK. And now it's going to create a sheet. And it says, ah, property of null. Okay, so we have to umm, go back to our App Script and edit something.
11:32 So what we need to do is change our const sheet up here. To a variable. So, or let. Let's try variable.
11:45 Uh, because const means it's going to stay the same the whole time. Let's try that now. Let's delete our sheet here so that we don't have it.
11:58 Let's do Google Maps. Let's do this. Same query. We need to do let then. Well, that's going to have the exact same sheet.
12:22 So, let's make sure we delete that sheet. So, what I'm going to do is, instead, just assume that the sheet doesn't exist.
12:38 So, don't put this if. So, we're just going to insert a sheet with query. And then we're going to say, let the sheet equal this sheet.
12:46 Uh, we can do variable here. It doesn't matter. So, let's say, Do this. And let's smith and try again. Bookstore in Baton Rouge.
13:04 And here we go. It's done. So, we're creating the new sheet. We're running the script and getting back results. Pretty.
13:14 Cool, right? This was all able to be done in Google Apps Script with a single API key, uh, with the Google Maps API.
13:24 Pretty awesome. I want to show you to, uh, some more deeper, cooler stuff about this, but go down in the description below.
13:33 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.
13:42 So what you might want to do is take this API key out of here, and put something like spreadsheet app.getactive spreadsheet, get sheet by name, call it API key, or API, yeah, key, uh, git range, and we're going to say one comma one, which is also, .
14:03 . . n quotes a1, but I like to use this row and column kind of syntax. So one comma one is going to be your a1, and .get value.
14:17 So make sure you go back to your sheet, create a sheet called API key, and put your API key right here in a1.
14:24 We can also delete all of the other. If you're going to subscribe. Stuff. And there you go. Make sure you don't share the sheet with anyone you don't want using your API key.
14:34 Of course, again, by the time you watch this video, I'm going to roll this API key. It won't exist anymore.
14:39 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.
14:49 It's actually in the sheet. . Bookstore in San Francisco. So here's the query in the sheet, and it's running it. Awesome.
15:05 Pretty cool, right? Then we can do this. So if you're watching this on better sheets, go down below. Get the sheet now.
15:11 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.
15:19 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.
15:30 I think it'll be very interesting to see how others use it. Then also, if you're not watching this on better sheets, I'll go, uh, then ask any questions you want.