Send Email from Google sheets with one line of code

Google sheets has been gaining a lot of popularity due to its collaborative features which is very good for small teams to work on data together. Many teams are using Google sheets and one question that often come from them is ‘How do I send email from Google sheets?’

There may be different cases where one needs to send email from Google sheets, few of them may be – auto send email once target data is reached, auto send email to whole team, send customized mails with data from sheets based on Email Id etc.

So I’m writing this quick tutorial on how to send email from Google sheets using Google apps script. Google Apps Script is a JavaScript scripting language of Google apps. It is pretty easy once you start using it. follow next few steps and you will understand how it works.

Getting started

  1. Open any existing google spreadsheet or create one from sheets.google.com

  2. click on tools > script editor, A new window opens which contains a code editor. This is where we write apps script code.

Send basic Email in google sheets

Apps script has a Mail service which can be used to send mail with just one line of code. syntax for sending basic Email :

MailApp.sendEmail(to, subject, body);

Add this line to existing function or create a new function

MailApp.sendEmail("[email protected]", "test mail", "hello from codingislove.com");

send email in google sheets

Save and click run > a dialog pops up for permissions > continue and allow, Mail will be sent to TO address in the code.

That’s it you have sent an email programmatically using one line of code!

Practical use case

A simple example to show how to send custom emails with data from spreadsheet. Lets say my spreadsheet has some data – Names, emails and score of a team.

bulk email google sheets

Goal is to send mails to users along with their score. Go to script editor and start writing code.

function CustomEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A2:C4");
var UserData = range.getValues();
for (i in UserData) {
var row = UserData[i];
var name = row[0];
var email = row[1];
var score = row[2];
MailApp.sendEmail(row[1], "Custom mail", "Hello " + name + ", This is an email report of your score. Your score is " + score);
}
}

Code explanation

Have a look at above code once. It is almost self-explanatory, first we get active sheet, then get range with data using getRange() method and get values in range using getValues() method. This method returns an array of values. read more about arrays here

Then we use for to loop through each user, get their name,email,score and send them an email. To run the script from spreadsheet without opening script editor, I created a button using Insert > drawing > draw a button shape with text send emails > save & close. click on small arrow on top right of the button and assign a script > enter function name. In this case, function name is CustomEmail. Now whenever button is clicked, emails are sent to users.
assign script to button in google sheets

Wrapping up

This was a simple use case of sending emails in google sheets, you can add more functionality like making user data range dynamic using getLastRow() method of spreadsheetApp so that when a new user row is added, email will be sent to that user also without changing any code.

As already mentioned, there are many other ways sending emails can be useful like auto send emails using timed triggers, spreadsheet change triggers etc. Explore more about Mail service using official documentation here : Mail Service – apps script

Example of timed trigger here – Google sheets timed trigger and email notifications example.

Update : Checkout next part of this tutorial – Create Auto responding google form with 2 lines of code

Also there is a limit on number of emails that can be sent using this service – check limits here : Quotas for google services

Read documentation, open a spreadsheet and start experimenting with code, you’ll fall in love with code ❤️ Coding is Love

If you have any questions or feedback, comment below.

Ranjith kumar
0 0 votes
Article Rating
Subscribe
Notify of
guest

103 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
adam
adam
7 years ago

hey there- can you elaborate on : “you can add more functionality like making user data range dynamic using getLastRow() method of spreadsheetApp so that when a new user row is added, email will be sent to that user also without changing any code.”

I’d like to do something very similar to your lesson – but i dont want to send multiple emails, just a single email sent (even by pushing a button) that only sends an email with data from the selected row, or the last row.

Jesper J
Jesper J
7 years ago

Hello

I hope someone in here can help me.
I need a script that can send an emails to 2 specific email adresses.
the mail must contain the latest entry to a google sheet (comming from a google form).
So that whenever someone makes an entry in forms, which sends the data to the sheet, and the script then sends an email with only the latest data.

Hope to hear from you guys 🙂

CB
CB
7 years ago

Hi Ranjith,

Are you able to send all of the sheets content to an email?

Cheers,
Colin

CB
CB
7 years ago
Reply to  Ranjith kumar

I am thinking of using the sheet as a type of order form if possible.

