The reason why I started my programming life was that I wanted to use technology to bring convenience to people, and possibly make things easier on their every busy day. Being part of the American Red Cross Club at UC Davis, I signed up for volunteering events on a shared Google Spreadsheet. Sometimes I would sign up for an event on my phone while I was traveling between classes. Without putting it immediately down on my schedule planner, I might forgot that I have signed up and missed the event. In order to maintain the attendance rate so that people in need can get the maximum assistant from us, I decided to use my knowledge in programming to write a script to automatically generate emails to volunteers 24 hours before the events, with details of the event and contact information of the person-in-charge included in the email body.
I then figured that this functionality might also be useful for other teams or organizations, so I made two versions of the automatic reminder script: one specifically tailored for the American Red Cross Club, and one as add-ons for general use.
------ Google Add-ons script ------
- Open the add-on by clicking Add-ons > Automatic Reminder > Show sidebar
- Type in the Date, Time, Event Name and Location
- Create one or two columns, either only the "Email" column or both the "Name" and the "Email" column
- Check which columns you want to be included under the "Include" section
- If there is a person-in-charge of the event, include the information of the person
- Type in the email of the sender. Make sure you have access to the email account
- Check the box underneath if you want a log report after the emails are sent to the participants
- Include the body structure of the email in the "Message Format" text box. For any information that would vary among participants, e.g. the name of the participant, leave them in brackets.
- Hit the "Set Reminders" button and let the script do the rest for you!
------ Tailored script for American Red Cross Club ------ To use the script as it is, there are several format requirements for the Google Spreadsheet.
In cell A1, it must be month/day Event Name. Note that after the email is sent, the phrase '(Reminder Sent)' would be added after the event name in cell A1.
For the location in cell A3. it must be "Where: Event Location", so that the location will be included in the email.
For the contact information below for every time slot, it must be time AM/PM/am/pm - time AM/PM/am/pm, then Name, Phone, Email, and anything else after the email column.
If there is an officer involved in the timeslot. The row should be either 'Officer' or 'Officer:'. If there are more than 1 officer during that timeslot, they should be separated with a comma, followed by a white space.
For this script, the script will run automatically everytime a person open the spreadsheet, it will then read it all the sheets in the workbook, then set up to generate email reminders for the nearest time. In this particular example, the program will generate email reminders to Amy and Bob at 9/6 10 AM, a day before the sign-up time. Similarly, Catherine, David and Elaine will receieve their email reminders at 9/6 11 AM.
After the email is being sent at 9/6 10 AM, Bob would received an email like this:
Subject: Reminder for Health and Safety Committee Tabling
Hi Bob B,
This is a reminder that you have signed up for the Health and Safety Committee Tabling on 9/7 at 10 AM - 11 AM at MU table #1.
Your officer at that time is Amy A. His/Her contact is (123) 456-7890, firstname.lastname@example.org.
From your Officers <3