Send mass emails using Google Apps Script from a Google Spreadsheet

Image source: Enginemailer

Hello Learners,

In today’s world, everyone is pressed for time, and they want their tasks accomplished as quickly as possible. In between, we have to send emails to the group of people for invitations, meetings, and other details that will vary from person to person. Therefore, sending emails to a group of people with such details one by one will be very time consuming and increase the chance of errors in the sent details.

In this article, we will see — “How to send bulk emails from Google Spreadsheet using Google Apps Script”.

What are the benefits you will get from it?

1. Personalized emails based on your requirements
2. Efforts saved while sending emails to the group
3. Interacting with the right audience with the right details

Google Apps Script implementation of bulk emailing with Google Spreadsheet

We will follow these steps in order to develop bulk email templates.

1. Create a new Google Spreadsheet in your Drive.
2. Provide columns with your required data, such as email, name, personalized information, etc.
3. Open Google Apps Script editor to write the script.

Script Editor path: Extension=>Apps Script (from Spreadsheet menu option)

Google Spreadsheet Extension Menu Bar

As we proceed with script development, let’s go over the steps.

Step 1: In this step, we will create a Google Spreadsheet with the required columns. For the sample, I have added three columns — name, email, and address. See the below image for an example.

Google Spreadsheet demo image

Step 2: Open the script editor after creating the Spreadsheet template and write the following sendEmail() function.

function sendEmail(){
var ss = SpreadsheetApp.openById("Your_Spreadsheet_ID");
var sheet = ss.getSheetByName("Your_SheetName");
var json_data = getData(sheet);
for(var j=0;j<json_data.length;j++) {
var message="Dear "+json_data[j].name+",<br><br> I am glad to invite you at the following address: "+ json_data[j].address +".<br><br>";
message=message+"Best wishes,<br>"+"Team<br><br>";
MailApp.sendEmail({to: json_data[j].email, cc: 'Your_CC_email_ID', subject: "Invitation", htmlBody: message, noReply:true} );
}
}

We have defined spreadsheet id and the sheet name in the sample send email function. Our next section will describe what happens after we call the getData() function, which returns spreadsheet data in JSON format. After that we have written an email template in which JSON parameters have been set, such as name, email, and address between email content, and the MailApp library function has been called to send the email. The entire process will be called in a loop equal to the length of the JSON array, which is the total number of people in Google Spreadsheet.

Step 3: We need to define the getData() function, let’s see the code for that.Write the getData() function to get the Google Spreadsheet data in JSON format.

function getData(sheet){
   var jo = {};
   var dataArray = [];
// collecting data from 2nd Row , 1st column to last row and last    // column sheet.getLastRow()-1
var rows = sheet.getRange(2,1,sheet.getLastRow()-1, sheet.getLastColumn()).getValues();
for(var i = 0, l= rows.length; i<l ; i++){
     var dataRow = rows[i];
     var record = {};
      record['sno'] = dataRow[0];
      record['name'] = dataRow[1];
      record['email'] = dataRow[2];
      record['address']=dataRow[3];
      dataArray.push(record);
   }
   jo = dataArray;
   var result = JSON.stringify(jo);
return jo;
}

Our above declared function has just defined few parameters as follows: range of the sheet, last row, and last column, and values of the range in array form, and it will return complete range values in JSON format.

Step 4: This is the Bonus step, where you can use the following script at the top of your Google Apps Script, if you would like to run this function or call the send email function from the front end of the Google Spreadsheet.

function onOpen(e) {
// Add a custom menu to the spreadsheet.
SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
.createMenu('Custom Menu')
.addItem('Send Emails', 'sendEmail'
.addToUi();
}

In the above code, when the onOpen() function is executed, a custom menu that is named “Custom Menu” is added to the right of the Spreadsheet menu option. The sendEmail() function needs to be defined under the custom menu by adding the addItem library function that calls it from the front end as another option.

I’m available for freelance opportunities and you may reach out via email at dilipkashyap.sd@gmail.com for further collaboration.

If you are interested to learn Google Apps Script and automate your Google Workspace ? must try this e-Book on “Google Apps Script: A Beginners Guide”

I hope this article will help you understand how to send personalized mass emails. If you have any questions, please leave a comment. I would be happy to assist you.

Happy Learning!