The user receives the link, populates the sheet and is able to send directly to me by the click of a button.

May not be possible, but just brainstorming ideas.

jitesh
jitesh
7 years ago

Hey, This is lovely script. I tested and it worked the test code.

I have a question.

If i need to send an email something like this:

Hi name,

first line

second line

third line

regards
my name

in the above template, you can see there is auto field(merge name field) and after every line there is a [enter] a line break, how to do that?

jitesh
jitesh
7 years ago

Hello ranjith,

along with above question in above comment, i have more three questions

I have over 5000 list of rows in my google sheet. Each row with one email address. Now with your given code, how i can format the line breaks etc.. and how to manage that i paste your given or helped code, save it, press run and close the sheet/switch off computer.. will this code still work to keep sending mails.
How to manage that in one hour, maximum of 20 emails goes… and the mails keeps on going, 20 an hour, till the time total 5000 records not mailed.
How to manage that from email should me”my personal domain email and not gmail”

Jitesh
Jitesh
7 years ago
Reply to  Ranjith kumar

its okay for 100 emails.. but how to do that.

how can i restrict in this case to 5emails per hour, non stop until the whole data is been emailed.?

Jitesh
Jitesh
7 years ago
Reply to  Ranjith kumar

So is there any script with you which can help to put it one for all.. trigger for an hour .. and helper column/

Jitesh
Jitesh
7 years ago

bro,

code is fine and i will either learn or try to hire someone..

but can you kindly help with, what is hourly trigger. how to?

Gus
Gus
7 years ago

Hi there!

Very interesting stuff! This got me thinking, if I wanted to make a script that copies the “to”, “subject” and “body” components from cells, how would I go about that?

Specifically, I have a report sheet containing data that is to be emailed each night.

It would look something like this:

The title is generated through a function in, lets say, A1, the addresses are contained in A2 and the body contained in cells A3:B8. Is there a way to copy this data into their respective fields in the email? So far, I haven’t had much luck.

Many thanks in any case!

Gus
Gus
7 years ago
Reply to  Ranjith kumar

Thanks so much for the reply!

The practical use example is rather similar, you’re right, except the data arrangement looks pretty different in my case. And, admittedly, my rudimentary coding skills are not helping me at all at this point.

http://imgur.com/sVXTmRr

This shows the data arrangement. The body is B30:C48, the subject is in E34 and the addresses are all listed in E37. And there’s where my headache began, as I can’t figure out how to rework the code in your post to fit the data and can’t actually restructure the sheet to fit the code as other sections can’t be moved.

Sorry for the inconvenience, but any help would be greatly appreciated.

jitesh
jitesh
7 years ago

Hello,

In this line of your code:
MailApp.sendEmail(row[1], “Custom mail”, “Hello ” + name + “, This is an email report of your score. Your score is ” + score);

While sending an email, is there any option where the receiver of my email can see the mail sender’s name, i.e. my name ?

The Lusk
The Lusk
7 years ago

Awesome sauce! Is there a way to have the script only look at one of my sheets (tabs) instead of all of them? Also, how could I trigger it to run based on cell contents? For example, if column BG is set to “Completed” then this script runs, but not until then.

Cassie
Cassie
7 years ago

Thank you for this….your explanation was much easier than anyone else’s. Can you tell me if there is any way to have the “comment” information in an individual cell within Google Sheets be emailed instead of the info just typed in the actual cell?

We just want to be able to email the actual comment information that is linked to each individual cell.

Adam
Adam
7 years ago

Can we send automatic email to a person once a certain cell is change? without clicking run on the scripts? For example the status was changed from pending to completed, an email will be sent to the person that their order was completed. Something like that. Hope to hear from you soon.

Mj
Mj
7 years ago

Hello,

I would like to send out an HTML email once a certain cell value changes. And the email should be customised based on the values in the sheet columns. Could you tell me how I go about doing this?

Aktar
Aktar
7 years ago

