arrow_back

Google Apps Script: Access Google Sheets, Maps & Gmail in 4 Lines of Code

Join Sign in

Google Apps Script: Access Google Sheets, Maps & Gmail in 4 Lines of Code

45 minutes 1 Credit

GSP235

Google Cloud Self-Paced Labs

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 lab resources will be made available to you.

This Qwiklabs 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 AppSheet for the duration of the lab.

What you need

To complete this lab, you need:

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

  • Time to complete the lab.

  1. Make sure you are signed into Qwiklabs using an incognito window.

  2. When ready, click start lab button.

    A new panel will appear with the temporary credentials that you must use for this lab.

    If you need to pay for the lab, a pop-up will open for you to select your payment method.

  3. Note your lab credentials. You will use them to sign in to AppSheet for this lab.

    If you use other credentials, you will get errors or incur charges.
  4. Click Open AppSheet.

  5. Click to sign in with Google.

    Sign in with Google

  6. In the Sign in with Google dialog, enter the Qwiklabs provided Username and click Next.

    If you see other accounts listed, click Use another account and then enter the Qwiklabs provided Username and click Next.

    If you use other credentials, you'll get errors or incur charges.

  7. Enter the Qwiklabs provided Password and click Next.

  8. Click Accept to accept the terms.

  9. On the AppSheet consent page, click Allow. This allows AppSheet to access the Google Drive folders associated with your Qwiklab account.

    Sign in with Google - provide consent

  10. You are now signed in to AppSheet.

    My apps - create a new app

    Click on the X in top right corner of the Create a new app dialog to view the AppSheet MyApps page.

    The MyApps page be empty since you do not have any apps yet.

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:

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

Create a new Google Sheet and enter a street address

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

  1. Click the link to create a new Google Sheet.
  2. On the blank spreadsheet, click into the first cell in the upper left-hand corner (A1). It will be in column A and row 1. 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. Here is an example of entering an address in New York City: 5f8bd97d9aec9778.png

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

Edit Apps Script code

Now edit the Sheets bound script.

  1. To open the script editor, select Extensions from the top menu bar, then click on Apps Script.

db26cb0d3a66452a.png

  1. What you see now in your browser is the code editor for the bound script:

code-editor-bound-script.png

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.

Edit the (template) code

  1. The "template" code you're given is empty and doesn't do much. Copy the code below to replace the template code in the editor window. Then update <YOUR_EMAIL> with an email address you can access:

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]}); }
  1. 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 */

Other than this optional annotation, the best part is that the 4 lines of sendMap() make up the entire app.

Of course, you need to replace the fake email address (*friend@example.com*) with one of yours that you can access 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?

unsaved-code-gs.png

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

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

save-icon.png

save-file.png

Alternatively, you can CTRL+S (PCs, Linux) or Command+S (Mac). If you haven't named your project yet, you must do so before you can proceed.

Run the Google Sheets, Maps, and Gmail app

  1. Now run the app. Since the function was renamed to sendMap(), Select the function to run as sendMap:

run-sendMap.png

Click on the "run" triangle icon and ensure the function to run is sendMap().

174f9137757af868.png

  1. One of the Apps Script features that developers appreciate is that you don't have to write the authorization code. Although Apps Script manages this, users (of your app) still need to grant permission (for this script) to access your Sheet and be able to send email through Gmail on your behalf. The first auth dialog looks like this:

auth-dialog.png

  1. Click Review Permissions.
  2. If prompted, choose your account (your Username found in the Connection Details section of the lab).

8aa63b55cc9e5ee0.png

  1. Now you get the real OAuth2 dialog window asking for permission to access your Sheet as well as send email on your behalf:

134f0d05f1d739a2.png

  1. After you grant permission, the script runs to completion.

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

hello-map-execution.png

  1. Now check the email account where you sent your message. You should find a message with Subject "Map" and a message body that looks like this:

fc76285462b62185.png

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

Application - 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!

  1. This is a normal JavaScript function declaration for sendMap().

function sendMap() {
  1. 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();
  1. 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 ours... cell A1, the one we entered the address in. Now let's fetch what's inside that range of cells with the getValue() call, and assigned to the address variable upon return. Try adding more addresses and reading from different cells.

var address = sheet.getRange("A1").getValue();
  1. The 3rd line connects to the Google Maps Service via the Maps object. As soon as we have access to the Maps Service, we request a new static map be created via newStaticMap(). You can then put a "pin" dropped on the address we pulled from the Sheet by using the addMarker() method.

var map = Maps.newStaticMap().addMarker(address);
  1. 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 Sheet, generate a Google Map based on that address, and send the map to an email recipient.

Finish Your Quest

GSuite-Integrations-125.png

This self-paced lab is part of the Workspace Integrations Quest. A Quest is a series of related labs that form a learning path. Completing this Quest earns you the badge above, to recognize your achievement. You can make your badge public and link to them in your online resume or social media account. Enroll in this Quest and get immediate completion credit if you've taken this lab. See other available Qwiklabs Quests.

Take your next lab

Continue your Quest, or check out these suggestions:

Next steps / learn more

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

Related and general videos

News & updates

Google Cloud Training & 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 January 18, 2022
Lab Last Tested January 18, 2022

Copyright 2022 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.