Google Sheets Automation Using Google Apps Script

Step 1 : Sample Google Sheet

Step 2 : Write the Automation Script

function sendEmail() {var ss = SpreadsheetApp.getActiveSpreadsheet();var responses1 = ss.getSheetByName(“Sheet1”);var responses2 = ss.getSheetByName(“Sheet2”);
// Getting data from the sheets// Getting the subject, body and the footer from the sheetvar 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();
//Row 1 and Col 1var EmailSubject = data1[0][0];var EmailBody = data1[0][1];var EmailFooter = data1[0][2];
data2.forEach(function (row,i) {MailApp.sendEmail(row[1],EmailSubject,’Dear ‘ + row[0] + ‘,\n\n’ + EmailBody + ‘\n\n’ + EmailFooter);});


Currently pursuing B Tech at MPSTME. Technical Blogger, interested in Google Workspace. Hope to remain a life long student. Created a course on Google Classroom

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store