Hands-on with Google Apps Script: Accessing Google Sheets, Maps, and Gmail in 4 lines of code!

1. Google Apps Script intro

In this codelab, we'll introduce you to one of the easiest ways to write code that accesses Google developer technologies. It's all done by applying JavaScript, a mainstream web development language. Using Google Apps Script, you'll write code to extract a street address from a cell in a Google Sheet, generate a Google Map based on the address, and then send the map as an attachment using Gmail. The best part? It will only be four lines of code.

What you'll learn

  • How to use Google Apps Script with various Google services, such as Google Sheets, Google Maps, and Gmail.
  • Develop code using the in-browser editor for Apps Script.

What you'll need

  • A web browser with access to the internet
  • A Google Account (Google Workspace accounts might require administrator approval)
  • Basic familiarity with Google Sheets
  • Ability to read Sheets A1 Notation

2. Survey

How will you use this codelab/tutorial?

Read it through only Read it and complete the exercises

How would you rate your experience with Google Workspace developer tools & APIs?

Novice Intermediate Proficient

3. Overview

Now that you know what this codelab is about, what are you going to do exactly?

  1. Learn a bit about Apps Script, which is based on JavaScript.
  2. Create a Google Sheets spreadsheet.
  3. Enter a street address in the spreadsheet's top-left cell (A1).
  4. Learn how to open the Apps Script editor for any document.
  5. Edit Apps Script code, save, and run it.
  6. 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.

4. Create a Google Sheet & enter a street address

In a new Google Sheet, enter a valid street address by following these instructions:

  1. Create a Google Sheet using this convenience link (sheets.google.com/create). Alternatively, go to your Google Drive (drive.google.com) and click New > Google Sheets > Blank spreadsheet

413a93e5b2f6b3d4.png

  1. Within the blank spreadsheet, go to the first cell in the upper left-hand corner (A1). It will be in column A and row 1. If you need to re-familiarize yourself, you can check out this description of A1 notation.
  2. Enter a valid street address in the cell. Choose an address in the world with an exact location using a street address, a zip/postal code, or a combination of city and state/province. Here's an example of an address in New York City:

7077e446455639df.png

That's all you have to do in the sheet. Let's open the editor and write some code.

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.

  1. 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]});
}
  1. Replace the placeholder email address ('YOUR_EMAIL_ADDR') with a valid one. For example, [email protected].
  2. To save your script, click Save save.
  3. 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('[email protected]', '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:

ba6e1a798e404e0d.png

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:

9bed7ef1ccbd7569.png

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:

51f61fc51a13d28f.png

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:

739bb45b75e3f7c7.png

With just four lines of code, you accessed 3 different Google products in a meaningful way.

7. Additional resources

The code featured in this codelab is also available at its GitHub repo at github.com/googleworkspace/apps-script-intro-codelab. Below are more resources to help you dig deeper into the material covered in this codelab and to explore other ways of accessing Google developer tools programmatically.

Documentation

Videos

News & updates

Other codelabs

Introductory

Intermediate

Reference applications