Hello Ranjith Kumar,
I`ve seen your codes, very good. I have a question for you. I have a Google sheet, which gets new data from Appsheet ( app making program). The sheet has 8 collums. Im wondering if its possible to make a script that sends each new entry (1 row 1-8) on email to a specific email adress containing the information which is in those 8 collums.

Collum example : Date , Adresse, Apartment number, Number of keys, Name of janitor

Then the data comes into the sheet:

20.01.2017 , St.pauls gate 32, 514, 1, Alex

Thank you very much in advance, would really appriciate if you would help me 🙂

Have an awesome day!

Richard Stevenson
Richard Stevenson
7 years ago

Hi Ranjith, loved your original custom emails tutorial!
I teach at an international school and I want to be able to send emails to parents if their child gets below a certain score. So, it would be like your original example but the email is only sent depending on a condition. For example my scores are all out of a maximum 8 but I only want to send the email if the score is <3. I have tried adding in an IF condition but emails are still sent to everyone! Any idea how this could be done? I could senf you a screenshot of the code I have written?
Thanks a lot.
Best wishes
Richard

Richard Stevenson
Richard Stevenson
7 years ago
Reply to  Ranjith kumar

Hi Ranjith, here is the link to my code: https://bin.codingislove.com/yiyucicesa.js
Thank you.
Best wishes
Richard

Richard Stevenson
Richard Stevenson
7 years ago
Reply to  Ranjith kumar

Ok great!! I just did it as well and posted! Will look through your code and check against mine. This is my first time doing this. Thanks very much for your extremely quick reply, I cannot believe you did this so fast after I posted!!!
All the best
Richard

Richard Stevenson
Richard Stevenson
7 years ago
Reply to  Ranjith kumar

Solved it myself Ranjith!! Please see here:
https://bin.codingislove.com/owicebolij.js

All the best
Richard

sindhu
sindhu
7 years ago

I want to send e-mail via google spreadsheets using edit box in the sheet and after pressing submit button the content should come in my mail. I am able to receive mail but I am not able to receive the content that is entered in the edit box.The data entered in the edit box will be dynamic but the recipient will be same. can you please help me out. Thanks in advance.

faan
faan
7 years ago

Hi Kumar.
great stuff. thanks.
Trying to use your getLastRow() suggestion… but I get an INVALID EMAIL – UNDEFINED error message when running the below code.
Could please have a look?

function my_dynamic_CustomEmail() {

// new code for DYNAMIC range
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();
var lastCell = sheet.getRange(lastRow, lastColumn);

Logger.log(lastCell.getValue());
// end of new code DYNAMIC range

var range = sheet.getRange(lastRow,lastColumn);
var UserData = range.getValues();
for (i in UserData) {
var row = UserData[i];
var name = row[0];
var email = row[1];
var score = row[2];
MailApp.sendEmail(row[1], “Custom mail”, “Hello ” + name + “, This is an email report of your score. Your score is ” + score);
}
}

fana
fana
7 years ago
Reply to  Ranjith kumar

txs a lot 🙂

Mike
Mike
1 year ago
Reply to  fana

Ranjith,
Could you provide this code, the link has expired and I could use exactly what fann needed.

VISHWAS DOLE
VISHWAS DOLE
7 years ago

How to send e-mail to various people with different doc for example having a housing society of 400 flats and I want to send maintenance bill by e-mail to each flat owner having a unique e-mail address,

vishwas dole
vishwas dole
7 years ago
Reply to  Ranjith kumar

Thank you

Stas
Stas
5 years ago
Reply to  Ranjith kumar

Ranjith, if you already have written the post you announced in your reply to VISHWAS DOLE, could you please post a link here?

Greg
Greg
6 years ago

Hi Ranjith,
Thanks for this post. I’m wondering how it would be possible to auto-trigger an email be sent when a specified date in a cell gets within 7 days from todays date? My sheet has due dates in a specific column and I would like email reminders to be sent to various individuals (whoever is assigned to that due date) when these due dates fall within 7 days. For the body of the email, I would like to gather data from specific cells within the same row as the corresponding due date. Thanks and I appreciate any guidance you are able to provide.

Greg
Greg
6 years ago
Reply to  Ranjith kumar

I was testing this on a range of cells and was able to get the email to send correctly, but I already have a global OnEdit auto-sort script running on my sheet and using OnEdit for the (below) script conflicts and makes them both inert.

I have (or tired to have) it set to pull the client’s name into the email from the column 2 to the left of the targeted/edited cell. The email is also supposed to pull in the date mentioned in the target cell, but for some reason the emails are displaying the date as 1 day prior to the date in the cell.

Thanks in advance for your help!

function onEdit(){
var sh = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var editedCell = sh.getActiveRange().getColumnIndex();
var range = sh.getRange(“AF3:AF15”);

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 12; // First row of data to process
var startColumn = 45;
var numRows = 1; // Number of rows to process
var numColumns = 1;
// Fetch the range of cells A2:B3
var dataRange = sheet.getRange(12, 45, 1, 1)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var clientname = range.offset(0, -2)
.getValue();
var duedate = range
.getValue();
var row = data[i];
var emailAddress = row[0];
var message = “Insurance Review Form due for ” + clientname + ” on ” + duedate + “.” + ‘\r’ + ‘\r’ + “If needed, a PDF version of the form can be found here: https://www.dropbox.com/s/rlm3ayvjgcp5hsu/MAP%20Concurrent%20Review%20Form.pdf?dl=0“;
var subject = “Insurance Review Due for ” + clientname;
MailApp.sendEmail(emailAddress, subject, message);
}
}

Jen
Jen
6 years ago

In the body portion of the script, could you instead call a Gmail template that’s already been created? So instead of the “score” value or the email body, you’d call + template +.

Manuel
Manuel
6 years ago

Hello,

Is there any example of how to send an email when a cell value change?

Thank you.

Jenny
Jenny
6 years ago

Hi-

I have a Google Form which the Responses go into Google Sheets. I have been trying for days to get a formula or script to work. We have 6 locations and one of the answers on the form asks which clinic location they want to attend. So I want to have the response emails go to separate people based on the location answer (which is in column D). I have used the following formula with it and no luck:
I have also tried a form emailer script with this formula but it kept sending emails, I had to remove it after getting 376 emails.

=IF($D2=”Normal, IL”, “[email protected]”,IF($D2=”Springfield, IL”, “[email protected]”,IF($D2=”Lombard, IL”, “[email protected]”,IF($D2=”Champaign, IL”, “[email protected]”,IF($D2=”Peoria, IL”,”[email protected]”,IF($D2=”Washington, IL”,”[email protected]”,IF($D2=”Dallas, TX”,”[email protected]”)))))))

Could you please help? Thank you! I am not very good with scripts/formulas yet. Learning 🙂

Linda
Linda
6 years ago

I cut and pasted all of this code, but I have an error message on line 10. It says:
Missing ) after argument list. (line 10, file “Code”)
Seems to be a parentheses missing from someplace on that line, but I don’t know enough to understand the error message or figure out where I need to place it.
Ideas?

Linda
Linda
6 years ago
Reply to  Ranjith kumar

My actual error was in line 9, forgot to change the word score to my own column heading. But now it says the app is not verified by Google, and that it is unsafe to run. So I’m stuck again! Says you need to have your app approved by Google in order to prevent the warning screen from popping up.

Linda
Linda
6 years ago

Sorry, I think it is calling my coding an “app”, so I ignored it and everything worked as it should! Thanks! My only question now is how to put a period at the end of the last sentence. I’ve tried a couple of things, but it is rejecting my attempts!

Linda
Linda
6 years ago
Reply to  Ranjith kumar

That’s the fix, thank you! I was close!

Matthew
Matthew
6 years ago

Hi Thank you for your help! Reading your article, I was able to get my mail app working.

However, even though I was able to get it to work – I was wondering if you could explain the variables to me and what they do so I actually understand what is going on.

This code:
var lastRow = sheet.getLastRow();
var range = sheet.getRange(2, 1, lastRow-1,7);
Logger.log(range.getA1Notation());

I do not understand the sheet.getRange(2,1) <– what does that reference?

What is the Logger.log line?

Thanks in advance!

Jason Prichard
Jason Prichard
6 years ago

This is great! The code works perfectly. My need is quite simple. I just need a variety of buttons (just a few with peoples names on them) and when pressed, a form email (doesn’t ever need to change, just alerts that something in the database has changed). Your code is perfect for this I just need to know how to associate this script with a button.
Thanks in advance!

Josh
Josh
6 years ago

I am new to this…
I am looking for away to automatically send an email if a cell is edited. The email address is also on the sheet. Any help reviewing this would be great.

Thanks!

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Requests" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 8 ) { //Any changes within this column set the date below and the email on the 2nd call
      var sched = r.offset(0, 0);//offset (row,column) 
      var email = r.offset(0,-1)
      var name = r.offset(0,-2)
GmailApp.sendEmail(email,name,sched)

}
}
}
Arup Mallick
Arup Mallick
6 years ago

Hi Ranjith kumar,

I have a column called “Assigned To” which is consist of drop down list with 4 names. If I want to send an automatic mail with some text when I will select a name from the list and the mail should go to this person particularly which have been selected fro the list. Is it possible? One more thing that if i will select a name from the 8 th number row randomly instead of serially, then this mail will send or not?

Please help me. This is very urgent for me.

Looking forward to your inputs.

Cheers,
Arup

Arup Mallick
Arup Mallick
6 years ago
Reply to  Ranjith kumar

Hi Ranjith,
Thanks for your concern, but still I am getting an error message when I will run this script.

TypeError: Cannot read property “range” from undefined. (line 2, file “Code”)

It would be better if you will do this sheet from your end and send me a video clip of that. frankly speaking i am not so good in coding sector, so can you please do this for me?

If you need my data, then please let me know. I will share with you.

Eagerly waiting for your reply.

Thanks,
Arup

Sarah
Sarah
6 years ago

Hi,
thanks for the detailed explanations!
i am trying to use the function you have mentioned,
var dataRange = sheet.getRange(2, 1, lastRow-1,3);
but get the constant reply from the debugger:
“Cannot find method getRange(number,number,number,number)”
for some reason i can not use 4 numbers in the getRange function.
Any idea why this is happens?
thanks alot!

Sarah
Sarah
6 years ago
Reply to  Ranjith kumar

yes of course 🙂 i can see the sheet ID when in the debug mode. I can also run any sheet.getRange(A2:D3); function, just not the one with 4 numbers in parameters -not even sheet.getRange (1,2,3,4);
it just doesn’t work for some reason

Sheila
Sheila
6 years ago

Hi,
This is a great and easy to understand example. I have my script working to send email, but it sends me all the emails for different dates. What I need is for it to only end the emails on dates stored in a column and save the other rows for later dates. Can you help?

Juan Prinsloo
Juan Prinsloo
6 years ago

Hi Ranjith. My name is Juan and from South Africa. New to scripting using Google sheets… Is it possible to add email tracking (when a message is read or opened, without the recipient needing to take any action) as part of the script to send emails from Google Sheets? If so, can you share some code, please!

Dinesh P
Dinesh P
6 years ago

I am a beginner and no idea about coding, i got a error “Missing ; before statement. (line 2, file “Code”)” when i tried to run the code.

Dinesh P
Dinesh P
6 years ago
Reply to  Ranjith kumar

Hi Ranjith,
Thanks a lot, I have done it, i had earlier copied the code from PDF document,
Though the mails were sent successfully there was an alert in the sheet ” Failed to send email: no recipient”
Thanks a lot once again for your help in learning coding basics.

andy
andy
6 years ago

HI- great post indeed. Im trying to get goog sheets to fire off emails on a test sheet like at this link below
https://docs.google.com/spreadsheets/d/1QKh6OJ1JV401tKh7QggoWpNz4Sfi0ppjHXwSPF8kf4E/edit?usp=sharing
So 2 kinds of alerts

#1 program sheet to send email to [email protected] w Mesg ” Trigger” when Col C value is lower than Col D or Higher than Col E
#2 also email [email protected] with mesg “Trigger reachd” when Col C value is lower (Col I Col F) or Lower than (Col H Col G)
Could you suggest what script will do this

Jonathan
Jonathan
6 years ago

Hi there, great tutorial. I am trying this out but having difficulty in the range of adding more than one ‘score’. e.g. I am needing to send a number of scores per email address. Also when I click send emails, it only sends me the last email in my list and not the other 23 before it. Any ideas?

Jonathan
Jonathan
6 years ago
Reply to  Jonathan
function CustomEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A2:K24");
var UserData = range.getValues();
for (i in UserData) {
var row = UserData[i];
var Name = row[0];
var Email = row[1];
var Freestyle = row[2];
var Score = row[3];
var Backstroke = row[4];
var Score2 = row[5];
var Breaststroke = row[6];
var Score3 = row[7];
var Overall = row[8];
var Grade = row[9];
MailApp.sendEmail(row[1], "Swimming Scores/Grades", "Hello " + Name + ", This is an email update of your current swimming scores. Your score is " "Freestyle Time" + Freestyle + "Freestyle Score out of 10" + Score + "Back Stroke Time" + Backstroke + "Back Stroke Score out of 10" + Score2 + "Breast Stroke Time" +Breaststroke + "Breast Stroke Score out of 10" + Score3 + "Overall Average Score" + Overall + "Overall Grade" + Grade);
}
}
Emilie Sorel
Emilie Sorel
5 years ago

Hi!
I sent a message earlier but I think it did not go through. I actually have a script that grabs an email address and some data from the spreadsheet and sends an email. Everything goes through, even the email but I still have this error message:
“Failed to send email: no recipient”

I think the problem is linked to how my range is defined but I have tried a bunch of things and I am quite clueless at this point.

Can you help me out?
Here is my code: https://bin.codingislove.com/mokudihodo

Thank you!

Jamie
Jamie
5 years ago

Hi Ranjith,
Thanks for your article, it is very informative. Great stuff. I have a script running but need extra functionality. I have a sequence of 3 possible emails to send out. What I would like is if I get no reply to the first email is to send out the second email in the sequence with the first email added to the footer.

Should I get no response to the second email to then send out the third mail with both the first and second emails added as a footer.

Is something like this possible?

Jamie
Jamie
5 years ago
Reply to  Ranjith kumar

Hi Ranjith,
Yes, I would check for responses manually in Gmail. When I receive a response I then reply and begin negotiations so no more automation is required for those situations

Jamie Cartwright
Jamie Cartwright
5 years ago
Reply to  Jamie

Hi Ranjith,
Just following up on my last message. Is there any way to do this?

Jamie
Jamie
5 years ago

Hi Ranjith,
Yes, I would check for responses manually in Gmail. When I receive a response I then reply and begin negotiations so no more automation is required for those situations

Julian
Julian
5 years ago

Hi Ranjith,

Great post and thanks for the support you’re giving to the community.

Let’s consider your exact same example but changing the Score variable to Deadline (which is a date).
I would like to incorporate an If statement so that If the Deadline has passed/or is upcoming an email is sent.

The if statement would be something like:

if (Deadline[i] – TODAY()>0) {
MailApp.sendEmail(row[1], “Custom mail”, “Hello ” + name + “, This is an email report of your deadline. Your deadline was on ” + Deadline);
}
else if (Deadline[i] – TODAY()=5) {
MailApp.sendEmail(row[1], “Custom mail”, “Hello ” + name + “, This is an email report of your upcoming deadline. Your deadline will be due on ” + Deadline);
}

else {
}

Note* the last else is to denote that for all other scenarios, no action should be taken.

Thank you for your help!!
Julian

Matt
Matt
4 years ago

Nice little article on how to send an email, thanks for that 😀

I prefer to use a little addon which is much easier and has a lot of features built in:
https://chrome.google.com/webstore/detail/schedule-send-emails-in-s/jfmcgoflikkgpilaaencmobegkapljda

Tell me what you think 🙂

Larry
Larry
4 years ago

This is great stuff!!!!
I am stuck though. I have a very simple spreadsheet that has conditional formatting based on dates. When the current date is in the date range listed on the sheet, the row will turn green and change the cell in column F of that row from blank to the word “CURRENT”. I am trying to figure out the following:

Have the script go through row by row until it finds the word “CURRENT” in column F. Then it should send an email to an email set as a var in the script with the all the values of that row. I can’t seem to figure out how to do this. Can you help?

Also the other issue is how to trigger it. I would like to set a trigger that checks for example every 8 hours to see if the word “CURRENT” in column F has moved due to conditional formatting and if it has, have this be the trigger that fires the email only once. It would not fire again until the word “CURRENT” moves again. The trick here is can this be done with a trigger without the need to detect a user changing a cell? What I mean is, the conditional formatting logic I created is what makes a cell in column F show the word “CURRENT” or not show it. I would like the trigger to do this without the need to even open the spreadsheet. I am not sure thats possible, is it?
Really would appreciate the help!
Thanks!

https://ibb.co/1KpHQ4r

Selvaraj Ramsamy
Selvaraj Ramsamy
3 years ago

Hi, Can anyone help me to complete the below script for sending emails and reminders from google sheet?
1. When the status comes Open in column ‘O’ the mail should go automatically to the 1st level email ID’s which is available in the column ‘U’ With EMAIL_SENT entry in column ‘V’ automatically to prevent duplicates emails.
2. When the Open Days in Column ‘T’ comes more than 4 days then the reminder mail should go automatically to 2nd level email ID’s which is available in the column ‘W’ With EMAIL_SENT entry in column ‘X‘ automatically to prevent duplicates emails.
 function sendEmail() {
 var Timestamp = 0;
 var ASM = 1;
 var Region = 2;
 var DealerName = 3;
 var Topics = 4;
 var DescriptionOfIssue = 5;
 var DateOfIssue = 6;
 var AssistTicketNo = 7;
 var MachineSerialNo = 8;
 var MachineStatus = 9;
 var WarrantyStatus = 10;
 var ASMRemarks = 11;
 var SupportRequiredFromManitou = 12;
 var EmailAddress = 13;
 var FirstLevelEmail = 20;
 var SecondLevelEmail = 21;
 var Open = 14;
 var EMAIL_SENT = 22;
 var EMAIL_SENT = “EMAIL_SENT”

 var emailTemp = HtmlService.createTemplateFromFile(“Email”);  
 var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“After sales Open/Close issues”);
 var data = ws.getRange(“A2:Z” + ws.getLastRow()).getValues();  
 if (status == ‘open’);
  data.forEach(function(row){
  emailTemp.Timestamp = row[Timestamp];
  emailTemp.ASM = row[ASM];
  emailTemp.Region = row[Region];
  emailTemp.DealerName = row[DealerName];
  emailTemp.Topics = row[Topics];
  emailTemp.DescriptionOfIssue = row[DescriptionOfIssue];
  emailTemp.DateOfIssue = row[DateOfIssue];
  emailTemp.AssistTicketNo = row[AssistTicketNo];
  emailTemp.MachineSerialNo = row[MachineSerialNo];
  emailTemp.MachineStatus = row[MachineStatus];
  emailTemp.WarrantyStatus = row[WarrantyStatus];
  emailTemp.ASMRemarks = row[ASMRemarks];
  emailTemp.SupportRequiredFromManitou = row[SupportRequiredFromManitou];
  emailTemp.EmailAddress = row[EmailAddress];
  emailTemp.FirstLevelEmail = row[FirstLevelEmail];

  var htmlMessage = emailTemp.evaluate().getContent();
    
  GmailApp.sendEmail(row[FirstLevelEmail],”Open Issue Notification”,”Your email doesn’t support HTML”,{name: “ADMIN TEAM”, htmlBody: htmlMessage});
  sheet.getRange(startRow + i, 22).setValue(EMAIL_SENT);
  SpreadsheetApp.flush();
});
}
FYI… The HTML script is done. You can find the google sheet by clicking this link—>( https://docs.google.com/spreadsheets/d/1tva9ly_xgWUxFyeEgjMv5Fv8e4Yxh2AXli51i_7qpYk/edit?usp=sharing)
Regards, Selva

Tina Asiamah
Tina Asiamah
3 years ago

Hello Please Can Some Be Of Help?
I want to write a script which will send an automatic email to my listed email addresses any time a column is entered (not when a subject is blank) 

Toni
Toni
2 years ago

Is there a way to send emails from within google sheets but from my work email address as opposed to my gmail? My default in gmail is to send emails from my work address, but it’s not working from google sheets.

Glen
Glen
2 years ago

Could probably update this page to incude template literals?

datainnutshell
3 months ago

Nice explanation and in case anyone want to have a ready made mass email sender based on google sheet you can also refer – https://datainnutshell.com/google-app-script/how-to-trigger-emails-from-check-box-ticks-from-google-sheets-and-google-app-script/