PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

Contents

HOW TO SEND EMAIL FROM GOOGLE SHEETS

INTRODUCTION

EMAIL is one of the most important mode of communication today. It is realiable , 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.

NAMEEMAIL IDRESPONSE
DENIS[email protected]RECEIVED
GYANKOSH[email protected]NOT RECEIVED
ARTHUR[email protected]NOT RECEIVED
KRANE[email protected]RECEIVED
DAVID[email protected]NOT RECEIVED
MARY[email protected]RECEIVED
JOHN[email protected]RECEIVED
BERNS[email protected]RECEIVED
SEN[email protected]RECEIVED

 

EXAMPLE TABLE CONTAINING EMAIL ADDRESS AND OTHER DATA

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 readymade 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.

ARRANGED DATA FOR SENDING E MAILS

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.
ARRANGED DATA FOR SENDING E MAILS

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);

};
};
SCRIPT TO SEND MAIL FROM GOOGLE SHEETS

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.
STEPS TO RUN CODE IN GOOGLE SHEETS

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.

};
};

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Share this:

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: