GSP235

Overview

In this lab, you are introduced to one of the easiest ways to write code that accesses Google developer technologies, all by leveraging one of the mainstream web development languages, JavaScript. Using Google Apps Script, you write code to extract an address sitting in a cell in a Google Sheet, generate a Google Map based on that address, and send a link to the map to yourself or a friend using Gmail. The best part? It really takes only 4 lines of code!

Objectives

• Learn a bit about Apps Script... enough to get you going
• Create a new Google Sheets spreadsheet
• Learn how to enter the script editor for any document
• Edit Apps Script code, save, and run it
• Use Gmail to see the fruits of your labor!

Suggested experience

The following experience would enhance your learning experience:

• Basic JavaScript skills (helpful but not required)
• Basic spreadsheet skills

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab---remember, once you start, you cannot pause a lab.

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

Start your lab

When you are ready, click Start Lab in the upper left panel.

Find your lab's User Email and Password

To access the resources and console for this lab, locate the User Email and Password in the left panel. These are the credentials you use to log in to Google Drive.

If your lab requires other resource identifiers or connection-related information, these will also appear on this panel.

Sign in to Google Sheets

1. Click Open Google Sheets

Tip: Open the tabs in separate windows, side-by-side.

2. Notice the User Email field has been pre-filled. Click Next.

3. Enter the Password and click Next.

4. Accept all terms and conditions as prompted.

Google Sheets opens and you are signed in to your Student Google Account.

What is Google Apps Script?

Google Apps Script has a development environment that may be different from what you're used to. With Apps Script, you:

• Develop in a browser-based code editor but can choose to develop locally if using clasp, the command-line deployment tool for Apps Script
• Code in a specialized version of JavaScript customized to access Workspace, and other Google or external services (URLfetch, JDBC, etc.)
• Safely ignore writing authorization code because Apps Script handles it for you
• Do not host your app—it lives and runs on Google servers in the cloud.

Note: Teaching you Apps Script is outside of the scope of this lab. There are plenty of online resources. The official documentation features an overview with quickstarts, tutorials, as well as videos. This lab introduces you to the Apps Script development environment so you're comfortable creating code and get you thinking about the types of applications you can build with it.

Apps Script applications come in one of two forms:

1. Bound—meaning it's forever, and only tied to one Google document (Doc, Sheet, Slide, Site, or Form)
2. Standalone—an independent script not bound to Google Sheets, Docs, Slides or Forms file, or Google Sites.

Bound and Standalone apps can also be published to expose more broadly:

• Not published—remains private, accessible only to project owners
• Published as an add-on—your app can be installed from the add-on store
• Published as web app—your app handles HTTP requests and has web UI components
• Embedded in Google Sites—published web apps can be embedded in either the new Sites or classic Sites pages
• Published as an API executable—your app can be accessed through the Execution API
• Some valid combination of the above

Your first Apps Script app should be bound to a Google Sheet. Time to create a new spreadsheet!

Task 1. Create a new Google Sheet and enter a street address

Enter a street address in a new Google Sheet by following these instructions:

1. To create a new sheet, open Google Sheets.
2. On the blank spreadsheet, click into the first cell in the upper left-hand corner (A1). It should be in column A and row 1.
3. Enter an address in that cell, - pick any worldwide valid street address with a targeted location such as postal code or city and state/province, for example 76 9th Ave, New York.

That's all you have to do in the Sheet. Now you're ready to enter the editor and write some code!

Click Check my progress to verify that you've performed the above task.

Create a new Google Sheet and enter a street address

Task 2. Open Apps Script

Apps Script provides a code editor you use to create the Sheets bound script.

• From the top menu bar, select Extensions > Apps Script.

Apps Script opens. Notice the code editor window on the right:

A default function named myFunction() is automatically created for you, and in the editor. That's it... you're now ready to write your application.

Task 3. Edit the (template) code

1. The file Code.gs provides "template" code and doesn't do much. Copy the code below and paste it in the editor window to replace the template code. Then update <YOUR_EMAIL> with lab provided user email:

function sendMap() { var sheet = SpreadsheetApp.getActiveSheet(); var address = sheet.getRange("A1").getValue(); var map = Maps.newStaticMap().addMarker(address); GmailApp.sendEmail("<YOUR_EMAIL>", "Map", 'See below.', {attachments:[map]}); }

2. To restrict this app to access only the Sheet you're working with (as opposed to all of a user's Sheets), add this annotation as a file-level comment for the peace of mind of your users:

/** * @OnlyCurrentDoc */

You did it. Not counting the optional annotation, you just created sendMap(), a 4 line app.

Remember to replace <YOUR_EMAIL> with the user email provided by this lab so you can check for email messages during this lab.

