Automated Google Appointment Calendar Follow-Up Emails

Posted on Author bradshreff

Do you use Google Appointment Slots and wish you could have an automated thank you message with a survey link included each day? Well, you can!

A couple years ago at #ISTE, one of the EdTech Coaches mentioned using the Google Appointment Calendar feature in Google Apps for Ed. It’s really easy to setup and gives you some decent customization options. You create the time-periods and then the appointment length. If you want more details on this, checkout the Google Product Forum for it. This system has worked great for me as a coach. My teachers can click a link, book me when they want me to be there, and I come to them for whatever they need.

I wanted to get a good way to track the effectiveness of these meetings, so I created a Google Form that asked my teachers to rate their satisfaction with the support they received. I sat down each day at the end of the day and sent the emails one at a time. Tedious and frustrating.

After searching for what seemed like an obvious option (automated follow-up emails), I found nothing that accomplished this goal, so I set about creating my own.

Using a combination of Google Sheets and VBS Scripts, I made a solution that works, and now, I am sharing it with you.

Following the steps below will create an automated email sent each day to anyone who booked an appointment with you. It will also check the submitted responses of the feedback form, and if they don’t complete the survey within 3 days, sends them a reminder.

  1. Make a copy of this Google Sheet (will prompt to make a copy automatically): BradShreffler.com – Calendar Emailer
  2. Create a Google Form that asks the questions you want to ask. If you want an example of what I’m using, checkout this one. Also, make sure this form is set to collect email responses.
  3. Create the responses Google Sheet by clicking the green square in the top right corner from Google Forms Responses tab.
  4. In the BradShreffler.com – Calendar Emailer Sheet, paste the link for the Google Form (the send link you would give for someone to take the form) in cells B9 and B15.
  5. Paste the URL for the Responses sheet for the form in cell B17
  6. Adjust the other fields of this first page so that the email includes your name, the message you want sent, etc. DO NOT merge cells, add cells, delete cells, or move any cells around!!
  7. Go to the “Form Responses” sheet and click on cell A1, then click “Authorize”
  8. Click on Tools -> Script Editor
  9. Click the “Triggers” icon 
  10. Create a trigger for “import_calendar” as Daily and roughly at the end of your workday.
  11. Create a trigger for “sendEmails” as Daily and an hour after the previous trigger.
  12. Should look like this when it is setup:
  13. Authorize the app for the requested permissions.

If you have any issues at all with this, please let me know!! Also, if these steps are too cut and dry for you, feel free to use this video walkthrough I made.