HOW TO SEND EMAIL FROM GOOGLE SHEETS

INTRODUCTION

EMAIL is one of the most important mode of communication today. It is reliable , fast, realtime and green replacement of the traditional posts.

Think of a scenario where we have thousands of email addresses of different people in Google Sheets and we want to send a mail to them regarding some information or a welcome or thanking mail. Seems impossible?? It’ll be really a cumbersome task if it is to be done manually.

But thanks to Google Sheets for providing us API for sending the email directly from the Google Sheets to the various recipients.

In this article we would learn to send email from the Google Sheets directly to various recipients.

We’ll take help of GOOGLE SCRIPT to send email directly from google sheets.

STEPS TO SEND EMAIL FROM GOOGLE SHEETS DIRECTLY

Let us take an example to learn how to send email from Google Sheets.

For the example,

We’ll create a table containing some information about the recipients.

NAME	EMAIL ID	RESPONSE
DENIS	denis@abc.com	RECEIVED
GYANKOSH	gyankosh060309@gmail.com	NOT RECEIVED
ARTHUR	arthur@abc.com	NOT RECEIVED
KRANE	krane@abc.com	RECEIVED
DAVID	david@abc.com	NOT RECEIVED
MARY	mary@abc.com	RECEIVED
JOHN	john@abc.com	RECEIVED
BERNS	berns@abc.com	RECEIVED
SEN	sen@abc.com	RECEIVED

STEPS TO SEND EMAIL FROM GOOGLE SHEETS

It is really easy to send email from google sheets to different email addresses at once without caring to open the account and send any individual email address.

Thanks to ready made API given by google sheets.

Let us learn to send the email directly from google sheets.

STEP 1: ARRANGE THE INFORMATION IN A TABLE IN GOOGLE SHEETS

The first steps is to arrange the data as per requirement in the sheet.

THE THREE MAIN REQUIREMENTS OF SENDING AN EMAIL FROM THE GOOGLE SHEETS ARE – AN EMAIL ADDRESS, SUBJECT [ OPTIONAL ] AND A MESSAGE BODY.

For the understanding purpose, we are taking a simple example with only three columns containing the above mentioned three requirements.

For our example, we have already arranged the data with a name, email id and the response status.

The DETAIL TABLE is shown below.

STEP 2: OPEN SCRIPT EDITOR IN GOOGLE SHEETS

As we are going to send the email from these sheets using the GOOGLE SCRIPT (Just like VBA in Excel) , we are going to write script for the same.

• Click on TOOLS>SCRIPT EDITOR.
• It’ll take us to a new tab which will show the Script Editor as shown in the picture below.

STEP 3: TYPE THE CODE IN THE GOOGLE SHEETS SCRIPT EDITOR

After we have entered the SCRIPT EDITOR, it is time to create a script to send the email to different recipients.

Copy and paste the following script in the script editor.

//Gyankosh.net
//Send email from GOOGLE SHEETS directly to different mails.

function sendEmailfromGoogleSheets()

{
 var sheet = SpreadsheetApp.getActiveSheet();
 var firstrow = 2; 
 var numberofrows = 9; 
 var firstcolumn=1;
 var numberofcolumns=3;

 var dataRange = sheet.getRange(firstrow,firstcolumn,numberofrows,numberofcolumns);


var data = dataRange.getValues();

 for (var i = 0; i < data.length; i++)
  {
   var row = data[i];
   var Name = row[0];
   var Email = row[1];
   var Response = row[2];
   var message = "Hi " + Name + "! Your Response is" +Response;
   var subject = "Gyankosh.net";
   MailApp.sendEmail(Email, subject, message);


  };
};

STEP 4: RUNNING THE CODE

Now we are ready with our code in the Script Editor to be run and tried.

• Select the function name from the SELECT FUNCTION option in the code editor. [ Normally it ‘ll be automatically selected but if there are more than one functions, we need to select it. ]
• Click RUN button and the code will run.
• Check the email and the body in the destination mail.

EXPLANATION OF THE CODE TO SEND EMAIL FROM GOOGLE SHEETS

Let us understand the code used for sending the emails from google sheets.

The code used is given below with the explanation of each and every step.

//Gyankosh.net
//Send email from GOOGLE SHEETS directly to different mails.

*Comments are used starting with a DOUBLE SLASH (//). Any text whether it is part of script or not is not compiled.

function sendEmailfromGoogleSheets()

The declaration of the function. A function is a self contained code snippet which performs some predefined task and may or may not return any value.
{
var sheet = SpreadsheetApp.getActiveSheet();

*Fetching the Active (opened ) sheet from the Spreadsheet.
var firstrow = 2;
var numberofrows = 9;
var firstcolumn=1;
var numberofcolumns=3;

*Declaration of variables and passing different values to them.
var dataRange = sheet.getRange( firstrow ,firstcolumn ,numberofrows ,numberofcolumns );

*We declare a variable datarange and pass the selected range to this datatype. For passing the range we use the getRange method of the sheet.

getRange is passed with four arguments as (Startingrow, Starting column, number of rows , number of columns).

Our data is stored between A2:C10. which means our starting row is 2, number of rows are 9. Starting column is 1 and number of columns is 3.

The values passed above can be correlated to the explanation.

var data = dataRange.getValues();

*Now as the datarange has been defined, we declare a var datatype data and get all the values from the dataRange.

data range type variable now contains all the values in the form of 2D arrays of rows and columns such as data[0] is first row and row[0] is the first column.
for (var i = 0; i < data.length; i++)
{

var row = data[i];
var Name = row[0];
var Email = row[1];
var Response = row[2];

*A loop is started till the number of rows and all the values are extracted
var message = “Hi ” + Name + “! Your Response is” +Response;

*A message variable is created using the values in the table and putting the custom strings.
var subject = “Gyankosh.net”;

*The subject is declared. It can be an explicitly declared string or again from the table.
MailApp.sendEmail( Email, subject, message);

*MailApp class’s sendEmail method used to send the email finally.

The mail will be sent from the account you are logged in. For Example, if you are logged in the google sheets with a particular account, the mails will be sent from the same email account.

First time, gmail would ask for the permission which can be provided by you.

};
};