Make email more actionable with Google Workspace Add-ons

1. Overview

In this codelab, you'll use Google Apps Script to write a Google Workspace Add-on for Gmail that lets users add receipt data from an email to a spreadsheet directly within Gmail. When a user receives a receipt by email, they open the add-on which automatically gets relevant expense information from the email. The user can edit expense information and then submit it to log their expense into a Google Sheets spreadsheet.

What you'll learn

• Create a Google Workspace Add-on for Gmail using Google Apps Script
• Parse an email with Google Apps Script
• Interact with Google Sheets via Google Apps Script
• Store user values using Google Apps Script's Properties service

What you'll need

• Access to the internet and a web browser
• A Google account
• Some messages, preferably email receipts, in Gmail

2. Get the sample code

As you work through this codelab, it might be helpful to reference a working version of the code you'll write. The GitHub repository contains sample code that you can use as a reference.

To get the sample code, from the command line, run:

git clone https://github.com/googleworkspace/gmail-add-on-codelab.git

3. Make a basic add-on

Start by writing the code for a simple version of the add-on that displays an expense form alongside an email.

First, create a new Apps Script project and open its manifest file.

1. Navigate to script.google.com. From here, you can create, manage, and monitor your Apps Script projects.
2. To create a new project, at the top left, click New Project. The new project opens with a default file named Code.gs. Leave Code.gs alone for now, you'll work with it later.
3. Click Untitled project, name your project Expense It!, and click Rename.
4. At the left, click Project Settings .
5. Select the Show "appscript.json" manifest file in editor" checkbox.
6. Click Editor .
7. To open the manifest file, at the left, click appscript.json.

In appscript.json, specify the metadata associated with the add-on, such as its name and the permissions it requires. Replace the contents of appsscript.json with these configuration settings:

{
  "timeZone": "GMT",
  "oauthScopes": [
    "https://www.googleapis.com/auth/gmail.addons.execute"
  ],
  "gmail": {
    "name": "Expense It!",
    "logoUrl": "https://www.gstatic.com/images/icons/material/system/1x/receipt_black_24dp.png",
    "contextualTriggers": [{
      "unconditional": {
      },
      "onTriggerFunction": "getContextualAddOn"
    }],
    "primaryColor": "#41f470",
    "secondaryColor": "#94f441"
  }
}

Pay special attention to the portion of the manifest called contextualTriggers. This part of the manifest identifies the user-defined function to call when the add-on is first activated. In this case, it calls getContextualAddOn, which gets details about the open email and returns a set of cards to display to the user.

To create the getContextualAddOn function, follow these steps:

1. At the left, hold the pointer over Code.gs, then click Menu > Rename.
2. Type GetContextualAddOn and press the Enter key. Apps Script automatically appends .gs to your file name, so you don't need to type a file extension. If you type GetContextualAddOn.gs then Apps Script names your file GetContextualAddOn.gs.gs.
3. In GetContextualAddOn.gs, replace the default code with the getContextualAddOn function:

/**
 * Returns the contextual add-on data that should be rendered for
 * the current e-mail thread. This function satisfies the requirements of
 * an 'onTriggerFunction' and is specified in the add-on's manifest.
 *
 * @param {Object} event Event containing the message ID and other context.
 * @returns {Card[]}
 */
function getContextualAddOn(event) {
  var card = CardService.newCardBuilder();
  card.setHeader(CardService.newCardHeader().setTitle('Log Your Expense'));

  var section = CardService.newCardSection();
  section.addWidget(CardService.newTextInput()
    .setFieldName('Date')
    .setTitle('Date'));
  section.addWidget(CardService.newTextInput()
    .setFieldName('Amount')
    .setTitle('Amount'));
  section.addWidget(CardService.newTextInput()
    .setFieldName('Description')
    .setTitle('Description'));
  section.addWidget(CardService.newTextInput()
    .setFieldName('Spreadsheet URL')
    .setTitle('Spreadsheet URL'));

  card.addSection(section);

  return [card.build()];
}