Did you notice when you replaced the code in the editor, a red circle showed up to the left of the file name?

That means you've edited the file which now needs to be saved. You'll see it every time you have an unsaved edit.

3. Save and name your project, call it anything you like—for example, "Hello Maps!". Save the file by clicking the Save project icon.

Alternatively, you can CTRL+S (PCs, Linux) or Command+S (Mac). You must name your project before you can proceed.

Task 4. Run the Google Sheets, Maps, and Gmail app

To run the app you created:

1. Since the function was renamed to sendMap(), select the function to run as sendMap:

2. Click Run in the Code Editor menu bar to run the sendMap() function.

Apps Script manages the authorization code, so you don't have to write it. App users, however, still need to grant permission to the script to access your Sheet and be able to send email through Gmail on your behalf. The first auth dialog looks like this:

3. Click Review Permissions.
4. If prompted, choose your account (your Username found in the Lab Details panel of the lab).

5. In the next dialog asking if your app can access your Google Account, click Allow.

After you grant permission, the script runs to completion.

6. Hover over to the left side and click on Executions to see sendMap listed. Click View Dashboard if prompted.

7. Click the Open Gmail button shown on the left side lab panel.

8. In the Choose and account dialog, enter your student username and click Next.

9. Enter the lab provided Password and click Next.

10. Click Accept to accept the terms.

You should now be in Gmail, looking at your Inbox. You should find a message with Subject "Map" and a message body that looks like this:

Click Check my progress to verify that you've performed the above task.

Run the Google Sheets, Maps, and Gmail app

Just think about it... four lines of code that access three different Google products in a meaningful way, even though it's not a complete application by itself. Even if you're unfamiliar with JavaScript or Apps Script, the code should be readable enough to give you a rough idea how it works, and perhaps what Apps Script can accomplish for you.

Task 5. The App - a detailed explanation

This section reviews the code in more detail.

Since this application is short, there's no overall code structure to discuss. Instead,this section reviews each line of this app, which touches three different Google products!

This is a normal JavaScript function declaration for sendMap():

function sendMap() {

The first line of code calls the Spreadsheet Service accessible from Apps Script via the SpreadsheetApp object. The returned sheet is assigned to a variable of the same name.

The getActiveSheet() method does exactly what it says it does—it returns a "handle" to the current sheet that is active in the user interface (UI):

var sheet = SpreadsheetApp.getActiveSheet();

With the sheet object, reference the cell range (of a single cell) in A1 notation with getRange(). A "range" is a group of cells, including just a single one like yours... cell A1, the one you entered the address in.

Now fetch what's inside that range of cells with the getValue() call, and assigned it to the address variable upon return. Try adding more addresses and reading from different cells.

var address = sheet.getRange("A1").getValue();

The 3rd line connects to the Google Maps Service via the Maps object. As soon as you have access to the Maps Service, request that a new static map be created via newStaticMap().

You can then put a "pin" dropped onto the address you pulled from the Sheet by using the addMarker() method:

var map = Maps.newStaticMap().addMarker(address);

The last line uses the Mail Service (via the GmailApp object), calling its sendEmail() method, to send the email which includes both the text "See below." and the map image as an attachment:

GmailApp.sendEmail("friend@example.com", "Map", 'See below.', {attachments:[map]}); }

Congratulations!

You used the Google Apps Script to write code that accesses Google developer technologies to extract an address in Google Sheets, generate a Google Map based on that address, and sends the map to an email recipient.

Finish your quest

This self-paced lab is part of the Workspace Integrations Quest quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll in this quest and get immediate completion credit. Refer to the catalog for all available quests.

Take your next lab

Continue your quest, or check out these suggestions:

• The Apps Script CLI - clasp

Next steps / Learn more

• Read the Google Sheets API developer documentation.
• Post questions and find answers on Stackoverflow under the google-sheets-api tag.

Additional resources

The code featured in this lab is also available at its GitHub repo at GitHub.com/googlecodelabs/apps-script-intro. (This lab aims to stay in-sync with the repo.) Below are additional resources to help you dig deeper into the material covered in this lab as well as explore other ways of accessing Google developer tools programmatically.

Documentation

• Google Apps Script documentation site
• Apps Script Gmail Service
• Apps Script Spreadsheet Service
• Apps Script Maps Service

Related and general videos

• Others in Google Apps Script video library
• Workspace Dev Show video series

News and updates

• Workspace developers blog
• Workspace developers Twitter (@GSuiteDevs)
• Workspace developers monthly newsletter
• Google Workspace Learning Center

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated June 05, 2023

Lab Last Tested June 05, 2023

Copyright 2024 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.