Google Sheets Automation Using Google Apps Script

Sending emails in bulk is an important requirement for most businesses. You often have situations, where you need to send the same email with a few customisations to tens if not hundreds of recipients.

In this blog post, we will cover how you can send a bulk email to multiple recipients via Google Sheets. I will be covering a fictitious example of the following:

• We need to send an email to 100 residents of a housing society about the maintenance bill that has to be paid by the housing society residents.
• The task of writing an individual email to each of the 100 recipients is a cumbersome process and we will use Google Sheets and a little bit of Apps Script to automate the whole process.

So let us get started.

Step 1 : Sample Google Sheet

The sheet that we will be using is shown below (If you would prefer working with a Copy of the Sheet, click here).

In this spreadsheet, I have created two sheets. In the first sheet (Sheet 1), which you see above, I have put the subject, body and footer , that has to be sent in the email.

In the second sheet (Sheet 2), that you see below, I have put all the names, their address and email id’s of all the residents .

The process that we want to automate is the following:

1. The user of the sheet goes to Sheet 1 and fills out the values for Subject, Body and Footer of the Email.
2. The script (which we shall write in a while) will then look up the entire list of email recipients , listed in Sheet 2, and send the email with some personalisation for each user i.e. their name.

Step 2 : Write the Automation Script

While you are in the sheet, launch the Apps Script Editor.

To do that:

1. Click on Tools in the main menu.
2. Click on Script Editor.

The menu option is shown below:

This brings up the Script Editor as shown below:

Paste the following code in the Editor:

Now let me explain the different parts of the code:

function sendEmail() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responses1 = ss.getSheetByName(“Sheet1”);
var responses2 = ss.getSheetByName(“Sheet2”);

Here I am going to get the data from the two sheets:

• The variable responses1 contains the contents of the sheet1, that contains the subject, body and the footer of the email that has to be sent to the residents.
• The variable responses2 contains the name, flat number and the email id of the residents.

// Getting data from the sheets
// Getting the subject, body and the footer from the sheet
var data1 = responses1.getRange(2,1,2,3).getValues();
// Getting the email address of the residents
var data2 = 
responses2.getRange(2,3,responses2.getLastRow() — 1,4).getValues();

Now, we are going to get the data out of the sheets and assign it to suitable variables.

From Sheet 1 we are going to take the following data:

• Subject of the Email
• Body of the email
• Footer of the email

//Row 1 and Col 1
var EmailSubject = data1[0][0];
var EmailBody = data1[0][1];
var EmailFooter = data1[0][2];

From Sheet 2 we are going to take the email address of the residents.

We are going to assign the data to suitable variables.

• Since the email subject is in the first row and first column, I have written data1[0] [0] ; this will get the data from the first row and first column.
• Since the email body is in the first row and second column, I have written data1[0] [0].

You might ask why I have written data1. This is because we have got the entire sheet’s data into the variable data1. The same is done for sheet 2, the entire sheet’s data is put in data2.

data2.forEach(function (row,i) {MailApp.sendEmail(row[1],EmailSubject,’Dear ‘ + row[0] + ‘,\n\n’ + EmailBody + ‘\n\n’ + EmailFooter);
});

We have got the subject, body, footer of the email and the email id of the residents . Now we are going to put the variables in the Gmail App function. This will take the email id, subject, body and the footer and put it in the Gmail App function.

Our code is complete. Select the correct function (sendEmail) as shown below and run the program.

The script on successful execution will send out an email to each recipient. A sample email is shown below:

Summary

We saw how you can automate the process of sending out bulk email to multiple recipients. This can be used by organisations to send out emails in bulk and reduce the work of the people sending the emails. Another benefit of automation, is also process efficiencies and reduction in errors.