Every Google Workspace Add-on's add-on's user interface consists of cards split into one or more sections, each containing widgets that can display and get information from the user. The getContextualAddOn function creates a single card that gets details about an expense found in an email. The card has one section containing text input fields for relevant data. The function returns an array of the add-on's cards. In this case, the returned array includes just one card.

Before deploying the Expense It! add-on, you need a Google Cloud Platform (GCP) Project, which Apps Script projects use to manage authorizations, advanced services, and other details. To learn more, visit Google Cloud Platform Projects.

To deploy and run your add-on, follow these steps:

1. Open your GCP project, and copy its project number.
2. From your Apps Script project, at the left, click Project Settings .
3. Under "Google Cloud Platform (GCP) Project", click Change project.
4. Enter your GCP project's project number, then click Set project.
5. Click Deploy > Test deployments.
6. Ensure that the deployment type is Google Workspace Add-on. If necessary, at the top of the dialog, click Enable deployment types and select Google Workspace Add-on as the deployment type.
7. Next to Application(s): Gmail, click Install.
8. Click Done.

Now you can see the add-on in your Gmail inbox.

1. On your computer, open Gmail.
2. On the right side panel, the Expense It! add-on appears. You might need to click More Add-ons to find it.
3. Open an email, preferably a receipt with expenses.
4. To open the add-on, in the right side panel, click Expense It! .
5. Give Expense It! access to your Google account by clicking Authorize Access and follow the prompts.

The add-on shows a simple form alongside an open Gmail message. It doesn't do anything else yet, but you'll build out its functionality in the next section.

To see updates to your add-on as you continue through this lab, you only need to save your code and refresh Gmail. No additional deployments are needed.

4. Access email messages

Add code that fetches email content and modularize the code for a bit more organization.

Next to Files, click Add > Script and create a file named Cards. Create a second script file called Helpers. Cards.gs creates the card and uses functions from Helpers.gs to populate fields in the form based on the content of the email.

Replace the default code in Cards.gs with this code:

var FIELDNAMES = ['Date', 'Amount', 'Description', 'Spreadsheet URL'];

/**
 * Creates the main card users see with form inputs to log expenses.
 * Form can be prefilled with values.
 *
 * @param {String[]} opt_prefills Default values for each input field.
 * @param {String} opt_status Optional status displayed at top of card.
 * @returns {Card}
 */
function createExpensesCard(opt_prefills, opt_status) {
  var card = CardService.newCardBuilder();
  card.setHeader(CardService.newCardHeader().setTitle('Log Your Expense'));
  
  if (opt_status) {
    if (opt_status.indexOf('Error: ') == 0) {
      opt_status = '<font color=\'#FF0000\'>' + opt_status + '</font>';
    } else {
      opt_status = '<font color=\'#228B22\'>' + opt_status + '</font>';
    }
    var statusSection = CardService.newCardSection();
    statusSection.addWidget(CardService.newTextParagraph()
      .setText('<b>' + opt_status + '</b>'));
    card.addSection(statusSection);
  }
  
  var formSection = createFormSection(CardService.newCardSection(),
                                      FIELDNAMES, opt_prefills);
  card.addSection(formSection);
  
  return card;
}

/**
 * Creates form section to be displayed on card.
 *
 * @param {CardSection} section The card section to which form items are added.
 * @param {String[]} inputNames Names of titles for each input field.
 * @param {String[]} opt_prefills Default values for each input field.
 * @returns {CardSection}
 */
function createFormSection(section, inputNames, opt_prefills) {
  for (var i = 0; i < inputNames.length; i++) {
    var widget = CardService.newTextInput()
      .setFieldName(inputNames[i])
      .setTitle(inputNames[i]);
    if (opt_prefills && opt_prefills[i]) {
      widget.setValue(opt_prefills[i]);
    }
    section.addWidget(widget);
  }
  return section;
}

