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
- Open any existing google spreadsheet or create one from sheets.google.com
-
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");
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.
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.
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.
- Flutter lookup failed in @fields error (solved) - July 14, 2023
- Free open source alternative to Notion along with AI - July 13, 2023
- Threads API for developers for programmatic access - July 12, 2023
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.
Hi Adam, you can do that using getActiveRange and getRow methods.
Here’s a sample code, If you want to send a single email with data in selected row – http://pastebin.com/DsqcMA3m
Hope that helps, Let me know!
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 🙂
Hi Jesper, Here’s what you need – Create Auto responding google form with 2 lines of code
Hi Ranjith,
Are you able to send all of the sheets content to an email?
Cheers,
Colin
Did you mean the whole sheet? It depends on the character limit of email. If you want to send whole sheet then why not just share a link to the sheet!
Can I know whats your use case?
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.
Did you have a look at https://codingislove.com/auto-responding-google-form-2-lines-code/
You can create an order form in google forms, send it to user. When he fills in the form you will get an email of new order and user will get an order success email. Detailed explanation in the above link!
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?
Hi Jitesh,
You can just use a line break character “\n” or send HTML email instead of plain text email.
Example here – https://codingislove.com/auto-responding-google-form-2-lines-code/
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”
Firstly, Apps script has a limit on the number of recipients per day. Currently, It is 100. So you cannot send emails to more than 100 unique Ids. check all the limits here – https://developers.google.com/apps-script/guides/services/quotas#current_quotas
And MailApp from Apps script is not the right solution for such huge data. You should try to use a dedicated Email service with API which supports bulk emails and call it using UrlFetchApp. Few Email services with API are – SendGrid, SparkPost, Mailgun.
These services support HTML emails. You can use <br> tag to add a link break.
hope that helps.
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.?
You have to use time-driven triggers to run a script every hour. You should also keep track of last sent row using a helper column in your google sheet. Read more about time triggers here – https://developers.google.com/apps-script/guides/triggers/installable
So is there any script with you which can help to put it one for all.. trigger for an hour .. and helper column/
Here’s an example which uses timed triggers – https://codingislove.com/alexa-rank-checker-in-google-sheets/
You have to understand the code and modify it to match your use case or hire a freelancer to do it – Hire a Google Apps script freelancer
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?
It’s just code which is triggered by time. One hour in this case. You should write code which sends 5 emails from 5 rows in the sheet and set a trigger for every hour. Add another column in your sheet named email status and code should set its value to “sent” when email is sent to id in a row so that you can continue sending emails from next Id when code is executed next time. Read this article completely to understand timed triggers – https://codingislove.com/alexa-rank-checker-in-google-sheets/
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!
Hi Gus,
Didn’t you see the second example (practical use case) in the post? It’s almost the same as your use case. It takes “TO” address and other details from cells in the sheet.
You can set a timed trigger to send email every night. An example of timed trigger is here – https://codingislove.com/alexa-rank-checker-in-google-sheets/
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.
Can you please post the same on our forum https://forum.codingislove.com/category/8/excel-ninjas along with the existing code that you’ve till now. It’s easier to share code and communicate there.
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 ?
Email will be sent from your own gmail address. So the receiver can see your name and email. If you want to add your name in body also then you can add it as regular text.
MailApp.sendEmail(row[1], "Custom mail", "Hello " + name + ", This is an email report of your score. Your score is " + score + "\n" + "This report is sent by Jitesh");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.
Hi Lusk, You can use
On edittrigger for your use case. It will be triggered whenever a cell is edited or changed in the spreadsheet. This trigger also gives a range object which can be used to find out which cell has been changed and run script based on that. Write a function and setup On Edit trigger for that function in the script editor. Here’s some sample code – https://bin.codingislove.com/dofotalowu.goThank 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.
Hello Cassie,
Comments cannot be directly accessed using apps script but Note of a cell can be accessed. Comment and Note are pretty similar except comments can be replied and can be used as a small discussion board. Here’s a topic about accessing comments and notes in google support forum – https://productforums.google.com/forum/#!topic/docs/rf6r-p7YSKo
If you want to get Note then you can do it by using getNote method.
var comment = SpreadsheetApp.getActiveSheet().getRange("A1").getNote();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.
Hello Adam, Have a look at this comment – https://codingislove.com/send-email-google-sheets/#comment-670
It’s pretty much the same use case.
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?
Same query has been requested by few others in the comments above.
You can use On edit trigger for your use case. It will be triggered whenever a cell is edited or changed in the spreadsheet. This trigger also gives a range object which can be used to find out which cell has been changed and run script based on that. Write a function and setup On Edit trigger for that function in the script editor. Here’s some sample code – https://bin.codingislove.com/dofotalowu
I will be writing a detailed tutorial on this topic soon. Meanwhile, try it out using above hint.
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!
Hello Aktar,
The current example in this post which sends emails to users with score is similar to your requirement. You just have to add few extra columns. But triggering email when new data is added should be done using “onChange” trigger. I’ll be writing about this soon. Meanwhile you have to try it out on your own by reading the documentation.
If you are using Appsheet then they have built-in email notification system. You actually don’t need any extra setup in google sheets. Read it here – Appsheet email notification example
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
Hi Richard, Please share your code by pasting it here – https://bin.codingislove.com
Hi Ranjith, here is the link to my code: https://bin.codingislove.com/yiyucicesa.js
Thank you.
Best wishes
Richard
Everything was fine with your code except there was an extra
Forloop inside If statement which was looping through all rows again and sending emails to everyone. I cleaned up the code a bit. Here’s the working code – https://bin.codingislove.com/fubamaduku.jsOk 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
Solved it myself Ranjith!! Please see here:
https://bin.codingislove.com/owicebolij.js
All the best
Richard
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.
Hi, Please share your existing code.
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);
}
}
Hi Faan, This should work – https://bin.codingislove.com/ohahosulig.js
We don’t need last cell and last column here. We only need the last row. The number of columns is always fixed and starting row is always 2 so we just need the last row to make the range dynamic. So whenever you add a new row, that row will also be added to the range. Try out this code!
If you want to make the columns also dynamic then it can be done using getLastColumn as you did but your getRange method should be modified. (Just replace 3 with getLastColumn result in the above code.)
txs a lot 🙂
Ranjith,
Could you provide this code, the link has expired and I could use exactly what fann needed.
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,
I’m writing a post on that. I’ll publish it soon!
Thank you
Ranjith, if you already have written the post you announced in your reply to VISHWAS DOLE, could you please post a link here?
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.
Hi Greg, You can do it using a timed trigger. It takes some time to write code for this. Please share your existing code.
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);
}
}
Hi Greg
Sorry, I’m replying after a long time. I did not see your reply. OnEdit trigger conflicts can be avoided by checking the range which was edited.
function onEdit(e){
var range = e.range;
//check If range is equal to your required cell here
}
Coming to your code, It is hard to debug without looking at the data in your sheet. Can you privately share access to this sheet to my account – [email protected]? I’ll have a look.
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 +.
Hi Jen,
When you say Gmail template, Is it a canned response? I’m not sure if you can pull canned responses or not but if you store the message as a draft then you can pull it using GmailApp and use it in the body of the email.
Hello,
Is there any example of how to send an email when a cell value change?
Thank you.
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 🙂
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?
Hi Linda, You might have missed something while copying. Also, Make sure to copy the data to sheet also as shown in the image.
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.
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!
Something like this should work
That’s the fix, thank you! I was close!
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!
Hi Matthew, The code you are looking at is for sending emails using a dynamic range, When a new row with name, email and score is added then that row should also be used for sending email. getLastRow method gets the last used row in the sheet so we use it to set range till that row.
Syntax for getRange is like this –
getRange(startingRow, startingColumn, numberOfRows, numberOfColumns)sosheet.get range(2, 1, lastRow-1,3);means get range starting from 2nd row and 1st column. data is starting from 2nd row so the total number of rows in our dynamic range = lastRow – 1Finally, number of columns is 3 in my case because it has Name, Email and score columns. It may be different in your case according to your data. Logger.log was used just for logging the dynamic range that we got. You can see the logs using View > Logs which will have something like “A2:C4”
Hope that helps!
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!
Hi Jason, I’ve already explained how to associate a script with a button along with a screenshot. Check the code explanation section.
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) } } }Hi Josh, You have to check the edited range instead of active sheet and active range. It would be something like this –
function onEdit(e) { var editedRange = e.range; //Assuming that you want to send email when I2 cell is changed to Yes if (editedRange.getsheet().getName() == "Requests" && editedRange.getColumn() == 9 && editedRange.getRow() == 2) { if (editedRange.getValue() == 'Yes') { // send email here } } }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
Hi Arup, It is possible but you have to write some code using onEdit trigger. Read this comment – Send email when a cell is edited
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
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!
Hi Sarah, Did you set the sheet first?
That error occurs if
sheetis not a valid sheet object.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
That’s weird. In this case, I can’t tell what’s the issue without looking at the sheet. Give me access. I’ll have a look – ranjithkumar8352@gmail(dot)com
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?
Hi Sheila, Please share your code.
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!
Hi Juan, Unfortunately that is not possible
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.
Hi Dinesh, Make sure to copy the code correctly from the article. Hover on the code snippet above and click on view source icon and then copy code.
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.
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
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?
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); } }Hi Jonathan, There’s a small mistake in your code. Here’s the working code – https://bin.codingislove.com/jiwonemedo.js
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!
Hi Emilie, The problem is in this line –
var emailAdd = row[1];row[1]refers to 2nd column and not 1st column. So, Change it to row[0] or move your email to second column. Hope that helps!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?
Hi Jamie, How would you check if you have received the response or not? Is it manual?
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
Hi Ranjith,
Just following up on my last message. Is there any way to do this?
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
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
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 🙂
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
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
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)
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.
Could probably update this page to incude template literals?
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/