Jul 06 2013

Adding email notification to Google Forms and Google Spreadsheets

This idea and the code to implement it may be useful in other settings, but I developed it in the context of supporting a community with a largely volunteer organization at its center.   At least in my mind the code comes from a point of view.

Here’s a picture of what it does:

google-form-sheet-pgm-v2

Adding email notifications to a Google Form and a Google Spreadsheet.

Here are the functions that are involved (shown in red), starting from the upper right-hand corner:

  1.  If the person completing the form provides their email address, the Google Script below will send them a snapshot of what they entered into the form in a simple but legible format that I call a “row snapshot.”
  2. Obviously Google Forms are very handy in the context of a community or a small organization because they make it easy to collect information from a large number of un-identified people into an orderly and useful format.
  3. Once data is entered into a Google Spreadsheet, many manipulations, edits, sorts, and analyses are relatively easy.   Controlling access to the Google Spreadsheet so that either few or many people can see or edit the Spreadsheet is easy to do.
  4. People who are looking at the Spreadsheet while logged on (as opposed to anonymous users) can use a drop down menu created by this program to request a “row snapshot” report for any given row.  The first time you run the program, you need to authorize it to send email to you on your behalf.
  5. Once an authorized user has received the row snapshot in an email message, it’s useful for sharing, by forwarding it to other people who are involved or need to be alerted to the status of whatever it is that is represented in that row.

In developing this, I was adapting code from a Google Script tutorial titled “Tutorial: Automating a Help Desk Workflow“.  What was crucial but easy to miss was this little snippet:

Now we need to set up a trigger so that this function will be called each time a form response is submitted. In the Script Editor, go to Resources > Current project’s triggers. Click the link that says ‘No triggers set up. Click here to add one now.’

Once that was working, the code has evolved over the past few months to the point where I think it’s worth sharing:

code.gs.txt

No responses yet

Comments are closed.