The createExpensesCard function takes an array of values to prefill the form as an optional argument. The function can display an optional status message, which is colored red if the status begins with "Error:", and is otherwise green. Instead of adding each field to the form manually, a helper function called createFormSection loops through the process of creating text input widgets, sets each default value with setValue, and then adds the widgets to their respective sections on the card.

Now, replace the default code in Helpers.gs with this code:

/**
 * Finds largest dollar amount from email body.
 * Returns null if no dollar amount is found.
 *
 * @param {Message} message An email message.
 * @returns {String}
 */
function getLargestAmount(message) {
  return 'TODO';
}

/**
 * Determines date the email was received.
 *
 * @param {Message} message An email message.
 * @returns {String}
 */
function getReceivedDate(message) {
  return 'TODO';
}

/**
 * Determines expense description by joining sender name and message subject.
 *
 * @param {Message} message An email message.
 * @returns {String}
 */
function getExpenseDescription(message) {
  return 'TODO';
}

/**
 * Determines most recent spreadsheet URL.
 * Returns null if no URL was previously submitted.
 *
 * @returns {String}
 */
function getSheetUrl() {
  return 'TODO';
}

The functions in Helpers.gs get called by getContextualAddon to determine the prefilled values on the form. For now, these functions will only return the string "TODO" because you'll implement the prefill logic in a later step.

Next, update the code in GetContextualAddon.gs so that it leverages the code in Cards.gs and Helpers.gs. Replace the code in GetContextualAddon.gs with this code:

/**
 * Copyright 2017 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

/**
 * Returns the contextual add-on data that should be rendered for
 * the current e-mail thread. This function satisfies the requirements of
 * an 'onTriggerFunction' and is specified in the add-on's manifest.
 *
 * @param {Object} event Event containing the message ID and other context.
 * @returns {Card[]}
 */
function getContextualAddOn(event) {
  var message = getCurrentMessage(event);
  var prefills = [getReceivedDate(message),
                  getLargestAmount(message),
                  getExpenseDescription(message),
                  getSheetUrl()];
  var card = createExpensesCard(prefills);

  return [card.build()];
}

/**
 * Retrieves the current message given an action event object.
 * @param {Event} event Action event object
 * @return {Message}
 */
function getCurrentMessage(event) {
  var accessToken = event.messageMetadata.accessToken;
  var messageId = event.messageMetadata.messageId;
  GmailApp.setCurrentMessageAccessToken(accessToken);
  return GmailApp.getMessageById(messageId);
}

Note the new getCurrentMessage function, which uses the event supplied by Gmail to read the user's currently open message. In order for this function to work, add an additional scope to the script manifest that allows read-only access to Gmail messages.

In appscript.json, update the oauthScopes so that it also requests the https://www.googleapis.com/auth/gmail.addons.current.message.readonly scope.

"oauthScopes": [
  "https://www.googleapis.com/auth/gmail.addons.execute",
   "https://www.googleapis.com/auth/gmail.addons.current.message.readonly"
],

In Gmail, run your add-on, and authorize access for Expense It! to view email messages. The form fields are now prefilled with "TODO".

5. Interact with Google Sheets

The Expense It! add-on has a form for the user to enter details about an expense, but those details have nowhere to go. Let's add a button that sends the form data to a Google Sheet.

To add a button, we'll use the ButtonSet class. To interface with Google Sheets, we'll use the Google Sheets service.

Modify createFormSection to return a button labeled "Submit" as part of the form section of the card. Take the following steps:

1. Create a text button using CardService.newTextButton(), labeling the button "Submit" using CardService.TextButton.setText().
2. Design the button such that when it is clicked the following submitForm action gets called via CardService.TextButton.setOnClickAction():

/**
 * Logs form inputs into a spreadsheet given by URL from form.
 * Then displays edit card.
 *
 * @param {Event} e An event object containing form inputs and parameters.
 * @returns {Card}
 */
