Google Script: Sending an Approval Email from Form/Spreadsheet Data

By Melinda Waffle - December 04, 2020

I was asked to create a script that would send an approval email once a request was approved - for 2 separate groups, and to be used for 2 different reasons! The example below is being used for building access approval (RE: the work-at-home requirements due to COVID-19). The other way this was used was for approval of spending requests.

These utilize an additional column that is marked (in these cases) with a Yes that will trigger the email, then if an email is sent it makes that notation in the final column. Note - the script will break (stop running) if someone submits a non-working email from a form, so I recommend having the form auto-collect the email.

Here are the directions for using the script -

Creating and Sending the Email

1. Create the spreadsheet

You can either do this with a New Spreadsheet, adding the data fields you want

Create a form with the data you are collecting and have it create the companion spreadsheet

Important:

Be sure to include email address!
You can have additional fields of information that are not being used by the script

2. Paste the Code Into the Script Editor

Go to the Tools Menu -> Script Editor

Delete out what you see

Copy the code from my Script to create and send an email based on form data document.

Paste the code into the Script Editor

3. Personalize the Code

I've commented throughout the script so you should be able to modify the code. Some specific areas to look for:

the data by row information - adjust these to your information

in JavaScript, counting starts at 0 (not 1) so data from Column A is mapped as [0], Column B is [1] and so forth
if you add/change variable names, make sure that references to them when crafting the email match exactly
if you add additional items to the list, make sure to end each line with a ;

the email subject

actual text you want in the Subject line needs to be in quotes
any variable you want in the Subject line needs to NOT be in quotes
separate any of these with a plus (+) sign
end with a semi-colon (;)

the email body

actual text you want in the email body needs to be in quotes
line breaks are added with \n - within the quotes
any variable you want in the email body needs to NOT be in quotes
separate any of these with a plus (+) sign
end with a semi-colon (;)

4. Save & Test Your Script

Click on the save icon to save the script.

Test your script by clicking on the play icon - make sure you have at least 1 row of data in your spreadsheet or it will not do anything! It will ask for permission because it will be writing to your spreadsheet and sending from your email. You will need to permit that for the script to work.

If you messed it up, you will get a red error message at the top. Have fun troubleshooting ;-)

5. Set Your Trigger

(I do recommend testing it prior to adding your trigger.)

While still in the Script Editor, go to the Edit Menu -> Current project's triggers

A new window will open, click on the Add Trigger button in the lower right corner

Change the Select event type to "On edit" so that it will run the script when you edit the spreadsheet. (This is if like in my example you are simply adding approval in a column and minimally/not changing any other data. If you are making lots of edits, this is not the best option as the script will be running way too much!!)