How To Send Emails From Google Sheets

Sending an Email is a relevantly easy task, as anyone with an active internet connection and the recipient’s email address can do that. Gmail even lets you send the same email to multiple recipients at once, which is truly a time saver. But things get difficult when you want to send different emails to different groups of people and that too at the same time.  Most people do that manually, which honestly, is really time-consuming. And that is because they don’t know any tool that can get it done automatically at once. However, if you’re one of those people, then you’ll be delighted to know that you can save a lot of your time by using Google Sheets.

Yes! Like Gmail, you can send the same email from Google Sheets to multiple recipients. But what makes it better, is that you can also assign different email for each and every recipient, to be sent at the same time. But how? Well, in this article, we are going to answer it all.

Along with that, we are also going to tell you how you can get a notification alert when someone makes changes in the shared spreadsheet. And if that’s what interests you more, then you can skip straight to automatically send email when Google Spreadsheet is updated.

Learn More: How To Lock Cells In Google Sheets

Send Email From Google Sheets

For this thing to work, we’re going take help of a code, which has to go in the Script Editor tool of the Google Spreadsheet. At first, it is safe to test this method before directly using it to send emails to actual recipients. To test the authenticity of this method, you can put in your secondary or maybe one of your friend’s email address in the recipient list. And if it works fine for you, then you can use it to send emails to actual recipients.

1. Go to Google Sheets and open a new spreadsheet. Now, divide the first four rows of the sheet into the Email address, Subject, Message, and Status. After that, fill the respective rows with relevant information as shown in the image below.

Note: Keep the Status row empty as it is used to show the delivery status of the Email.

creating four rows

2. Next, on the toolbar, click on Tools and select Script editor.

script editor

3. The editor will open up in a new tab with a pre-written code in the untitled project. Now, delete all the pre-written code so that you can paste the required code in the project.

4. You can get the script/code written by us below. Just copy it and paste it in the Script editor.

Send Emails From Spreadsheet Code

function onOpen() {

var ss = SpreadsheetApp.getActiveSpreadsheet(),
options = [{
name: “Send Mail”,
functionName: “sendEmails2”
}, ];
ss.addMenu(“Email Sender”, options);

}

var EMAIL_SENT = “EMAIL_SENT”;

function sendEmails2() {

var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // First row of data to process
var numRows = sheet.getLastRow() – 1; // Number of rows to process
// Fetch the range of cells
var dataRange = sheet.getRange(startRow, 1, numRows, 4)
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[0]; // First column
var subject = row[1]; // Second column
var message = row[2]; // Third column
var emailSent = row[3]; // Fourth column

if (emailSent != EMAIL_SENT) { // Prevents sending duplicates

MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 4).setValue(EMAIL_SENT);
// Make sure the cell is updated right away in case the script is interrupted
SpreadsheetApp.flush();

}

}

}

Note: The original source of this code is GitHub, however, we modified the code to make it more convenient. Because the original script/code didn’t let the user include the Subject row. So all your sent Emails would only have the predefined “New Email” subject, which is not convenient. Moreover, with the original code, you would have to make changes in the code if there were more than 2 emails on the list. But by modifying the code, we overcame these shortcomings.

5. If you’re done pasting the script into the editor, then press Ctrl+S to save the code.paste script in script editor

6. As soon as you do that, a pop up will appear asking for a name of the project. Give it a suitable name and click on OK.

edit script name

7. Next, on the toolbar, click on the Select function button and select sendsEmails2 option.

Note: If you see Myfunction button instead of the Select Function, then it means the project hasn’t been saved. Try saving it again to solve the problem.

send Email from Google Sheets

8. Now, click on the play or run button to compile the code. When you do that, a pop up saying ‘Authorisation required’ will appear. Click on ‘Review permissions.’

send Emails from Google spreadsheet

9. A new window will open up asking for your Gmail account from which you would like to send emails using Google Sheets. Just select the desired email to move forward.

10. After that, a warning message will show up. Select the Advanced option to reveal the Go to [Project name] (unsafe) option and then click on it.

Script warning

11. On the next page, scroll down and click on the Allow option to give the required permissions to the script.

Allow permission tab

Note: You have to provide this authorization only for the first time. Once you’re done with it, then you only have to click on the Play/Run button to send the e-mails.

12. Finally, the script will get compiled and the e-mails will be sent to all the email addresses at the same time.

13. Here, you will get the EMAIL_SENT status in the Status row for confirming that emails were sent successfully.

send emails using Google Sheets

With this, you have successfully sent emails from Google Sheets.

Send Email When Google Sheet Is Updated

If you work on a shared spreadsheet, then it is very important to know about all the changes made to it by others. To stay informed about all the updates done in the shared sheet, you can enable the notification alert for that. This will send an alert on your e-mail whenever someone changes anything on the sheet. So, if you want to get notified when the spreadsheet is updated, then you can follow these steps:

1. Go to the desired shared spreadsheet. Now, click on Tools and select Notification rules.

send Email when Google sheet is updated

2. After that, on the Set notification rules window, select the options according to your wish and click on the Save button.

set notification rules tab

You can set Notify me With… for daily alerts by choosing the Email-daily digest, or you can choose Email-right away, to get an instant notification on your Gmail account when someone updates the spreadsheet.

3. Now, you can add another notification rule or simply click on Done to complete the process.

done option

4. From now on, if someone updates the spreadsheet, then you will get an email on your Gmail account.

email alert when Google spreadsheet is updated

Now, you will always stay informed about any changes made in your Google spreadsheets.

Learn More: How To Create Drop Down List In Google Sheets

Conclusion

So, now you can send different emails to multiple people from Google Spreadsheets at the same time. It wasn’t that hard after all, was it? Additionally, with the Google Sheets notification alert, you can now get notified about any changes made in your shared spreadsheet. And we really hope that you were able to follow each and every step in this article without any difficulty. But still, if you have any confusion regarding any step, then we wouldn’t mind answering your query in the comments section.

LEAVE A REPLY

Please enter your comment!
Please enter your name here