Apps Script tutorial: Scripting email workflows (budget approvals)

Course 3 of 5 Apps Script + Sheets tutorials

LEVEL: Intermediate to build, but anyone can use this sample.

Image for post
Image for post

Introduction

Gathering inputs from multiple sources and then taking action on each becomes taxing when the task needs to be realized with frequency and with multiple stakeholders. An example is quarterly or annual budget approvals for a finance team.

In this article I will share how to use an Apps Script in a Google Sheet to automatically create a budget submission form that you can share with end users, and when their responses arrive in the sheet, you can collaborate with other reviewers to send emails in bulk depending on whether you are approving, rejecting, or asking for more information about their request. The emails use a Google doc as a template that pulls information from the sheet such as a user’s name, the budget values they entered, or special comments.

Table of contents

A. Get your sheet

B. Create form

  • Run script to create a company-wide budget intake form
  • Visit live form

C. Create reviewer fields

D. Send email

  • Email templates
  • Test sending emails
  • Enter reviewer comments
  • Trigger emails to be sent

E.How the code works

A. Get your sheet

B. Auto-create form

  • Your page may take a second to load with a new custom menu at the top, called Send email > Create form.
Image for post
Image for post
  • You are then prompted for permissions to run your script, select Review permissions.
Image for post
Image for post
  • Select the Google account you are signed into the sheet.

Note: you may encounter a conflict if you are logged into multiple accounts and/or select a different account than the one you used to create your spreadsheet. If this happens, log out of your other accounts.

Image for post
Image for post
  • Select Allow.
Image for post
Image for post

Note: if you encounter a warning page that says “this app is not verified” proceed to click Advanced > Go to <name of sheet> > Allow so that your script sends as you.

  • Visit your spreadsheet, in a few seconds you should now see a new tab called Form Responses 1 and it has 9 columns headers, these are the fields for each question.The responses will arrive in this sheet.

Note: if you want to have multiple forms to keep responses separated, you run the createForm script more than once. This will create a new form each time, and link it to a different tab in your sheet with its own unique instance of the form. You can always visit each form by selecting the Form menu at the top of the sheet where the responses arrive in its tab

Visit live form

  • From the spreadsheet tab Form Responses 1, visit the menu option Form > Got to live form. It should look like this one.

IMPORTANT: if you do not see the menu option called “Form” shortly after, you may have to click Send email > Create form a second time, and that should do it.

Image for post
Image for post
  • Submit 4 responses to create test responses into your sheet. Ensure to use your own email address so you can receive the test emails.
Image for post
Image for post

Optional: at a later time, if you wish to add data validation to your sheet, you can select the pencil icon in the right bottom corner of your form

Image for post
Image for post
  • And modify the question such as making entries be numbers only for example.
Image for post
Image for post

Note: the URL address of the form is the link you can share with other teams to fill out,

Image for post
Image for post

C. Create reviewer fields

  • Insert 3 new columns to the left of Timestamp.
  • Name column A Action (singular), column B Comment (singular), and column C Status.
Image for post
Image for post
  • In column A create a drop-down options by selecting the top of the column (entire column is highlighted) and choosing the Data menu at the top of the sheet > Data validation.
Image for post
Image for post
  • In Cell range modify A1 to A2 so it skips the header, and in Criteria change the option to List of items, and in the second field that’s empty paste the following values with commas in between: Review in progress, Approved, Research needed, Not approved
  • Click Save
Image for post
Image for post
  • The Action column will now have the following options:
Image for post
Image for post
  • These actions will be used by the script in the sheet’s editor in order to send custom emails based on the action you select.

D. Send email

Email templates

  • There is a tab named Templates in your sheet which has 3 rows listed with a different Google docs for different actions.

For example if you select the option Approved in the Action column, and then run the script, it will send an email to that user using the Google doc you linked as its template.

Image for post
Image for post
  • The template contains column names in uppercase surrounded by double {{CURLY BRACKETS}} in order to insert data from your sheet to customize the email to each recipient.

Note: if you encounter any issues when testing emails, ensure you set the correct sharing permissions for the Google Doc (try shareable to anyone in your organization).

Image for post
Image for post

