.png&optimizer=image&quality=80&width=280)
Table of Contents
Tags
Welcome!
In my constant attempt to help clients automate internal processes using Google Workspace tools, I came across a new problem that my university was facing.
My university wanted to make announcements regarding college events, marks, student attendance and more, but the process involved a lot of manual work. They had all the emails stored in a Google Sheet and wanted to automate the entire process of sending emails.
So I developed a Google Apps Script code to automate this process where on the click of a button all the emails are sent to the students without any manual effort.
In this blog we will be taking a look at how to automate the bulk emailing process using Google Apps Script.
Sample Google Sheet
The Google Sheet that I will be using for this blog contains the name of the students and their email address. If you want to work with the sheet, click here.
Step1: Write the Automation Script
While you are in the Google Sheet, let’s open up the Script Editor to write some Google Apps Script. To open the Script Editor, follow these steps:
- Click on Extensions and open the Script Editor.
2. This brings up the Script Editor as shown below.
We have reached the script editor, let’s code.
function SendEmail(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("Student Details");
var data = sheet.getRange(2,1,sheet.getLastRow() - 1,2).getValues();
//Logger.log(data);
We are going to start off by creating a new function SendEmail(), inside which we will be accessing the Google Sheet and the data inside the sheet. To do this we will be using the SpreadsheetApp() to access the Spreadsheet, followed by getting the sheet by name using the getSheetByName().
After getting the sheet by name, we have to get the data present inside the Google Sheet.To do that we use the getRange() function where we pass in two parameters, the start row and start column. After which we get the last row and then use the getValues() function to get the values.
After this we just print the data using the Logger.log function and the following output appears.
Next we are going to be getting the individual names and emails so we can send custom emails to the student.
data.forEach(function(row,i){
var name = row[0];
var email = row[1];
Logger.log(name)
Logger.log(email)
Here we are going to be using the forEach() function to iterate through the data. After iterating through the data we get the name and email by using the row[] function. After this we go ahead and print the data using the Logger.log function.
Here you can see that the individual names and email addresses have been printed. Using these names and emails we can send custom emails to the students.
var body = "Dear "+ name + "<br><br>"+
" It was noted with serious concern that quite a few students were detained last semester on account of attendance default. Despite monthly attendance information" + "<br><br>"+
"being provided to students and regular counselling by faculty members, it is found that students do not take the attendance rules seriously. This results in losing an academic year affecting career" + "<br><br>"+
"prospects after graduation. It is also noted that students do not read the SRB and are not aware of the various rules and regulations and the repercussions of non-compliance."+ ",<br><br>" +
"Regards,"+ "<br><br>"+
" XYZ University " + "<br><br>";
var subject = "Student Attendance";
GmailApp.sendEmail(email, subject, "", { htmlBody: body } );
});
Here we have designed the email body using the data that we got from the sheet such as the name and email. After designing the body we are going to be using the GmailApp to send these emails to the students.
We start off by declaring the subject of the email after which we use the GmailApp to send the emails. To send the emails using the GmailApp we are going to be using the sendEmail function. Inside this function we pass the target email address, followed by the subject and the body of the email.
Our code is complete and good to go.
You can enhance the code by making it run automatically using Triggers or creating menus. To know more about that check out the link given below.
Step2: Check the Output
Our code is complete and good to go. Lets go ahead and see if our code is working and good to go.
On running the code, the code will send individual custom emails to the respective students.
Here you can see that the code has successfully sent an email to the student containing all the details.
Conclusion
In this blog we saw how we can bulk share emails using data in Google Sheets and Google Apps Script. You can use this code to send emails whenever required, you can make weekly announcements by using triggers that will automatically send the emails to the students.
You can get the code from the GitHub link below.
Collection of Google Apps Script Automation scripts written and compiled by Aryan Irani. …github.com
Feel free to reach out if you have any issues/feedback via
.