3. Overview
Now that you know what this codelab is about, what are you going to do exactly?
- Learn a bit about Apps Script, which is based on JavaScript.
- Create a Google Sheets spreadsheet.
- Enter a street address in the spreadsheet's top-left cell (A1).
- Learn how to open the Apps Script editor for any document.
- Edit Apps Script code, save, and run it.
- Use Gmail to view the result.
Let's get started.
What is Google Apps Script?
Google Apps Script
is a development platform that makes it fast and easy to create scripts and small applications that integrate with Google Workspace. With Apps Script, you:
- Write code in JavaScript and access built-in libraries for Google Workspace applications like Gmail, Calendar, Drive, etc.
- Have nothing to install?we give you a code editor right in your browser, and your scripts run on Google's servers.
- Don't have to worry about complex topics such as security and data access permissions, since the platform handles it for you.
Apps Script can be used to create various applications, from chat bots to web apps. One of the most popular uses is to extend the functionality of a Google Sheets spreadsheet. In the rest of this codelab, you'll learn how to use Apps Script to take data from a spreadsheet and connect it to other Google services.
5. Edit Apps Script code
Now that you have a new Google Sheet, it's time to edit its bound script. Follow these instructions:
Open the script editor
On the menu bar, select
Extensions > Apps Script
to open the browser code editor for Apps Script.
In a new browser tab, the code editor shows the default
container-bound
script for the sheet. A default function named
myFunction()
is automatically created and your cursor is placed inside the function so you can get started.
Email a Google Map of the address
The "template" code you're given is empty, so let's replace it with our application.
- In the script editor, replace the default
myFunction()
code block with the following code:
/** @OnlyCurrentDoc */
function sendMap() {
var sheet = SpreadsheetApp.getActiveSheet();
var address = sheet.getRange('A1').getValue();
var map = Maps.newStaticMap().addMarker(address);
GmailApp.sendEmail('YOUR_EMAIL_ADDR', 'Map', 'See below.', {attachments:[map]});
}
- Replace the placeholder email address (
'YOUR_EMAIL_ADDR'
) with a valid one. For example,
friend@example.com
.
- To save your script, click Save
.
- To rename your Apps Script project, click
Untitled project
, enter a title for your project, and click
Rename
.
Code review
Let's review the 4 lines of the
sendMap()
function that makes up the entire application. Interestingly, the code references 3 different Google products.
The first line is an annotation comment that affects authorization:
/** @OnlyCurrentDoc */
Most scripts ask the user for some permissions before they can run. These permissions control what the user is allowing the script to do. The first line is a comment containing an
optional annotation
instructing Apps Script to limit access to the current spreadsheet (as opposed to
all
the user's spreadsheets). It's always best practice to include this annotation when you're only working with a single file.
The code uses a normal JavaScript function declaration for
sendMap()
:
function sendMap() {
The next line calls the
Spreadsheet Service
accessible from Apps Script through the
SpreadsheetApp
object
. The returned sheet is assigned to a variable of the same name. The
getActiveSheet()
method gets a reference to the current sheet object and stores it in the variable
sheet
.
var sheet = SpreadsheetApp.getActiveSheet();
With the
sheet
object, we reference the cell range (of a single cell) in
A1 notation
with
getRange()
. A "range" is a group of cells, including a single one like cell
A1
(the one we entered the address in). To fetch what's
inside
the range, the
getValue()
method returns the value of the top-left cell in the range and assigns the value to the variable
address
. You can also try adding more addresses and reading from different cells.
var address = sheet.getRange('A1').getValue();
The third line of code connects to the Google
Maps Service
using the
Maps
object
.
newStaticMap()
creates a static map object and the
addMarker()
method adds a "pin" to the map using the address in the sheet.
var map = Maps.newStaticMap().addMarker(address);
Lastly, the
Gmail Service
through the
MailApp
object
calls the
sendEmail()
method to send the email that includes both the text "See below." and the map image as an attachment.
GmailApp.sendEmail('friend@example.com', 'Map', 'See below.', {attachments:[map]});
}
6. Run the Google Sheets, Maps, and Gmail app
Once named and saved, it's time to run the function. At the top of the editor, ensure
sendMap()
is selected from the functions list and click
Run
.
An Apps Script feature developers appreciate is you don't have to write authorization code that grants the program access to the user's data. Although Apps Script manages this, users of your app still need to grant permission for the script to access their spreadsheets and to send email through Gmail. The first authorization dialog looks like this:
Click
Review permissions
to continue.
Next you'll get the OAuth2 dialog window asking for permission to access your sheet and to send email on your behalf:
After you grant permission, the script runs to completion.
Check the email account where you sent your message, and you should find an email with "Map" as the subject and a message that looks like this:
When you open the attachment in the email message, you should get a Google Map with a pin on the address you entered into the sheet:
With just four lines of code, you accessed 3 different Google products in a meaningful way.