Send Emails from Google Sheets with Apps Script: Step-by-Step Guide

There is a moment in every Google Sheets user's workflow where the spreadsheet stops being enough on its own. You have a list of clients to notify about an invoice due date. You need to send a confirmation to every person who signed up via a form. You want a daily summary of overdue tasks emailed to your team leader without anyone having to remember to do it. In every one of these scenarios, the data is already in the sheet — the missing piece is the email.

Google Apps Script bridges that gap entirely within your existing Google Workspace account. No third-party tools, no mail merge add-ons with monthly fees, no copy-pasting addresses into Gmail. You write a small amount of JavaScript, connect it to your sheet, and emails go out automatically — personalised, formatted, and tracked — on whatever schedule or trigger you define.

This guide covers everything you need to build a production-ready email system from Google Sheets: which email class to use, how to read recipient data from your spreadsheet, how to build HTML email templates, how to attach files from Drive, how to handle errors gracefully, and how to track sent status back in the sheet so you always know what has gone out.

MailApp vs GmailApp: Which Class Should You Use?

Apps Script provides two separate classes for sending email: MailApp and GmailApp. They overlap in capability but differ in scope, permissions, and daily sending quotas. Choosing the right one before you start saves you rewriting code later.

MailApp is the simpler, lighter option. It requests only email sending permission — nothing else. Your script cannot read, search, or manage your Gmail inbox with MailApp. For use cases where the script only needs to send outbound messages and the operator has no interest in reading email in return, MailApp is the appropriate choice. The authorisation dialog shown to the user is narrower, which makes it easier to explain to non-technical team members why the script needs access.

GmailApp is the full Gmail service. It can send email, read messages, search the inbox, create drafts, manage labels, and thread replies. The trade-off is broader permissions — when you authorise a GmailApp script, you are granting it access to your entire Gmail account, not just the ability to send. For scripts that also need to check whether a contact has replied, search for related threads, or create draft messages for review, GmailApp is necessary.

Daily quota limits are the practical constraint that governs which class you use at scale:

Both classes share the same underlying quota pool — you cannot send 1,500 via MailApp and another 1,500 via GmailApp on the same day. The limit is per calendar day (UTC), and it resets at midnight UTC. If your script exceeds the quota mid-run, it throws a Service invoked too many times exception. The error handling section below covers how to manage this.

For the majority of Australian SMB use cases — invoices, notifications, form confirmations, weekly reports — MailApp is the right starting point. Switch to GmailApp only when you need bidirectional inbox access.

Setting Up Your Spreadsheet

Before writing a single line of code, structure your sheet properly. A well-designed spreadsheet makes the script simpler and the tracking system more useful.

Create a sheet with these columns as a minimum:

You do not need all of these for a basic send, but adding the tracking columns (E and F) from the outset makes the script significantly more useful. Every time the script fires, it writes back to those columns — giving you a permanent audit trail of who received what and when.

Sending a Basic Email

With your sheet set up, open the script editor: Extensions > Apps Script. Delete the default empty function and start fresh.

Here is a minimal working script that sends a plain-text email to every row in the sheet:

function sendBasicEmails() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0];

  // Map header names to column indices
  var nameCol      = headers.indexOf('Name');
  var emailCol     = headers.indexOf('Email');
  var sentCol      = headers.indexOf('Sent');
  var timestampCol = headers.indexOf('SentTimestamp');

  for (var i = 1; i < data.length; i++) {
    var row       = data[i];
    var name      = row[nameCol];
    var email     = row[emailCol];
    var sentStatus= row[sentCol];

    // Skip rows already sent or with no email address
    if (sentStatus === 'Sent' || !email) continue;

    var subject = 'A note from Cloud Geeks';
    var body    = 'Hi ' + name + ',\n\n'
                + 'This is a quick note from the team at Cloud Geeks.\n\n'
                + 'Cheers,\nAsh Ganda\nCloud Geeks';

    try {
      MailApp.sendEmail(email, subject, body);

      // Write the sent status back to the sheet
      sheet.getRange(i + 1, sentCol + 1).setValue('Sent');
      sheet.getRange(i + 1, timestampCol + 1).setValue(new Date());

    } catch (e) {
      sheet.getRange(i + 1, sentCol + 1).setValue('Error: ' + e.message);
    }
  }
}

A few things worth noting here before moving on:

