Google Sheets and Google Scripts have been a powerful combo for me in the past. A recent request to help required a deeper dive into that combo and include sending emails with Google Sheets. We’ll walk through an example covering the key takeaways from this experience. That includes sending emails based on data in a Google Sheet, ways to structure your data importing, and how to schedule the script to run.
To illustrate what I’ve learned, we’ll run an example scenario. Say we are setting up a customer relationship management (CRM) tool for a bake sale in Google Sheets. We would like our support team to reach out directly to customers to see what they thought of the product and experience.
We want some simple rules:
- Send an email with a list of customers to reach out to once a week.
- Contact customers one time.
- Contact customers no earlier than 10 days after their purchase.
- Only contact if we haven’t talked to them via another communication channel.
Let’s assume we already have the spreadsheet all set up. The next step is to create the script, which is the code that will run against our spreadsheet. We’ll talk about hooking the code up to the spreadsheet a little later.
Script Creation
Data Acquisition
Getting the data from the spreadsheet is pretty simple. Calling the SpreadsheetApp Class gives access to several methods to call. For example, you can use SpreadsheetApp.getActiveSheet().getDataRange().getValues() to return the data on the sheet that is currently selected. Now we have a way to get the data on the spreadsheet into the code where we can make use of it.
Data Validation
It turned out the most challenging part was guarding against changes that would likely happen within the spreadsheet over time. Specifically, the concern was about what would happen if a user renamed a column or sheet. The critical step of gathering info on customers’ experience could be brought down and possibly forgotten, causing incorrect data to be imported to the code.
We thought through how the spreadsheet would likely grow over time. And we realized we couldn’t assume the data would all remain in the same place, spreadsheet, sheet, etc. We planned to combat this a few different ways, such as the use of cell data validation and named ranges. Ultimately, we decided to rely heavily on error handling. Recording an error and sending it to the team when the process breaks seemed like the best and most inexpensive option.
We added `Throws` in the code whenever we were expecting a set of data from the spreadsheet that would be activated when we didn’t receive the expected data. This would report an error and cause the running of the script to fail, thus notifying us that there is likely an issue or change with the data. Upon failing, the Script owner will receive an email about the error that has taken place, notifying the team that a change to the spreadsheet caused our script to fail.
Writing Code
Now that we have confidence that we will receive a notification if something goes amiss, we can write the logic from our business case that we stated above. We would write the code to create a list of customers to contact only if their purchase was beyond 10 days, we haven’t talked to them yet, and it is their first order with us. We’ll handle the delivery of the email to the support team when we configure the trigger.
Sending Emails
The code to send the email is trivial. Google gives us a MailApp Class which gives us a method of MailApp.sendEmail() that expects to include three arguments the email addresses to send the message, the subject, and the body of the message.
Logging
The Logger helps you see the output of the code or a historical record of an action. This will give you the results from all the executions of the code. The Logger can be found in App Script > Executions.
Triggering the script execution
The last step is to make the code we wrote execute when it’s needed. There are a number of ways to ‘run’ the code in the script. For this example, we’ll use the Triggers feature of Google Script. This gives us access to schedule the code to be run on an interval or date of our choosing.
To set this up within Apps Script, go to Triggers and create a new trigger. Select the main function from the code we wrote in the script. Choose Time-Driven as the event source to execute the code, which, in our case, is weekly, Monday at 8 a.m.
If you are leaning on throwing errors as a safety measure, be sure in the Trigger to set ‘Failure notifications setting’ to deliver a notification at an appropriate interval. Setting the Trigger to fire OnEdit can be helpful when writing your code. After making alterations to the code, we would set the trigger to OnEdit and change a cell in the sheet that would make the script run.
Benefits of Sending Emails with Google Sheets & Scripts
This can be a great way to add a little automation to your spreadsheets. There is a little forethought on its design that might help prevent a few headaches along the way.