How to send bulk email from Google Sheets

Last updated: 2026-09-22 · Working Apps Script, no add-on required

1. Lay out the sheet

One recipient per row, with a header row. The script finds columns by name, so the order does not matter and you can add your own columns freely.

2. Open the script editor

From inside the spreadsheet: Extensions → Apps Script. That creates a script bound to this one spreadsheet, which is what you want — it can reach this sheet without you granting access to the rest of your Drive.

3. The simplest version that works

Paste this, then press Run. The first run asks you to authorise it.

function sendBulkEmail() {
  const sheet  = SpreadsheetApp.getActiveSheet();
  const values = sheet.getDataRange().getValues();
  const header = values.shift();

  const col = (name) => {
    const i = header.indexOf(name);
    if (i === -1) throw new Error('Missing column: ' + name);
    return i;
  };
  const emailCol  = col('Email');
  const nameCol   = col('Name');
  const amountCol = col('Amount');

  values.forEach((row) => {
    const address = String(row[emailCol] || '').trim();
    if (!address) return;

    MailApp.sendEmail({
      to: address,
      subject: 'Your statement',
      body: 'Hi ' + row[nameCol] + ',\n\n'
          + 'Your balance is ' + row[amountCol] + '.\n\n'
          + 'Thanks.'
    });
  });
}

That is a working mail merge. It is also the version that will hurt you on a real list, for the two reasons below.

4. The daily quota, which is not negotiable

Google caps how many recipients one account can email per day from Apps Script. From Google's published quota table for Apps Script services:

The attachment ceiling is 25 MB per message. Quotas reset 24 hours after the first request, and Google states they may change without notice — so read the number at runtime rather than trusting any figure written down, including this one:

Logger.log(MailApp.getRemainingDailyQuota());

5. The 6-minute execution limit

A single Apps Script execution is stopped at 6 minutes. Sending is slow enough that a few hundred rows can reach that ceiling — and when it hits, the script is killed mid-loop.

The damage is not the interruption. It is that a naive script has no record of where it stopped, so running it again starts from row 1 and emails everyone who already received the message a second time.

6. The version that survives both

Three changes fix it:

1. Write a Status value into each row as it is sent, and skip rows already marked.
2. Stop voluntarily at 4 minutes, well before the 6-minute kill, so the last write lands.
3. Check the remaining quota before each send, and stop cleanly when it runs out.

const STATUS_HEADER = 'Status';
const RUN_BUDGET_MS = 4 * 60 * 1000;  // stop before the 6-minute ceiling

function sendBulkEmail() {
  const started = Date.now();
  const sheet   = SpreadsheetApp.getActiveSheet();
  const values  = sheet.getDataRange().getValues();
  const header  = values[0];

  const col = (name) => {
    const i = header.indexOf(name);
    if (i === -1) throw new Error('Missing column: ' + name);
    return i;
  };
  const emailCol  = col('Email');
  const nameCol   = col('Name');
  const amountCol = col('Amount');

  // Create the Status column if it is not there yet.
  let statusCol = header.indexOf(STATUS_HEADER);
  if (statusCol === -1) {
    statusCol = header.length;
    sheet.getRange(1, statusCol + 1).setValue(STATUS_HEADER);
  }

  let quota = MailApp.getRemainingDailyQuota();
  let sent = 0, skipped = 0, failed = 0;

  for (let r = 1; r < values.length; r++) {
    if (values[r][statusCol] === 'SENT') { skipped++; continue; }

    if (quota <= 0) {
      Logger.log('Daily quota exhausted. Re-run tomorrow; sent rows are marked.');
      break;
    }
    if (Date.now() - started > RUN_BUDGET_MS) {
      Logger.log('Time budget reached. Re-run to continue from row ' + (r + 1) + '.');
      break;
    }

    const address = String(values[r][emailCol] || '').trim();
    if (!address) {
      sheet.getRange(r + 1, statusCol + 1).setValue('SKIP: no address');
      skipped++;
      continue;
    }

    try {
      MailApp.sendEmail({
        to: address,
        subject: 'Your statement',
        body: 'Hi ' + values[r][nameCol] + ',\n\n'
            + 'Your balance is ' + values[r][amountCol] + '.\n\n'
            + 'Thanks.'
      });
      sheet.getRange(r + 1, statusCol + 1).setValue('SENT');
      quota--;
      sent++;
    } catch (err) {
      sheet.getRange(r + 1, statusCol + 1).setValue('ERR: ' + err.message);
      failed++;
    }

    SpreadsheetApp.flush();  // make the status durable before any timeout
  }

  Logger.log('sent=' + sent + ' skipped=' + skipped + ' failed=' + failed);
}

7. Test it without emailing anyone

Before a real run, point every address at yourself and check the count. Or comment out the MailApp.sendEmail call and log what it would have done — a dry run that touches nothing:

// MailApp.sendEmail({ ... });
Logger.log('WOULD SEND to ' + address);

8. Things that bite

• Blank-looking cells that are not blank. A cell containing only a space passes a truthiness check. The String(...).trim() above is what catches it.
• Numbers formatted as text. getValues() returns the underlying value, not what you see. If you need the formatted string, use getDisplayValues().
• One bad address stopping everything. The try/catch keeps a single malformed address from killing the run — the row is marked ERR: and the loop continues.
• Re-running after editing rows. Rows already marked SENT are skipped. To deliberately re-send a row, clear its Status cell.

Next: attach a personalised PDF

Sending plain text is the easy half. The common version of this job attaches a document that differs per row — an invoice, a certificate, a statement. That is the same loop with a template step in front of it: how to mail merge to PDF from Google Sheets.