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

By -

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 
OR
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
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!!)
Save, and close the window.  

Close Script Editor.  And, start using your spreadsheet to make those approvals, letting it email when you have done so! 


Popular posts from this blog

Google Script - Create a Drop-Down List from Spreadsheet Column

By -
Image
(Updated on 3/21/2017 - triggers have been moved!) ( I just created a new related post on how to create a multiple choice  question on the form that a bit simplified - takes the "other" answers in a multiple choice question on a form, and updates the multiple choice options with those as options.) I guess it was only a matter of time before I ended up dabbling with Google Scripts.  Today I was asked by a teacher how he could populate a drop-down list on his Google Form from data on a spreadsheet - and have it update as he changed the data.  Not finding an add-on for this (why recreate the wheel? :-)  I explored creating a script.  And...discovered that this task is not too hard! To do this yourself - 1. Create your form.   Really important step.  Make sure to create your drop-down item and give it a title, but you do not need to add any options to it (because later you will populate it from the spreadsheet). 2. Create the spreadsheet where your list will reside. Yo
Read more

Google Calendar - How to Share Your Calendar Via Link

By -
Image
Google Calendar provides a couple ways you can share your calendar.  The first way you will see below provides a link that lets another person subscribe to your calendar.  They will need to be logged into their Google account, and it will display YOUR calendar along with their own. (Personally, I find this method the best for adding my kids' and husband's events so I see our families events all in one calendar.)  The second method displays your calendar in a browser window.  This is probably the preferred Teacher method for sharing with parents. (Here is a video on How to Share Your Calendar Via a Link .) When in your Google Calendar: Click on the Options menu (stacked dots) for the calendar you want to share - Select Settings and sharing - Click on Access permissions on the left side - Click on the Get shareable link - Note that who can see the calendar with this link depends on the access permissions you have set right above.  For example, with the setti
Read more

Google Mail - Create Calendar Event that Includes Email Message

By Anonymous -
Image
There are many time I get an email that I want to make into an event on my calendar - both to make sure I block out the time, and to move the information out of an email in my inbox into the event.  There is a feature called "Create event" that let's you do this - it is available in the More options when you are viewing the message.  Once you select create event, Google opens up a new window, loads your calendar, and creates an event with the message subject as the name of the event, and the body of the message as the description within the event: You are then able to edit the date/time as needed to finish creating the event.  One caveat...it adds everyone that was part of the message to the Guest list (see above in red box), so unless you want it to be a group event you will need to click the x next to everyone's name but yours.
Read more

Google Documents - Creating Page Anchors (aka Bookmarks)

By -
Image
There are times in a Google Document you want to provide links to specific areas within the page and  a table of contents doesn't fit the bill. (See how to create & use a table of contents here .)  When this happens, you will need to employ the use of page anchors. Anchors basically make a linkable spot on the page.  They are pretty easy to create, and can be linked tho both within the page and from outside the page.  Within a Google Document these are called Bookmarks. Here is how to set them up: 1. Put your cursor where you want the anchor/bookmark 2. Go to the Insert Menu, select Bookmark - this will add a little blue flag to the location That's it!  You can create as many as you need in the document. To get rid of an anchor/bookmark that you no longer want: 1. Click in the location you added it.  A pop-up should appear 2. Click Remove To use an anchor/bookmark within the document: 1. Type the text you want linked to the anchor/bookmark 2. Hi
Read more

Google Contacts - Newest Area to Get Some "Google Love"

By Anonymous -
Image
Be ready!  Your Google Contacts is about to update its looks.  I don't know about you, but I don't go to Google Contacts often.  I just don't like it.  If you need a refresher, this is what it looks (looked) like - Looked & felt very...dated.  This morning in going to one of my Google accounts, I got the pop-up window in my contacts.  Here is a screenshot of the new look - Hoping that the Google Love includes more than just the interface.  If there are any big changes that make Contacts more easily managed & navigated, you can be sure to see another blog post or two about it!
Read more