Auto email from Google sheets: An easy step-by-step guide

Suppose you have a collection of email addresses in a Google Sheets spreadsheet. How would you send them all emails from with Sheets? You can in fact, send automated emails from with Sheets.

Google Sheets is a useful online spreadsheet that runs in a web browser and it can be accessed on a Windows PC, Linux PC or an Apple Mac. If you have a spreadsheet that contains a list of email addresses, you could automatically send emails to everyone. This makes it useful for customer lists, client lists, suppliers, contacts, club members and so on.

A Google Sheet could have only email details, but it could just as easily contain a lot of other data and have a range of cells elsewhere with the email addresses, but here we will keep it simple. If you want to mass email from within Google Sheets, here is how to do it.

This method will work on a Windows PC or an Apple Mac, but I recommend using Google Chrome as the browser whichever computer you use. I ran into a problem with Safari on the Mac and had to switch to Chrome.

1 Create a Google Sheets spreadsheet

Google Sheets is accessed on drive.google.com in a browser and when sending emails it will use your Gmail account. Is there anyone left in the world that doesn’t have a Gmail account? Click the + New button in the top left corner and then select Google Sheets.

2 Create emails in Google sheets

Add email addresses to the spreadsheet in one column, the email subject in the next column and the email body in the next. It makes the script we will use much simpler if this is placed in the top left corner and there is nothing else in the sheet, but it is certainly possible to place these practically anywhere in the sheet and have a lot of other data and charts in it.

3 Open the script editor

A script is used to mass email from Google Sheets, so open the Tools menu and select Script editor.

4 Create the email script

Delete whatever is there (an empty default function) and replace it with this. Rather than type it in, which will probably lead to typing slips, copy and paste this code in:

function MyMailSender() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
MailApp.sendEmail(data[i][0], data[i][1], data[i][2]);
}
}

Here is an explanation of each line:

1. Contains the name of the function and it can be called anything. Use the name here, MyMailSender or use one of your own (nospaces).
2. Create a variable called sheet that contains the current active sheet.
3. Create a variable called data that contains the range of values – basically the part of the sheet containing our emails. It is useful when the sheet contains only emails as ours does. It basically finds the start and end rows and columns.
4. The for loop runs through each row from first to last (data.length). Something to watch out for here is that spreadsheet rows and columns are counted from 0, so the top left corner is 0,0. The first row with the headings is row 0 and the first email is at row 1. This is why the for loop starts at 1 (var i=1;).
5. MailApp.SendEmail(address, subject, body) is a built in function that sends the emails. data [ i ] [ 0 ] is the address in row i, column 1. data [ i ] [ 1 ] is the subject in row i, column 1. data [ i ] [ 2 ] is the email body in row i, column 2 and so on.

5 Run now or on a schedule

There is a Run menu and a Run button in the toolbar, so you can run this right now and it will send the emails in the sheet. However, let’s go a step further and send the emails at a scheduled time and date automatically. Open the Edit menu and select Current project’s triggers.

6 Add a script trigger

We have a script and we can run it manually by clicking Run, but how do we run it automatically? Using a trigger. This is an event of some kind and there are many different types. When the event occurs, the script is run. Click the Add Trigger button.

7 Define the script trigger

A panel appears with several options, but these are the only ones you need to change. Set Select event source to Time-driven. Set Select type of time based trigger to Specific date and time. Enter a date and time in the format shown (YYYY-MM-DD HH:MM). The script will run automatically on this day and at this time and it will send all those emails in the Google Sheet.

8 Give it permission

There are actually several steps here, not just one. The script accesses your Google Sheet and uses your Gmail to send emails and you must click through several security warnings. Select the Google account to use, Click Show Advanced above, then click Go to… whatever you called the script.

The reason for all the security pop-ups is that you could have copied some long and complicated script off the web that does something bad with your account. It might not be obvious from the script if you are not a programmer. My script is very short and its function is obvious, so it is safe. Click through the warnings and security messages to enable this trigger.

Once the script and trigger have been created, that’s it. On the day and time, the emails in the Google sheet will be sent. You do not need to open the sheet again, unless you want to change something of course.

You can add more email addresses, change the subject and body messages, and it will still work. The trigger is set to fire once only, but you can create other triggers, such as weekly or monthly.

One word of warning though. It is probably not a good idea to add thousands of emails to a Google Sheet and run this script. My guess is that Google would see this as a spam attempt and it might lock your account for spamming. Only use it for small numbers of emails.