Automate Reminder Emails for Upcoming Games
Category: Advice for Coaches
Do we have a game this weekend? What time is the game? Where is the game again? Yeah it gets annoying pretty quickly. What if there was a way to remind players and parents about an upcoming game without having to remember to click Send each week? With this article, you’ll learn how!
You may want to read VERSION 2: Automate Reminder Emails for Upcoming Games for an even easier method!
First, we need a schedule of games and a list of email addresses who wish to receive email reminders. Let’s use Google Drive, which provides features such as presentations, documents, and you guessed it, spreadsheets. Why use this? Well it’s available to you from anywhere if you have an internet connection and best of all it’s FREE! If you have a Google account already, that’s great. If not, sign up for a Google account with your preferred email address. You don’t actually need a Google email address.
Guess what? To make it easier, I already created the spreadsheet for you! You can view the spreadsheet even if you’re not signed in to Google Drive. But we want to make a copy of the spreadsheet, so sign in and then access the Schedule/Roster spreadsheet.
Choose File > Make a copy. Define a name for your spreadsheet and click Ok.
The spreadsheet will open, which has now been copied to your drive. There are two sheets. The first one is called Schedule. The second one is called Roster. Take some time to fill out your schedule and roster details. The email addresses defined on the Roster sheet will receive the reminder email.
Do not change the name of these sheets or the order of the columns. If you do, the email functionality will not work.
Once you’re done setting up your schedule and roster information, you’re now ready to define when the email should be generated. Choose Tools > Script Editor. Don’t get scared by what you see. Like I said, the spreadsheet was already set up for you and so was the email script, which is responsible for generating the content of the email. It uses Google Apps Script.
Try not to play around with the code if you don’t know what you’re doing. The email functionality may not work if you make edits.
We are interested in setting up a timed trigger. Choose Resources > Current project’s triggers. You’ll see text saying “No triggers set up. Click here to add one now”. Click it.
The email script is set up primarily for weekly games. Let’s say your games are on Sundays. Then I would want the email to be generated at the beginning of the week. How about Monday? This should allow enough time for players or parents to contact you if they will not be able to make the upcoming game. Set the trigger as defined above and click Save.
You will receive a pop up window asking you to authorize the script to run. Click Authorize. You have now authorized the script to run. Click Save again. You can close the script editor and spreadsheet.
What does the email look like?
You’ve defined your schedule, your roster, and set up the timed trigger. An email will be generated every Monday morning if the game date is within a week. You can see an example of what the email will look like in the above screen shot.
- The subject of the email includes the upcoming game date, time, and field name.
- The content of the email includes the upcoming game date, time, field name, and field address.
- Clicking the field address will bring you to Google Maps to make it easier to get directions.
- The entire schedule is included at the bottom of the email.
You have now set up automated email reminders for your team! Sit back, relax, and enjoy! Don’t forget to share this with your fellow coaches!