Jul 7, 2020
Send Automated Emails 📧 using Google Sheets 🎼 & Google Apps Script ⽂
Have you been in a situation where you had to look out for options to send out automated bulk emails customised for each recipient? There are multiple marketing automation platforms and email marketing services available which would allow you to get this activity done. But the problem with these platforms is that the free version comes with a Sent From XYZ tag along with a few other limitations. Opting for the paid version doesn’t make sense if you are not a regular user of this tool.
Let’s consider a day in the life of a B2B Product Manager who would want to communicate the list of features and bug fixes released as part of the last sprint. The practise of keeping all the stakeholders informed about the new product feature enhancements in the system helps in creating transparency and builds a sense of trust. Suppose the release email has to be sent out to 100+ clients who are using the system. Using a free version of marketing automation tool comes with the risk of getting ignored or marked as spam.
You can use Google’s App Script tool and Sheets to build an end-to-end automated email system. I will run you through the entire process of setting up: the master sheet, template for the email and Google script for auto-sending.
Setting up the master sheet - Create a Google Sheet file with the given column names: First Name, Last Name, Email Address and Status. From Tools open Script Editor. Create a project in the script editor to get started.
Setting up template for the email - Email template allows you to format the email you’d want to send out to multiple people in one go.
You can create a HTML Template by navigating to File > New > HTML File.
If you are comfortable with HTML then you could create your own fancy templates. If not, you could use tools which are easily available to generate your HTML content.
For the starters, you could refer to the template that I’ve used for my communications. https://github.com/sajitnandkumar/automated-emailer-google/blob/master/mail_template.html
Note the line <p>Hey <?= first_name ?>,</p>, this is for creating dynamic content in the email using the template provided. In the above context, this line is used to generate the first name of the recipient dynamically from the master sheet. You could use a different variable to customise the email with different content for each recipient.
Setting up the script for auto-sending email - Let me try and give you a quick overview of the content of this file - https://github.com/sajitnandkumar/automated-emailer-google/blob/master/sendEmail.gs
1. The HTML template we defined in the previous section is referenced in this script.
2. The script allows you to add attach files with the email. For this, you have to upload this file in your Google Drive and copy the file_id generated by Google Drive. The file_id would be a string of characters — which is a unique identifier for the file available in your drive.
3. The script has a function which is used to set salutation for the email. You could create more similar functions if you want the email content to be dynamic and picked up from the master sheet.
4. Email Subject and From Name of the email details are also part of this script which can be customised for delivery.
5. The master sheet has a column — Status which is available to track the status of email trigger. If the status column has the value EMAIL_SENT, then that row is excluded from the automated email list.
All you have to do next is click on the RUN icon available in the App Script Project view to initiate the auto-emails.
The Google App Script framework allows you to set up the entire workflow very quickly. Also, it allows you add some additional features like setting up scheduled emailers, track the events that got triggered and many more. You could read more about the same here.
You could improvise on this entire workflow by building custom add ons for Google Sheets and integrate it with the App Script. If time permits, then I will add add another article to explain the setup.
PS: If you are thinking about scale, fancy email templates and less efforts, then I would recommend going for a paid version of one of the recommended email marketing services.
PPS: If sending out such email communications is a weekly / bi-weekly / monthly activity, you have enough one-time bandwidth for setting up the HTML template & the script and you do not want to spend on third party tools, then Google helps you do this effortlessly.
Liked reading this? Follow me here for no reason — https://twitter.com/bigman_pigman 🤗
