Scenario:

We have prepared a Google spreadsheet with list of email recipients which looks like below:

First Name	Last Name	Email	Message
Apurwa	Singh	apurwa@gmail.com	Dear Mr Singh, We would like to invite you for the NAC meeting. Regards.
Nagesh	Badu	nageshbadu@gmail.com	Dear Mr Badu, We would like to invite you for the NAC meeting. Regards.
Bikram	Khatiwada	markibkhatiwada@gmail.com	Dear Mr Khatiwada, We would like to invite you for the NAC meeting. Regards.

Note: The name of the sheet is members.

With mail merge, we can send these three custom emails at one go.

Color Codes:

• Class in teal

• Method in green

• Variable in red

Steps:

1. Open the spreadsheet.

2. Select Script Editor from Tools menu.

Interface of script.google.com will open where you can write your own script for the given spreadsheet.

 

3. Type the following codes. 

 

function myFunction() { var spread=SpreadsheetApp.getActiveSpreadsheet();

This function will select the current spreadsheet.

var sheet=spread.getSheetByName('members');

This function will select the sheet titled members from the current active spreadsheet.

var lastrow=sheet.getLastRow();

This function will return the numeric value for the the last row of the spreadsheet.

In our case, last row is the fourth row. Hence, lastrow is equal to 4.

We need to do this to loop through the second row to the last row.

for(var i=2;i<=lastrow;i++){

Looping from the second row to the last row.

We dont need to read the content of the first row since values start from the second row.

Values of i throughout the loop will be as follows:

2

3

4

var email=sheet.getRange(i,3).getValue();

For a given i, sheet.getRange(i,3) is the value for email column.

Example:

When i is 2, sheet.getRange(i,3) becomes sheet.getRange(2,3) which represents the cell at 2nd row and 3rd column. 

sheet.getRange(2,3).getValue() pulls the content from the given cell which is apurwa@gmail.com.

Thus when i is 2, email variable becomes apurwa@gmail.com.

var lastname=sheet.getRange(i,2).getValue();

For a given i, sheet.getRange(i,2) is the cell containing column Last Name.

Example:

When i is 2, sheet.getRange(i,2) becomes sheet.getRange(2,2) which represents the cell at 2nd row and 2nd column. 

sheet.getRange(2,2).getValue() pulls the content from the given cell which is Singh.

Thus when i is 2, lastname variable becomes Singh.

When i is 3, lastname variable becomes Badu and so on.

var message='Dear Mr'+lastname+',\nWe would like to invite you for the NAC meeting.\nRegards.';

When i is 2, message translates into following:

Dear Mr Singh,

We would like to invite you for the NAC meeting.

Regards.

\n creates a new line feed.

GmailApp.sendEmail(email,'Invitation for NAC meeting', message);

This function sends an email with three parameters; email, subject and message.

} }

 

Code Summary:

function myFunction()

{

var spread=SpreadsheetApp.getActiveSpreadsheet();

var sheet=spread.getSheetByName('members');

var lastrow=sheet.getLastRow();

for(var i=2;i<=lastrow;i++)

{

var email=sheet.getRange(i,3).getValue();

var lastname=sheet.getRange(i,2).getValue();

var message='Dear Mr'+lastname+',\nWe would like to invite you for the NAC meeting.\nRegards.';

GmailApp.sendEmail(email,'Invitation for NAC meeting', message);

}

}