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.

Author: Ranjith kumar

A CA student by education, self taught coder by passion, loves to explore new technologies and believes in learn by doing.

75 Thoughts

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

  2. 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 πŸ™‚

    1. 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?

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

  3. 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?

  4. 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”

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

      1. 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.?

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

  5. 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?

    1. 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/

  6. 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!

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

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

    1. 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");

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

    1. Hi Lusk, 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.go

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

    1. 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();

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

  11. 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?

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

  12. 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!

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

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

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

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

  15. 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);
    }
    }

    1. 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.)

  16. 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,

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

    1. Hi Greg, You can do it using a timed trigger. It takes some time to write code for this. Please share your existing code.

      1. 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);
        }
        }

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

  18. 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 +.

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

  19. 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 πŸ™‚

  20. 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?

    1. Hi Linda, You might have missed something while copying. Also, Make sure to copy the data to sheet also as shown in the image.

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

  21. 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!

    1. Something like this should work

      MailApp.sendEmail(row[1], "Custom mail", "Hello " + name + ", This is an email report of your score. Your score is " + score + ".");
      
  22. 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!

    1. 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) so sheet.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 – 1

      Finally, 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!

  23. 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!

    1. Hi Jason, I’ve already explained how to associate a script with a button along with a screenshot. Check the code explanation section.

  24. 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)
    
    }
    }
    }
    
    1. 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
      }
      }
      }
      
  25. 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

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

  26. 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!

    1. Hi Sarah, Did you set the sheet first?

      var sheet = SpreadsheetApp.getActiveSheet();
      var range = sheet.getRange(2, 1, lastRow-1,3);
      

      That error occurs if sheet is not a valid sheet object.

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

Leave a Reply

Your email address will not be published. Required fields are marked *