WESLEY CHUN: Hey, all.
This is Wesley Chun.
Welcome to another Google Apps Script episode.
If you're new to that technology, pause and click
on the link in the description below
to watch the intro video first, then rejoin us here.
All right.
Now you may be wondering, accessing
Google Maps from a spreadsheet?
Sound crazy?
Did you think I made it up just to get your attention?
Well, quite the opposite actually.
In this video, I'll teach you to write code that not only does
what's advertised, but also why it's useful to do this.
And that little star there means that I may also
throw in a little extra surprise at the end, so stay tuned.
In this episode, we're going to start with a new Google sheet
and put some data in it, such as a street address.
Then we're going to have our spreadsheet ask
Google Maps to create a map for us, specifically
a map of that address that's in the spreadsheet.
This is fine and all, and you can easily
imagine more than one address, right?
The problem is putting each address into Google Maps.
You'd have to go back and forth between Maps
and the spreadsheet, right?
Well, what if I could just click a link in the spreadsheet?
That's what we're going to do.
To complete the circle, we'll save a link
to that map back into the Google sheet.
As mentioned earlier, the technology we'll be using
is Google Apps Script.
The documents and spreadsheets in your Google Drive
can actually be programmed using a subset of JavaScript
called Apps Script.
Apps Script code can access data in Google Apps,
as well as call other Google services,
Google Maps being one of them.
Again, if you haven't seen that video yet,
or if you're new to Apps Script, please check it out first.
The link is still in the description.
It hasn't moved since we last mentioned that.
Or here it is for your convenience,
if you're on a computer.
Another reason to watch it first is
because we're going to build on its "Hello World" example
that you see here.
If you're all set, then let's go to the computer now.
All right.
Before we get started, I want to strongly encourage all of you
to do this with me, because doing the hands-on
is much more valuable than just watching a video.
You can pause and resume as often as you need to,
because I'm not going anywhere.
We're going to take what we learned in the first Apps
Script episode and build on from there,
allowing your spreadsheet to talk to Google Maps.
Just like we did the last time, go to Google Drive
and create a new Google sheet.
When you're done, come back and join me here.
All right.
Let's go.
Start at the first cell and put any street address into it.
For example, we'll use 76 9th Avenue in New York.
Feel free to expand your spreadsheet columns
if you need to make them wider so that you can see everything,
but it's optional.
And that's it.
It's time to write some code.
Go up to the Menu bar and select Tools.
Then pull down Script Editor.
Just like in the previous episode,
we're going to start with Blank Project,
because we're not really going to write
that many lines of code.
Four, to be exact.
And also just like the last episode,
we need to get the current spreadsheet,
so we're going to use Spreadsheet
App and its Get Active Sheet method
to get the current sheet.
This time, let's save it to a variable-named sheet,
because we're going to use it more than once.
Naming make sense, right?
All right.
Last time, we used the Get Range method
to get the first cell, which was A1, remember?
We're going to do something similar.
We're going to use Get Range again,
and we're also going to use the A1 notation.
Let's just confirm that by taking a quick peek back
at the spreadsheet to confirm where our address is.
And there it is.
But instead of using Set Value to write "Hello World"
into that cell, we're going to use Get Value to pull out
the address and save that to a variable-named address.
Again, appropriately-named.
All right.
Now here comes the magic part.
Spreadsheet App is the Apps Script endpoint
for accessing Google Sheets.
Now in order to talk to Google Maps,
we need an equivalent object.
And that's going to be Maps.
As you can see here, the Maps object has many methods,
but the one we care about is New Static Map.
What are we going to do?
We're going to create a new Google map,
but we also want a map that has a pin on that address that
was in the first column.
The way you do that is you pass the address to the Ad Marker
method, like you see there, and now let's
save the map to a variable named "that."
Again, appropriately-named.
Then the final step is to store a link
to that map in the cell next to the address in the spreadsheet.
So we're going to use the sheet's Get Range method again,
also A1 notation.
We saw a moment ago that the cell next to the address is B1,
so this time we're going to use its Set Value method in order
to write that map's link to the cell.
And you can get that by using the Maps' Get Map URL method.
And that is it.
Oh.
If you haven't named your script yet,
you're going to be prompted, as before,
to do so when you save or run it.
We'll call it Google Maps demo, for lack of a better name.
Now when we run it again, you'll see
that we need to do the OAuth thing,
and grant this code access to our spreadsheet data.
Once you opt-in and are able to run it successfully--
and you can tell it's done when the yellow run dialog shows
up and disappears-- and if everything went according
to plan, when we pop back into the spreadsheet,
you'll see the map's link in cell B1.
There it is.
And if you click on it, you'll see
that it's the map that we asked Google Maps to make, along
with a pin at that address.
Isn't that awesome?
All right.
That's it.
We only needed four lines of JavaScript
connecting a spreadsheet to Google Maps.
Hope you enjoyed this demo, and more importantly,
are able to see how, in a few lines of code,
you could do something that could almost
be considered useful.
So was this all for show?
Was this just a party trick?
How can you take this and turn it
into something really useful?
Well, there are many possibilities.
Perhaps you've got the addresses of all your local customers
in New York City, and want to plot them on a Google Map
to help you plan to visit them all in a single day.
Well the code you just completed is your launch point.
Or maybe this is the monthly user group.
Or a club meeting.
Right?
Well instead of manually entering in the spreadsheet
data, perhaps you pulled the membership
so that the data comes from a Google form.
As you can guess by now, Apps Script
gives you the ability to turn generic, boring spreadsheet
data into an app, processing and managing member info as well
as possible meet-up locations.
To find out more about what you can
do with Google Maps from Apps Script,
check out the first link that you see here.
Of course, Apps Script gives you access not only to Maps,
but other Google services, too.
To find out about what other services are available to you,
check out the second link.
Finally, the resources at the bottom
will point you to more episodes in this and other developers'
series where you can subscribe to always get our latest
episodes.
That's it for now.
But as you can guess, it's just the beginning for you.
Stay tuned for future episodes, where
we'll do more stuff with Maps, Forms,
and explore other Google services from Apps Script.
This is Wesley Chun from Google, and we'll see you next time.
What-- wha-- you're still here?
It's over.
Go back to work.
All right.
Fine.
Apparently you won't go away until you get some more.
All right.
Let's go back to the computer, then.
When we last left off, we had just gone back
into the spreadsheet to click on the link in the cell
next to the address to see the map that Google Maps had
generated.
Let's go back to the code and revisit the fourth and final
line.
Sure, it's a good demo of how you
can write data such as the map's link back to the spreadsheet,
but we can do something more exciting, right?
Follow along with me and change the first part
of the line, specifically this part here.
Call the MailApp object's Send Email method.
Oh, and by the way, choose the second one
that takes three parameters.
We'll start with your friend's email address as the recipient.
Also enter a subject line.
Choose something like, I don't know, map?
And finally, reuse the link to the map
as the body, which is the final argument.
And that's it.
Any guess as to what this does?
Hint-- it's got something to do with Gmail.
Yes, that's right.
Instead of writing the link back to the spreadsheet,
don't you think it's way more cool to send
this map to my friend?
Well in this demo, I'm just going to send it to myself,
but you can pretend it's your friend for now.
All right.
Save, and run as usual.
Notice that you get another OAuth prompt here.
This time, it's for you to give this script permission
to send email on your behalf.
Oh.
By the way, be sure the email that your app sends
does not violate Gmail's terms of service and program
policies.
But that's a no-brainer, right?
Then go ahead and accept.
And when it's done, let's check our Gmail.
There's the message.
Boom!
If your friend clicks on the link in the message
that he or she receives, they'll see the same auto-generated map
that you saw earlier, and can now meet you at the party
to celebrate your awesome coding skills!
Wow.
So what were we left with?
A four-line script that now interacts
with three different Google products--
Google Sheets, Google Maps and Gmail.
Four lines, that's it.
I'd say this enhancement is even more
useful and intriguing than our first version.
We invite you to use your imagination
to see what you can accomplish with the power of Apps Script.
Hope you got your fill this time,
because you need to go back to work, and I need to go, too.
Happy coding, and I'll see you soon on another episode.