Sending emails on a schedule with Google Apps Script

Ever wondered how you would automate tasks in Google Workspace without using a tool developed by an external entity? Google has your back.

We have something called Apps Script that allows us to add functionality to drive files like sheets, forms, docs, etc.

In this article, we will see how one can send emails on a schedule. As an example, I have used an annual event. A birthday reminder script, if you will.

Let’s get into it. Here goes -

• Open a new sheet
• (Optional) Rename the first tab in the sheet. I do this because I use the name of the sheet to fetch different ones.
• Format data like you want it. The rule of thumb would be to have data in a format which everyone can read and understand. I have something like this -

• Go to Tools -> Script Editor.

• You can find the code for this in my github repository — https://github.com/dotslash-baunthy/birthday-reminders
• The data we require is in the files Code.js and Global Variables.js. The other files exist because I use clasp for using Apps Script in a local IDE (VS Code).
• Go ahead and copy data from Code.js to Code script file in the Script editor you opened previously.
• Create a new script file in the editor (the name does not matter), copy data from Global Variables.js and paste it there.
• All data from the sheet is fetched at once in the allData variable — SpreadsheetApp.getActive().getSheetByName(‘Birthday reminders’).getDataRange().getValues() — Gets all the data in the Birthday reminders sheet of the container spreadsheet (Global Variables script file).
• Testing for birthday is done inside the forEach loop (Code script file).

if (birthDate.getDate() == todayDate.getDate() && birthDate.getMonth() == todayDate.getMonth()) { send emails }

If the month fetched from the sheet matches the current month (when the script is being executed) and the date matches today, it is the user’s birthday and an email is to be sent.

• Run the createTrigger function in the script. This is only supposed to be done once. It will tell your script that it has to run the sendReminders function (which is actually doing the heavy lifting) every day between 6 to 7 am.

• When you run the function to install triggers, Google will ask you to review the permissions you are providing to the script -

• Read, compose, send, and permanently delete all your email from Gmail — We are using GmailApp to send emails only. The wider permissions are employed because the GmailApp class has other functions as well (which we are not using in this project).
• See, edit, create, and delete your spreadsheets in Google Drive — We are reading data from a sheet to gauge when reminders are to be sent. We are only reading data, not modifying it. Neither are we creating or deleting new sheets.
• Allow this application to run when you are not present — This one is pretty self explanatory. The script needs to run on a trigger when you are not present. It is not ideal to have a guy manually run the sendReminders function everyday, is it?
• We have an email address in the Global Variables script file that specifies who to send emails to in case sending the reminder fails due to some reason. Do not forget to modify the value of the errorEmail variable.
• Once all that is done, you are now set to send birthday emails on a schedule.

Some things to note -

• Although Apps Script can enable us to do more complex tasks, they should be used for basic tasks only. I say this with some experience. There are a lot of limits which are more severely applied on Apps Scripts (https://developers.google.com/apps-script/guides/services/quotas) than say something like App Engine or Cloud Functions.
• The script we have with us is a very basic one. Although it should generally work, it might need some tweaking to display different messages or compensate for a different format in the sheet.
• We can use clasp (https://developers.google.com/apps-script/guides/clasp) to use Google Apps Script on our computer locally, in an IDE.
• Please, please, please try to understand what we did. It will definitely help for future projects. Google has amazing documentation for all it’s APIs for developers and regular users like us. Check under Advanced Google Services here — https://developers.google.com/apps-script/reference?hl=en