In this article, we are going to learn how to send automated emails using a Gmail account, Google Sheets and Google Apps Script.
First, create a new Google Sheet by logging into your Gmail account.
You may also start a new spreadsheet in Google Sheets by typing sheet.new in your Chrome browser.
Enter a few test recipient email addresses, some dummy texts for the Subject and Body columns as shown below? (screenshot below showing dummy email addresses - you may replace them with your own email addresses).
Assuming we are going to send out three emails using the following recipient email addresses, Subjects and Body.
Next, click on Tools->Script editor to open the script editor.
If you are new to Google Apps Script, it is a JavaScript based language to automate tasks across Google products. The script editor is the place where you can write and run scripts. Click below link to find out more about Google Apps Script.
Give a name to this project. E.g. Send Automated Gmails.
The window panel on the right is where you type in your script codes.
Before we start sending emails automatically, let’s spend some time understanding how Google Apps Script can access data from Google Sheets.
At Google Apps Script, we want to be able to access the data in Google Sheets. In order to do this, we need to tell Google Apps Script the following:
Which Spreadsheet in Google Sheets are we accessing? You can use the unique SheetID generated by Google Sheets (more on this below). You can also specify the Spreadsheet by Name “GAPS-send automated gmails”
Which Worksheet inside the Spreadsheet, in this case is “Sheet1” (see below screenshot
What data are we extracting from Google Apps Script or adding data to?
The unique SheetID is the series of alphanumeric between /d/ and /edit#
The following scripts perform the following tasks:
Specify the Spreadsheet in Google Sheets to communicate with using SpreadsheetApp.openByID and save it in a variable ss.
Specify the Worksheet within the Spreadsheet to work with using getSheetByName (in this case, Sheet1) and save it in a variable ws.
Obtain the data from the Worksheet using getDataRange() save it in a variable known as data
Display the data in the Worksheet using Logger.log
Click Run to execute the script codes.
If you are running this script for the first time, you will need to give authorisation to Google Apps Script to access your data in Google Sheets.
Click Review permissions and Allow.
The execution log shows you what data is contained inside the Worksheet (Sheet1).
Let’s examine the original data in Google Sheets
and compare it with the logged data after executing the script.
Let’s organise the logged data abit by moving each array in its own row. (To do this -copy and paste onto any text editors.) You will see from below that there are 4 rows of data, with each row containing 3 columns separated by “,”.
This is how Google Apps Script represents rows and columns, using an Array of Arrays.
[
[Email , Subject, Body],
[test@xyz.com, Hello from GAPs, You are receiving a test email],
[test1@xyz.com, Hello from GAPs, You are receiving a test email],
[test2@xyz.com, Hello from GAPs, You are receiving a test email]
]
Well done for completing this far!
The Variable ‘data now contains all the data in the Spreadsheet.
Now that we understand how to access data from Google Apps Script, we are going to learn how to go through and extract each row of the data to send out the emails.
In programming, when a set of instructions is carried out in a repeated manner, it is called looping.
Looping
We introduce a new variable known as i. Set the value of the variable i to 2, increasing its value by 1 on each loop iteration until it reaches the last row.
i is set to 2 initially because the first row contains the headers which is not required.
To find the last row of data in Google sheets, we use the following script.
var lastRow = ws.getLastRow();
The following scripts loop through the 3 rows of data and send out an automated email using MailApp.sendEmail each row at a time.
function sendAutomatedGmail(){
var ss = SpreadsheetApp.openById("1xxxx2bH-3xxxTM");
var ws = ss.getSheetByName("Sheet1");
var data = ws.getDataRange().getValues();
Logger.log(data)
var lastRow = ws.getLastRow();
for (var i=2;i<=lastRow;i++){
MailApp.sendEmail(ws.getRange(i,1).getValue(), ws.getRange(i,2).getValue(), ws.getRange(i,3).getValue());
}
}
