In this codelab, we'll introduce you 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 will write code to extract an address sitting in a cell in a Google Sheet, generate a Google Map based on that address, and send the map as an attachment to yourself or a friend using Gmail. The best part? It'll really only be 4 lines of code!

What you'll learn

What you'll need

How will you use this codelab/tutorial?

Read it through only Read it and complete the exercises

How would rate your experience with G Suite developer tools & APIs?

Novice Intermediate Proficient

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

  1. Learn a bit about Apps Script... enough to get you going
  2. Create a new Google Sheets spreadsheet
  3. Enter a street address in the spreadsheet's top-left cell (A1)
  4. Learn how to enter the script editor for any document
  5. Edit Apps Script code, save, and run it
  6. Use Gmail to see the fruits of your labor!

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 G Suite. With Apps Script, you:

NOTE: It is outside of the scope of this codelab to teach you how to write JavaScript code. Our goal is to get you comfortable using the Apps Script development environment and give you an idea of the types of applications you can build with it.

Apps Script can be used to create a variety of different applications, from chat bots to web apps. However one of the original and most popular uses it to add additional functionality to a Google Sheets spreadsheet. In the rest of this codelab you'll learn how to use Apps Script to take data in a spreadsheet and connect it to other Google services. Let's get started!

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

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


  2. On the blank spreadsheet, go to the first cell in the upper left-hand corner (A1). It will be in column A and row 1. Now 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:

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

Now that you have a new Google Sheet, it's time to edit its bound script. Follow these instructions:

Open the script editor

Select Tools from the menu bar then click on Script editor.


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

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

Editing/replacing the (template) code

The "template" code you're given is empty and doesn't do much, so let's replace that with our application. Copy the code you see below, replacing everything in the editor window with the lines below. Believe it or not, this is the entire application!

/** @OnlyCurrentDoc */
function sendMap() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var address = sheet.getRange('A1').getValue();
  var map = Maps.newStaticMap().addMarker(address);
  GmailApp.sendEmail('friend@example.com', 'Map', 'See below.', {attachments:[map]});
}

The first line is a comment that contains an optional annotation that instructs Apps Script to limit its access to only this one spreadsheet we're working with (as opposed to all of a user's spreadsheets). You only need to add it once per script, and it helps give your users peace of mind that the script won't access other data.

Other than this, the best part is that the 4 lines of sendMap() make up the entire app! Of course, we need to replace the fake email address (friend@example.com) with one of yours. Did you notice when replaced the code in the editor, a red asterisk showed up to the left of the file name?

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.

Save the code

Now save and name your project (call it anything you like—for example, "Hello Maps!"). Save the changes by going to the menu bar and selecting File > Save.



Alternatively, you can click the small disk icon or CTRL-s (PCs, Linux) or Command-s (Mac). If you haven't named your project yet, you will be prompted to do so before you can proceed.

Once named and saved, let's run it! There are several options here too. Since we renamed the function to sendMap(), select Run > Run function > sendMap:



Alternatively, click on the "run" triangle icon and ensure the function to run is sendMap().

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


Click Review Permissions to continue.


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

After you grant permission, the script will run to completion. You won't see any visual cues other than knowing your script has completed when the tiny yellow execution dialog disappears.

Now check the email account where you sent your message, and you should find a message with Subject "Map" and a message body 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 (try other addresses!):

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

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

Documentation

Related and general videos

News & updates

Other codelabs

Introductory

Intermediate

Reference apps

This optional section is be used as self-study after the session concludes to fill-in any gaps which may have arose or for further research.

Since this application is so short, there's no overall code structure we can discuss. Instead, we can just walk through line-by-line and review this app which touches three different Google products in just four lines of code!

  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 of code 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 Gmail Service (via the MailApp 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]});
}