How to send emails from Google Sheets automatically

When I ran my own business on Google Sheets, the first thing I automated was email. Confirmations, reminders, balance notices: all of them came from a row in a sheet, and for a while I was copying addresses into Gmail one at a time. A 30-line script fixed that. Here's the version I'd write today, with the parts that usually go wrong called out.

The setup

Start with a sheet named Send with these columns in row 1:

Each row is one person. Leave Status blank for anyone who should get the email. The script fills in Status and Sent at as it goes, so you always have a record of who got what and when.

Open the script editor from Extensions > Apps Script, delete the sample code and paste this in.

The script

const SHEET_NAME = 'Send';
const SUBJECT = 'Your spot is confirmed';

function sendPendingEmails() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) return; // another run is already sending

  try {
    const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;

    const range = sheet.getRange(2, 1, lastRow - 1, 4);
    const rows = range.getValues();

    for (let i = 0; i < rows.length; i++) {
      const [name, email, status] = rows[i];
      if (!email || status === 'Sent') continue;

      if (MailApp.getRemainingDailyQuota() < 1) {
        console.log('Daily email quota used up. Stopping at row ' + (i + 2));
        break;
      }

      const firstName = String(name).split(' ')[0] || 'there';
      const body = `Hi ${firstName},\n\nYou're all set. We'll send a reminder the day before.\n\nThanks!`;
      const html = `<p>Hi ${firstName},</p><p>You're all set. We'll send a reminder the day before.</p><p>Thanks!</p>`;

      try {
        MailApp.sendEmail({ to: email, subject: SUBJECT, body: body, htmlBody: html });
        sheet.getRange(i + 2, 3, 1, 2).setValues([['Sent', new Date()]]);
      } catch (err) {
        sheet.getRange(i + 2, 3, 1, 2).setValues([['Error: ' + err.message, new Date()]]);
      }
      SpreadsheetApp.flush(); // write the status now, not at the end of the run
    }
  } finally {
    lock.releaseLock();
  }
}

Click Run once. Google will ask you to authorize the script; it needs permission to read the sheet and send email as you. After that, anyone with a blank Status gets the email and their row is marked Sent.

A few choices in there are deliberate:

• It reads all rows in one call. getValues() pulls the whole block at once. Reading cells one at a time is the most common reason email scripts get slow and hit the time limit.
• It writes the status right after each send. If the script stops halfway (quota, time limit, an error), the rows that already went out are marked, and the next run picks up with the rest.
• It sends both plain text and HTML. Most inboxes show the HTML. The plain text version is what shows in previews and in clients that block HTML.
• It checks the quota before each send and stops cleanly instead of failing partway through a message.

MailApp or GmailApp

Both can send. The difference is what else they can do and what they ask permission for.

MailApp only sends. When someone authorizes it, Google asks for permission to send email on their behalf and nothing more. For a script that sends confirmations, that's what I want.

GmailApp can send and also read, search, label and draft. Reach for it when you need to:

• Send from an alias you've already added in Gmail settings (GmailApp.sendEmail(to, subject, body, { from: 'office@yourdomain.com' })).
• Create drafts so a person can review them before they go out (GmailApp.createDraft(...)).
• Read replies or search for messages to log them back to the sheet.

The authorization screen for GmailApp asks for full access to the mailbox, which makes some clients nervous. If you don't need it, use MailApp.

Running it automatically

A script you have to run by hand isn't much better than sending by hand. There are two triggers I use for this.

On a schedule. Good for reminders and batch sends. In the script editor, open Triggers (the clock icon), add a trigger for sendPendingEmails, choose Time-driven and pick how often. Or create it in code and run this once:

function createHourlyTrigger() {
  ScriptApp.newTrigger('sendPendingEmails')
    .timeBased()
    .everyHours(1)
    .create();
}

When a form comes in. If rows arrive from a Google Form linked to the sheet, add an installable trigger for From spreadsheet > On form submit. The trigger passes an event object, but my script doesn't need it because it scans for unsent rows anyway. That also means a missed run gets caught up by the next one.

Don't use the simple onEdit or onFormSubmit function names for sending email. Simple triggers can't call services that need authorization, so the send fails without a clear error. Use an installable trigger.

Personalizing the message

The template above only uses the first name. In practice you'll want more columns, like the session someone signed up for or the amount they owe. Add them to the sheet and the destructuring line:

const [name, email, status, sentAt, session, balance] = rows[i];

Remember to widen the range to match (getRange(2, 1, lastRow - 1, 6)).

For anything longer than a few lines, I keep the email body in a separate tab or a Google Doc so the person who owns the wording can change it without opening the code. A simple way is to write placeholders like [Name] and [Session] in a cell and replace them:

function fillTemplate(template, values) {
  return Object.keys(values).reduce(
    (text, key) => text.split('[' + key + ']').join(values[key]),
    template
  );
}

Escape anything you drop into HTML that came from a form. Someone typing <b> into their name field shouldn't change how your email looks.

Attaching files from Drive

To attach a file, get it by its ID (the long string in its Drive URL) and pass it as an attachment:

const file = DriveApp.getFileById('PASTE_FILE_ID_HERE');
MailApp.sendEmail({
  to: email,
  subject: SUBJECT,
  body: body,
  htmlBody: html,
  attachments: [file.getAs(MimeType.PDF)]
});

getAs(MimeType.PDF) converts a Google Doc to PDF on the way out. For a file that's already a PDF, file.getBlob() works too. Attachments add to the size of each send and Google caps total attachment size per message, so for big files I send a Drive link instead.

Quotas and why emails stop

Apps Script counts recipients per day, not emails. A free Gmail account gets 100 a day and a Google Workspace account gets 1,500. One email to three people uses three. Google lists the current numbers on its Apps Script quotas page, and they do change, so check it if you're near the edge.

MailApp.getRemainingDailyQuota() tells you how many you have left right now.

If a send job is bigger than your daily quota, the status column does the work for you: the script stops when it runs out, and the next scheduled run the following day continues with the unsent rows.

The other limit to know is the six-minute cap on each run. Sending a few hundred emails fits easily. If yours has to build a PDF per person first, it may not, and that's where batching across runs comes in. I cover that in Apps Script limits.

Need it built for you?

If you'd rather have this set up and tested on your own sheet, with your templates and the edge cases handled, that's the kind of work I do under Google Sheets automation.

Questions people ask