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

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

  • How to use Google Apps Script with various Google services, such as Google Sheets, Google Maps, and Gmail
  • Develop code in a browser-based code editor

What you'll need

  • A web browser with access to the internet
  • A Google account (Google Workspace accounts may require administrator approval)
  • Familiarity with spreadsheets

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

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:

  • Write code in JavaScript and have access to built-in libraries for favorite G Suite applications like Gmail, Calendar, Drive, and more.
  • 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.

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 spreadsheet413a93e5b2f6b3d4.png
  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: 7077e446455639df.png

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('YOUR_EMAIL_ADDR', '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 ('YOUR_EMAIL_ADDR') with a real one, i.e., friend@example.com.

Now that you've edited the file, it needs to be saved.

Save the code

Now save and name your project (call it anything you like—for example, "Hello Maps!").

To save, at the top of the editor, click Save save.

To name your Apps Script project, click Untitled project, enter the name and click Rename.

Once named and saved, let's run it! At the top of the editor, ensure sendMap() is selected in the function dropdown list and click Run.

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.

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.


News & updates

Other codelabs



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]});