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("ranjithkumar8352@gmail.com", "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.

Get notified when there's a new post.

Need some help? Post your questions on our forum

Author: Ranjith kumar

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

44 Thoughts

  1. I was suggested this website by my cousin. I am not sure whether
    this post is written by him as nobody ese know such detailed about my difficulty.
    You are amazing! Thanks!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

      1. function sendEmails() {
        var sheet = SpreadsheetApp.getActiveSheet();
        var startRow = 11; // First row of data to process
        var numRows = 18; // Number of rows to process
        // Fetch the range of cells A2:B3
        var dataRange = sheet.getRange(startRow, 1, numRows, 18)
        // Fetch values for each row in the Range.
        var data = dataRange.getValues();
        for (i in data)
        {
        var row = data[i];
        //var emailAddress = row[0];
        var message = row[1];
        var subject = “Sending emails from a Spreadsheet”;
        MailApp.sendEmail(email_address, subject, message);
        }
        }

Leave a Reply

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