In this codelab, you'll use Google Apps Script to write a Gmail add-on that lets users add data to a spreadsheet from directly within Gmail. Anytime a user receives an email receipt, they can open the add-on which automatically contains relevant information about the expense. Users can edit that information and then submit the form to log their expense into a spreadsheet.

What you'll learn

What you'll need

Go to the GitHub repository to access the code. From the command line:

git clone https://github.com/googlecodelabs/gmail-add-ons.git

The repository contains a set of directories representing each step along the process, in case you need to reference a working version.

You'll be starting from scratch with an entirely blank script, but you can refer to, or copy files from, the repository as needed.

Let's start by writing the code for a simple version of our add-on:

  1. Navigate to script.google.com. This is an IDE where you can edit the code for your add-on.
  2. Rename your project Expense It!.
  3. Select View > Show manifest file, which opens a file titled appsscript.json.

You can specify the metadata associated with the add-on in this manifest file. Replace the contents of appsscript.json with the following:

{
  "timeZone": "GMT",
  "oauthScopes": [
    "https://www.googleapis.com/auth/gmail.addons.execute"
  ],
  "gmail": {
    "version": "TRUSTED_TESTER_V2",
    "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 serves to call the function getContextualAddOn when the add-on is first activated. As such, we'll need to write that function, which for an argument takes an event containing the ID of the email that is currently open.

To start, rename the file Code.gs as GetContextualAddOn.gs and then copy this simple implementation of the function, which we explain below:

/**
 * 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 Gmail add-on's user interface consists of cards split into one or more sections, each of which contains widgets that can display and get information from the user. In the above function, we create a single card meant for getting the details about an expense found in an email. The card has one section containing text input fields for relevant data. We return an array of the add-on's cards, which in this case includes just one.

Do the following to deploy and run your add-on:

  1. Select Publish > Deploy from manifest...
  2. Next to the entry Latest Version (Head) Click Get ID .
  3. Select and copy the Deployment ID value.
  4. Close the dialog and the Deployments window.
  5. Open the Gmail add-on settings tab.
  6. In the Add-ons tab, ensure that you have selected the Enable developer
    add-ons for my account
    checkbox.
  7. Paste your add-on's deployment ID into the Install developer add-on textbox
    and click Install.
  8. In the Install developer add-on dialog that appears, click the checkbox to
    indicate that you trust this developer (yourself), then click Install.
  9. In the Install developer add-on dialog that appears, click the checkbox to indicate that you trust this developer (yourself), then click Install.
  10. Open any message in Gmail, either on web or mobile.
  11. A button (vaguely resembling a receipt ) for the add-on should appear on the right or bottom of your screen depending on your platform.
  12. Click the button to authorize the add-on and follow the prompts.

You should now see a simple form created by the add-on. It doesn't do anything yet, but you'll build out the functionality in the next section.

As you continue in this lab, you only need to save your code and refresh Gmail to see updates to your add-on.

Next we'll add some code to fetch the email content as well as modularize the code for a bit more organization. Click File > New > Script file to create new scripts named Cards.gs and Helpers.gs. We'll abstract out the creation of the card to Cards.gs and use functions from Helpers.gs to populate fields in the form based on the contents of the email.

Replace the code in GetContextualAddOn.gs with the following code snippet to take advantage of the abstractions:

/**
 * 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. This uses the event supplied by Gmail to read the user's currently selected message. This also requires adding an additional scope to the script manifest to allow users to authorize the script.

Update the oauthScopes section in appscript.json to request the scope https://www.googleapis.com/auth/gmail.addons.current.message.readonly

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

Next, we can implement createExpensesCard in Cards.gs. We let the function take in an array of values to prefill the form as an optional argument. We also add the ability to display an optional status message, which we color red if the status begins with "Error:" and green otherwise. Instead of adding each field to the form manually, we write a helper function createFormSection to loop through the process of creating the widgets, setting the prefilled value with setValue and adding the widgets to the section on the card.

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

/**
 * Creates the main card users see with form inputs to log expense.
 * 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;
}

Finally, you can implement the four functions in Helpers.gs that get called by getContextualAddOn to determine the prefilled values on the form. For now, functions only need to return the string "TODO."

Run your add-on. It should behave just as before, with the addition of prefilled TODOs in the form.

So far, our add-on has a form for the user to enter details about an expense, but those form values have nowhere to go. With Google Apps Script you can easily interface with multiple Google APIs. Our goal is to link the user's inputs to a Google Sheet, so it makes sense to include a button for the user to click when they are ready to log an expense. Luckily, there is the button set widget, to which individual buttons can be added. Furthermore, Google Apps Script has a Spreadsheet service that allows scripts to communicate with Google Sheets.

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];
  });
}
  1. Create a button set widget using CardService.newButtonSet() and add your text button to the button set with CardService.ButtonSet.addButton().
  2. 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 appscript.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/.

Re-run the add-on and try submitting the form.

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.

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.

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.

To really save users' time, we should 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, 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!

In the event that our program misidentifies an expense in the given email and prefills the form with incorrect information, it would be convenient for the user to be able to clear the form. The CardAction class is useful here, since we can specify that a function gets called when the action is clicked.

Modify createExpensesCard such that the card it returns has an 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();
}

Beyond using Google Apps Script to edit an existing spreadsheet, you can create an entirely new spreadsheet programmatically. For our add-on, we should allow the user to create a new 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 should generate 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 you should 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.

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

You can let your imagination guide you into improving on the script, but here are some additional ideas for making an even stronger product:

Learn More