Test sending emails

Enter reviewer comments

  • Return to your Form Responses 1 sheet
  • In cell A2 select Approved. In cell B2 enter a comment like:

“We increased your request for more budget”

  • In cell A3 select Research needed. In B3 enter paste comment:

“Can you please send an email to budget@mycompany.com sharing why you are requesting a high office budget this year.”

  • In cell A4 select Not approved. In B4 paste comment:

“Your offsite budget exceeds policy amounts. Your budget can be reassessed by submitting a different amount.”

  • In cell A5 select Review in progress. In B5 paste comment:

“Started Feb 12 by danny@”

  • This way we will test when different action types are selected.
Image for post
Image for post

Trigger emails to be sent

  • In your sheet Form Responses 1 locate the custom menu option Send emails > Approved.
Image for post
Image for post
  • After a few seconds, cell C2 is populated with today’s date and the name of the template used for the email aka the Approved template in this case.
Image for post
Image for post
  • After the Status field is populated. Visit the inbox of the email address you entered, you should receive an email like this (it comes from the approved template listed in your sheets Template tab):
Image for post
Image for post
  • Next, locate the custom menu option Send emails > Research needed.

Note: you can delete the status that was already populated from a prior email sent, if you wish to send a different email to the same user.

  • After cell C3’s status field is populated, locate the custom menu option Send emails > Not approved. Cell C4 is populated.

Caution: you must wait for each request to finish as it will cancel a previously triggered email action if it has not completed and entered a timestamp.

  • Cell C5 remains empty because the status was not tied to an email action. By using the Review in Progress drop-down option, it helps differentiate between a new request and one that you have been viewing only.
Image for post
Image for post

E.How the code works

  • Visit Tools > Script editor to review the comments for each piece of the code’s functionality. A few things to call out:
Image for post
Image for post
  • The constants at the top are used to create the Google form, except for STATUS, ACTION, EMAIL, and TEMPLATES which are important for the script’s logic.
Image for post
Image for post
  • The function createForm lets a user to automatically create a form with the question types and question titles listed below, and links it to the spreadsheet along with its responses.
Image for post
Image for post
  • The next part of the script creates a Custom menu to display several sub-options in the sheet for users. This menu is added to the interface thanks to an onOpen simple trigger available in Apps Script. Each option has a name and executes a function.
Image for post
Image for post
  • The script uses several Arrow functions which is a feature in modern JavaScript (ES6) that allows you to write transformations that map(), filter(), and reduce() data in a less verbose manners than were done pre-ES6.
  • The function processRows does all of the heavy lifting in reading the sheet and mapping the Action rows to email templates. Templates are saved as key: value pairs ( name:template url).
Image for post
Image for post
  • In order to allocate which are the headers from the entire dataset, we use ` shift()`, which pulls the first row.
Image for post
Image for post
  • In order to specifically process values in the Status column for each row, we use `offset()`
Image for post
Image for post

As the variable statusRange by specifying to skip the first header row, find the index position of the column named STATUS, include all the rows until the end of the list, and only include values for that column.

Image for post
Image for post
  • The following filters the rows by their action and status values in order to queue them up appropriately for emailing.
Image for post
Image for post
  • The `.forEach()` method processes each row that was flagged for emailing and sends the email using the Apps Script MailApp library, and then updates the status array.
Image for post
Image for post
  • At this point these values are not saved into the sheet just yet, you have to have logic to write it back using `.setValues()`
Image for post
Image for post
  • Also note it’s handy to log whenever a row’s status is changed using Logger.log, creating a history record.
Image for post
Image for post
  • Finally at the bottom, in order to access the Google Doc templates for emailing, by using the Apps Script method ScriptApp.getAuthToken, you can use the same scopes you authenticated when you initially ran the script, saving you time in having to re-authenticate.
Image for post
Image for post

Note: the URL is a REST API to pull the doc content as an HTML string through its Google Doc ID.

Enter reviewer comments

Trigger emails to be sent

E.How the code works

Written by

Developer Advocate @Google. Vegan. Accessibility to cloud tech and permaculture knowledge 4 all. Decolonize. These are my opinions my friends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store