1. Automate the boring stuff…

A client needed to send short email every day with pre-prepared content to a list of people in google contacts who have a specific label. At the moment, they are doing this by creating and scheduling each individual email – but gmail can only schedule up to 2 weeks in advance, despite 3 months of content being available.

My initial thought was to use a python script, but deploying it on a server and getting API keys for various things becomes a bit of responsibility especially when it’s an outside organisation. Having investigated a few options, I decided to look at Google Apps Script which runs javascript code  within Google Sheets. It can access all other data in that account’s Google products such as Gmail and Google Contacts.

 

2. Create the Google Sheet

The first step was to create a new Google Sheet, with dates in one column, and the content for the email for that date next to it. This is very easy for anyone to update without coding knowledge. I also setup a ‘greeting’ and ‘ending’ line in the same sheet so these can be customised without changing the code.

 

3. Create the Google Contact label and get the group id

Assuming you have a list of contacts who have a specific label in Google Contacts, we now need to lookup the group id for this label, which we can do with a temporary piece of code.

Click Extensions > Apps Script to open the coding window, and paste in the code from ‘get_contact_group_id.js’, the second script in the gist below.

Google Apps Script does not have direct access to Google Contacts Groups, we need to enable the “Google People API” instead, which allows fetching contacts based on a label. To do this in Apps Script:

  • Click on Services (left panel, looks like a puzzle piece 🧩).
  • Scroll down and find the People API.
  • Click Add.

Now,  select the ‘listContactGroups’ function and click ‘Run’ in the Apps Script window, and you’ll get a list of group names along with their id. You’ll need the id relevant to the name of the group label you want to send emails to, so if we wanted to use the group name ‘Supporters’, we’d need the id number after the / which is highlighted on the image below in red.

4. Create the Apps Script

We can keep the the above code in place (or delete if you prefer), and paste in the ‘daily_email.js’ code from the gist below, making sure to change groupId in line 2 to the id we just found in part 3.

Make sure the the Google Sheet ‘sheet’ or ‘tab’ name is entered

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sheet_name"); // Ensure correct sheet name

Change the email subject

GmailApp.sendEmail("", "Email Subject", fullMessage, {

Finally add the admin email address

var notificationEmail = "your_notification_email@example.com"; // Change this to your actual email

(this address will receive a notification if there is no content in the sheet for the daily email).

 

5. Add Permissions & test

The first time the script is run it will ask for permission to access gmail, so this is a good time to do a manual test run (you may want to change the group id for a test email address at this point):

  • Click Run ▶️ (sendDailyEmail()) in the Apps Script editor.
  • A Google Authorization Prompt will appear.
  • Click “Review permissions”.
  • Choose your Google account.
  • Google will show a security warning (since the script isn’t from an official publisher).
  • Click “Advanced” > “Go to [Your Script Name] (unsafe)”.
  • Click “Allow”.

You should receive the email based on the content from the date entered into the google sheet!

 

6. Add time trigger

We want this script to run once every day, so click the timer icon ‘trigger’ on the left panel > Add New Trigger. You can then choose the correct function and the approximate time:

 

7. Conclusion

Deploying a Python server or microservice was overkill for this automation, so it was useful to see there are some great scripting options within google workspace – and it also looks like there Script Lab will allow you to do something similar in the Microsoft ecosystem. The code can mostly be written by AI anyway so its easy for non-coders to use. Check out this post for some ideas of the kind of automations you can doo with App Scripts.

This is definitely worth considering for basic / small scripts, but they do need to fit into these limitations. You’ll see in the code that we need a work around for one of these limits to send the emails in batches of 50 addresses at a time, which is the maximum number of recipients that App Scripts supports.