• headers.indexOf('Name') dynamically finds the column position by matching the header string, rather than hardcoding column numbers. This means the script still works if you reorder columns.
• The if (sentStatus === 'Sent' || !email) continue; check prevents duplicates. If you run the script twice, rows already marked "Sent" are skipped entirely.
• sheet.getRange(i + 1, sentCol + 1) uses i + 1 because the loop starts at i = 1 (row index 1 is the data's second row, but getRange uses 1-based row numbering, so i + 1 maps the data array back to the sheet row).

Save and run this function. On first run, approve the permissions dialog. Check your sheet after execution — the Sent column should show "Sent" for each row that was processed successfully.

Building an HTML Email Template

Plain-text emails work, but HTML emails give you formatting, branded headers, buttons, and properly structured content. Apps Script supports full HTML in both MailApp and GmailApp via the htmlBody option.

The cleanest approach is a template function that builds the HTML string with personalisation variables injected at the right points:

function buildHtmlEmail(name, invoiceNumber, dueDate, amount) {
  return '<!DOCTYPE html>'
    + '<html><body style="font-family: Arial, sans-serif; color: #333; max-width: 600px; margin: 0 auto;">'
    + '<div style="background-color: #1a73e8; padding: 20px 30px;">'
    + '  <h1 style="color: #ffffff; margin: 0; font-size: 22px;">Cloud Geeks</h1>'
    + '</div>'
    + '<div style="padding: 30px;">'
    + '  <p>Hi ' + name + ',</p>'
    + '  <p>This is a reminder that invoice <strong>' + invoiceNumber + '</strong> '
    + '  for <strong>$' + amount + ' AUD</strong> is due on <strong>' + dueDate + '</strong>.</p>'
    + '  <p>If you have already arranged payment, please disregard this message.</p>'
    + '  <div style="margin: 30px 0;">'
    + '    <a href="https://insights.cloudgeeks.com.au/pay" '
    + '       style="background-color: #1a73e8; color: #ffffff; padding: 12px 24px; '
    + '              text-decoration: none; border-radius: 4px; font-weight: bold;">'
    + '      Pay Now</a>'
    + '  </div>'
    + '  <p>Questions? Reply to this email or call us on 1300 XXX XXX.</p>'
    + '  <p>Thanks,<br>Ash Ganda<br>Cloud Geeks<br>'
    + '     <a href="https://insights.cloudgeeks.com.au">insights.cloudgeeks.com.au</a></p>'
    + '</div>'
    + '</body></html>';
}

To use this template inside your send function, pass the htmlBody option to MailApp.sendEmail():

MailApp.sendEmail({
  to:       email,
  subject:  'Invoice ' + invoiceNumber + ' — Payment Due ' + dueDate,
  body:     'Hi ' + name + ', your invoice ' + invoiceNumber + ' is due on ' + dueDate + '. Please visit https://insights.cloudgeeks.com.au/pay.',
  htmlBody: buildHtmlEmail(name, invoiceNumber, dueDate, amount)
});

Always include the plain-text body alongside htmlBody. Email clients that cannot render HTML — or users who have HTML email disabled — will fall back to the plain-text version. Omitting it is a common oversight that results in blank emails for a subset of recipients.

Reading Recipient Data from Google Sheets

For a more complete invoice-style send, here is how to read multiple columns from the sheet and pass the values into your HTML template:

function sendInvoiceReminders() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data  = sheet.getDataRange().getValues();
  var headers = data[0];

  var nameCol      = headers.indexOf('Name');
  var emailCol     = headers.indexOf('Email');
  var invoiceCol   = headers.indexOf('InvoiceNumber');
  var dueDateCol   = headers.indexOf('DueDate');
  var amountCol    = headers.indexOf('Amount');
  var sentCol      = headers.indexOf('Sent');
  var timestampCol = headers.indexOf('SentTimestamp');

  var sentCount  = 0;
  var errorCount = 0;

  for (var i = 1; i < data.length; i++) {
    var row       = data[i];
    var name      = row[nameCol];
    var email     = row[emailCol];
    var invoice   = row[invoiceCol];
    var dueDate   = row[dueDateCol];
    var amount    = row[amountCol];
    var sentStatus= row[sentCol];

    if (sentStatus === 'Sent' || !email) continue;

    var subject   = 'Invoice ' + invoice + ' — Due ' + dueDate;
    var plainText = 'Hi ' + name + ', your invoice ' + invoice
                  + ' for $' + amount + ' AUD is due on ' + dueDate + '.';
    var htmlBody  = buildHtmlEmail(name, invoice, dueDate, amount);

    try {
      MailApp.sendEmail({
        to:       email,
        subject:  subject,
        body:     plainText,
        htmlBody: htmlBody
      });

      sheet.getRange(i + 1, sentCol + 1).setValue('Sent');
      sheet.getRange(i + 1, timestampCol + 1).setValue(new Date());
      sentCount++;

      // Brief pause to avoid triggering rate-limiting
      Utilities.sleep(500);

    } catch (e) {
      sheet.getRange(i + 1, sentCol + 1).setValue('Error: ' + e.message);
      errorCount++;
    }
  }

  // Summary alert once the loop is done
  SpreadsheetApp.getUi().alert(
    'Done. ' + sentCount + ' email(s) sent. ' + errorCount + ' error(s).'
  );
}