function submitForm(e) {
  var res = e['formInput'];
  try {
    FIELDNAMES.forEach(function(fieldName) {
      if (! res[fieldName]) {
        throw 'incomplete form';
      }
    });
    var sheet = SpreadsheetApp
      .openByUrl((res['Spreadsheet URL']))
      .getActiveSheet();
    sheet.appendRow(objToArray(res, FIELDNAMES.slice(0, FIELDNAMES.length - 1)));
    return createExpensesCard(null, 'Logged expense successfully!').build();
  }
  catch (err) {
    if (err == 'Exception: Invalid argument: url') {
      err = 'Invalid URL';
      res['Spreadsheet URL'] = null;
    }
    return createExpensesCard(objToArray(res, FIELDNAMES), 'Error: ' + err).build();
  }
}

/**
 * Returns an array corresponding to the given object and desired ordering of keys.
 *
 * @param {Object} obj Object whose values will be returned as an array.
 * @param {String[]} keys An array of key names in the desired order.
 * @returns {Object[]}
 */
function objToArray(obj, keys) {
  return keys.map(function(key) {
    return obj[key];
  });
}

3. Create a button set widget using CardService.newButtonSet() and add your text button to the button set with CardService.ButtonSet.addButton().
4. Add the button set widget to the form section of the card using CardService.CardSection.addWidget().

In just a few lines of code, we are able to open a spreadsheet by its URL and then append a row of data to that sheet. Note that the form inputs get passed into the function as part of the event e, and we check that the user has provided all fields. Assuming no errors occur, we create a blank expenses card with a favorable status. In the case that we catch an error, we return the original filled card along with the error message. The objToArray helper function makes it easier to convert the form responses into an array, which can then be appended to the spreadsheet.

Lastly, update the oauthScopes section in appsscript.json again request the scope https://www.googleapis.com/auth/spreadsheets. When authorized this scope lets the add-on read and modify a user's Google Sheets.

"oauthScopes": [
  "https://www.googleapis.com/auth/gmail.addons.execute",
  "https://www.googleapis.com/auth/gmail.addons.current.message.readonly",
  "https://www.googleapis.com/auth/spreadsheets"
],

If you haven't already created a new spreadsheet, create one at https://docs.google.com/spreadsheets/.

Now re-run the add-on and try submitting the form. Ensure that you enter the full URL of your destination URL into the Spreadsheet URL form field.

6. Store values with the Properties service

Often times, users will log many expenses to the same spreadsheet, so it would be convenient to offer the most recent spreadsheet URL as a default value in the the card. In order to know the most recent spreadsheet's URL, we'll need to store that information every time the add-on is used.

The Properties service lets us store key-value pairs. In our case, a reasonable key would be "SPREADSHEET_URL" while the value would be the URL itself. To store such a value, you'll need to modify submitForm in Cards.gs such that the spreadsheet's URL gets stored as a property upon appending a new row to the sheet.

