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