Utilities.sleep(500) introduces a half-second pause between each send. This is a sensible precaution when sending in bulk — it reduces the chance of hitting rate limits and gives Google's servers a moment between requests. For lists of fewer than 50 recipients it is optional, but it costs almost nothing and protects against intermittent failures at higher volumes.

Adding Attachments from Google Drive

Attachments in Apps Script work by fetching a file from Google Drive using its file ID, then passing it to the email options object via the attachments array:

function sendEmailWithAttachment() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data  = sheet.getDataRange().getValues();
  var headers = data[0];

  var nameCol      = headers.indexOf('Name');
  var emailCol     = headers.indexOf('Email');
  var fileIdCol    = headers.indexOf('AttachmentFileId');
  var sentCol      = headers.indexOf('Sent');
  var timestampCol = headers.indexOf('SentTimestamp');

  for (var i = 1; i < data.length; i++) {
    var row      = data[i];
    var name     = row[nameCol];
    var email    = row[emailCol];
    var fileId   = row[fileIdCol];
    var sentStatus = row[sentCol];

    if (sentStatus === 'Sent' || !email) continue;

    try {
      var attachments = [];

      if (fileId) {
        var file = DriveApp.getFileById(fileId);
        attachments.push(file.getAs(MimeType.PDF));
      }

      MailApp.sendEmail({
        to:          email,
        subject:     'Your document from Cloud Geeks',
        body:        'Hi ' + name + ', please find your document attached.',
        attachments: attachments
      });

      sheet.getRange(i + 1, sentCol + 1).setValue('Sent');
      sheet.getRange(i + 1, timestampCol + 1).setValue(new Date());

    } catch (e) {
      sheet.getRange(i + 1, sentCol + 1).setValue('Error: ' + e.message);
    }
  }
}

file.getAs(MimeType.PDF) converts the file to PDF before attaching it. This is particularly useful when your source file is a Google Doc or Sheet — Apps Script converts it on the fly to a PDF without you needing to export it first. If you want to attach the file in its original format, use file.getBlob() instead.

Add the Google Drive file ID to your spreadsheet in an AttachmentFileId column. To find a file's ID in Drive, open the file and look at the URL — everything after /d/ and before the next / is the file ID.

Daily Quota Limits and How to Handle Them

The sending quotas set the practical ceiling for any Apps Script email system:

• Google Workspace paid accounts (Business Starter, Standard, Plus, Enterprise): 1,500 recipients per day
• Free Gmail accounts: 100 recipients per day

These limits apply to the account running the script, not the script itself. If you have a Workspace Business Standard licence, the script runs under your Workspace account and inherits that account's 1,500 daily quota.

The quota resets each day at midnight UTC, which is 10am AEST (or 11am AEDT during daylight saving). If you are sending to large lists and hitting the limit before the reset, consider splitting sends across multiple days using a trigger that runs daily and processing only a batch of rows per execution.

Here is a quota-aware version of the send loop that stops gracefully when the daily limit is approaching:

function sendEmailsBatchSafe() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data  = sheet.getDataRange().getValues();
  var headers = data[0];

  var emailCol     = headers.indexOf('Email');
  var nameCol      = headers.indexOf('Name');
  var sentCol      = headers.indexOf('Sent');
  var timestampCol = headers.indexOf('SentTimestamp');

  // Leave a buffer of 50 to avoid hitting the hard limit mid-run
  var remainingQuota = MailApp.getRemainingDailyQuota();
  var safeLimit = remainingQuota - 50;
  var sentCount = 0;

  if (safeLimit <= 0) {
    Logger.log('Daily quota nearly exhausted. Aborting run.');
    return;
  }

  for (var i = 1; i < data.length; i++) {
    if (sentCount >= safeLimit) {
      Logger.log('Reached safe sending limit for today. Resuming tomorrow.');
      break;
    }

    var row       = data[i];
    var email     = row[emailCol];
    var name      = row[nameCol];
    var sentStatus= row[sentCol];

    if (sentStatus === 'Sent' || !email) continue;

    try {
      MailApp.sendEmail(email, 'Update from Cloud Geeks', 'Hi ' + name + ', ...');
      sheet.getRange(i + 1, sentCol + 1).setValue('Sent');
      sheet.getRange(i + 1, timestampCol + 1).setValue(new Date());
      sentCount++;
      Utilities.sleep(300);

    } catch (e) {
      sheet.getRange(i + 1, sentCol + 1).setValue('Error: ' + e.message);
    }
  }

  Logger.log('Run complete. Sent: ' + sentCount + '. Remaining quota: ' + (MailApp.getRemainingDailyQuota()));
}