Note that properties can have one of three scopes: script, user, or document. The document scope does not apply to Gmail add-ons, although it is relevant to a separate type of add-on when storing information specific to a particular Google Doc or Sheet. For our add-on, the desired behavior is for an individual to see their own (as opposed to somebody else's) most recent spreadsheet as the default option on the form. Consequently, we select the user scope instead of the script scope.

Use PropertiesService.getUserProperties().setProperty() to store the spreadsheet URL. Add the following to submitForm in Cards.gs:

PropertiesService.getUserProperties().setProperty('SPREADSHEET_URL', 
    res['Spreadsheet URL']);

Then modify the getSheetUrl function in Helpers.gs to return the stored property such that the user will see the most recent URL every time they use the add-on. Use PropertiesService.getUserProperties().getProperty() to get the property's value.

/**
 * Determines most recent spreadsheet URL.
 * Returns null if no URL was previously submitted.
 *
 * @returns {String}
 */
function getSheetUrl() {
  return PropertiesService.getUserProperties().getProperty('SPREADSHEET_URL');
}

Finally, to access the Property service, the script will also need to be authorized. Add the scope https://www.googleapis.com/auth/script.storage to the manifest as before to allow your add-on to read and write property information.

7. Parse the Gmail message

To really save users' time, let's prefill the form with relevant information about the expense from the email. We already created functions in Helpers.gs that play this role, but so far we have only returned "TODO" for the date, amount, and description of the expense.

For instance, we can get the date the email was received and use that as the default value for the date of the expense.

/**
 * Determines date the email was received.
 *
 * @param {Message} message - The message currently open.
 * @returns {String}
 */
function getReceivedDate(message) {
  return message.getDate().toLocaleDateString();
}

Implement the remaining two functions:

1. getExpenseDescription might entail joining both the sender's name and message subject, although there exist more sophisticated ways to parse the message body and deliver an even more accurate description.
2. For getLargestAmount consider looking for specific symbols associated with money. Receipts often have multiple values listed, such as taxes and other fees. Think of how you might identify the correct amount. Regular expressions might also be handy.

If you need some extra inspiration, explore the reference documentation for GmailMessage or check out the solution code you downloaded at the beginning of the codelab. Once you've devised your own implementations for all the functions in Helpers.gs, take your add-on for a spin! Open up receipts and start logging them in a spreadsheet!

8. Clear the form with card actions

What happens if Expense It! misidentifies an expense in an open email and prefills the form with incorrect information? The user clears the form. The CardAction class lets us specify a function that gets called when the action is clicked. Let's use it to give the user a quick way to clear the form.

Modify createExpensesCard such that the card it returns has a card action labeled "Clear form" and when clicked calls the following clearForm function, which you can paste into Cards.gs. You will need to pass in opt_status as a parameter named "Status" to the action to ensure that when the form is cleared the status message remains. Be mindful that optional parameters for actions must be of the type Object.<string, string>, so if opt_status is not available, you should pass {'Status' : ''}.

/**
 * Recreates the main card without prefilled data.
 *
 * @param {Event} e An event object containing form inputs and parameters.
 * @returns {Card}
 */
function clearForm(e) {
  return createExpensesCard(null, e['parameters']['Status']).build();
}

9. Create a spreadsheet

Beyond using Google Apps Script to edit an existing spreadsheet, you can create an entirely new spreadsheet programmatically. For our add-on, let's allow the user to create a spreadsheet for expenses. To get started, add the following card section to the card that createExpensesCard returns.

var newSheetSection = CardService.newCardSection();
var sheetName = CardService.newTextInput()
  .setFieldName('Sheet Name')
  .setTitle('Sheet Name');
var createExpensesSheet = CardService.newAction()
  .setFunctionName('createExpensesSheet');
var newSheetButton = CardService.newTextButton()
  .setText('New Sheet')
  .setOnClickAction(createExpensesSheet);
newSheetSection.addWidget(sheetName);
newSheetSection.addWidget(CardService.newButtonSet().addButton(newSheetButton));
card.addSection(newSheetSection);

Now, when the user clicks on the "New Sheet" button the add-on generates a new spreadsheet formatted with a header row that is frozen such that it is always visible. The user specifies a title for the new spreadsheet in the form, although including a default value in case the form is blank might be a good choice. In your implementation of createExpensesSheet, return a nearly identical card to the existing card, with the addition of an appropriate status message as well as prefilling the URL field with the URL of the new spreadsheet.

10. Congratulations!

You've successfully designed and implemented a Gmail add-on that finds an expense in an email and helps users log the expense into a spreadsheet in just a matter of seconds. You've used Google Apps Script to interface with multiple Google APIs and persisted data between multiple executions of the add-on.

Possible Improvements

Let your imagination guide you as you enhance the Expense It!, but here are some ideas for making an even more useful product:

• Link to the spreadsheet once the user has logged an expense
• Add the ability to edit/undo the logging of an expense
• Integrate external APIs to let users make payments and request money

Learn More

• Gmail service
• Spreadsheet service
• Properties service
• Sheets API Codelab
• Slides API Codelab