MailApp.getRemainingDailyQuota() returns the number of emails you can still send today. Checking this before the loop and leaving a buffer of 50 prevents the script from crashing mid-run. Any rows not processed in today's run remain with a blank Sent column and will be picked up when the script runs again tomorrow.

Error Handling and Tracking Sent Status

Error handling is what separates a script you trust from one you hold your breath about every time it runs. The core pattern — wrapping each send in a try/catch and writing the error message back to the sheet — has been in every script in this guide. Here is why it matters in practice.

Without error handling, a single bad email address (a typo, a closed account, a domain that does not exist) will stop the entire script mid-run and leave the remaining recipients uncontacted. With try/catch, the error is caught, written to the Sent column as Error: [message], and the loop continues to the next row. You end up with a complete run where the vast majority of emails were sent, and a small set of rows marked with errors for you to investigate manually.

The SentTimestamp column serves a different purpose — it is your audit trail. If a recipient claims they never received the email, you can check the timestamp and compare it to your Gmail Sent Items. If the sheet shows "Sent" at 09:14 AEST and the Gmail log confirms dispatch at the same time, you have clear evidence the email left your account.

For scripts running on time-based triggers (rather than manually), add email error notification so problems surface proactively:

function notifyOnErrors(errors) {
  if (errors.length === 0) return;

  var subject = '[Action Required] Email send errors — ' + errors.length + ' row(s) failed';
  var body    = 'The following rows encountered errors during the email send run:\n\n'
              + errors.join('\n')
              + '\n\nPlease review the Sent column in your Google Sheet.';

  MailApp.sendEmail(Session.getActiveUser().getEmail(), subject, body);
}

Call notifyOnErrors(errors) at the end of your main function, passing in the array of error strings you collected during the loop. If there are no errors, the function returns immediately. If there are errors, you receive an email summary at the address associated with your Google account — no log-checking required.

Affiliate & Partner Programs

If you are building out a more sophisticated email operation on top of Google Workspace, a few tools integrate naturally with the Apps Script approach described in this guide and are worth exploring:

Google Workspace Referral Program — referworkspace.app.goo.gl. The 1,500-recipient daily quota is a Workspace benefit. Free Gmail accounts are limited to 100 sends per day, which is sufficient for internal notifications but quickly becomes a constraint for any client-facing send. If your business is still running on free Gmail, migrating to a paid Workspace plan — from approximately AUD $10 per user per month for Business Starter — unlocks the higher quota alongside SPF, DKIM, and DMARC email authentication, which directly improves deliverability for the emails your script sends.

GMass — gmass.co. For high-volume campaigns (thousands of contacts) or situations where you need open and click tracking without writing custom pixel logic, GMass extends Gmail and Sheets with a purpose-built campaign layer. It complements the Apps Script approach: use Apps Script for automated transactional sends (invoices, confirmations, report notifications) and GMass for larger outreach campaigns where engagement tracking is a priority.

Document Studio — available via the Google Workspace Marketplace. If your emails need to include dynamically generated PDF reports from Google Docs templates (for example, sending each client a custom-formatted report alongside the notification email), Document Studio handles the merge-to-PDF step cleanly and integrates with Sheets in a way that pairs well with the Apps Script send functions in this guide.

Conclusion

Sending emails from Google Sheets with Apps Script is one of the most immediately useful things you can automate in a Google Workspace environment. The data is already in your sheet — names, email addresses, amounts, dates, file references — and the code required to turn that data into personalised, tracked emails is shorter than most people expect.

Start with the basic send function, confirm it works against a small test list, and build from there. Add HTML formatting when plain text feels insufficient. Add Drive attachments when your recipients need files alongside the message. Add the quota check when your list grows beyond a few hundred rows. Layer in error notifications when the script runs on a schedule and nobody is watching.

The tracking columns — Sent and SentTimestamp — are worth setting up from the very beginning, even before you need them. A spreadsheet that records its own history is far more useful than one that just fires emails into the void. When someone asks whether an invoice reminder went out, you will have the answer in a cell, not in your memory.

Need help with your Google Workspace setup? Contact our team for a free consultation.

Ash Ganda is the founder of CloudGeeks, a Google Workspace consultancy helping Australian SMBs get more from their cloud tools. He writes about practical technology strategies at insights.cloudgeeks.com.au.