Send Email from Google Apps Script and Google Sheets Rows via API

A Google Sheet is often the real database of a small business: bookings, applicants, orders, invoices. Sending an email for each new row with MailApp works until you hit the daily Gmail quota or want the mail to come from your own domain rather than a personal Google account. Apps Script can call any HTTPS API with UrlFetchApp, so the sheet can send through your business domain instead. Here is the script and the wiring.

Quick answer

Store your API key in Script Properties, write a function that reads rows where a "Sent" column is empty, POSTs one JSON request per row with UrlFetchApp, and writes a timestamp into the Sent column on success. Run it from a time-driven trigger every 5 or 10 minutes, or from an onEdit trigger if you need it immediate.

Set up the sheet and the key

1. Lay out columns, for example A: Email, B: Name, C: Order, D: Sent. Row 1 is the header.
2. Open Extensions > Apps Script from the sheet.
3. In the editor go to Project Settings (the gear) > Script Properties > Add property. Name OQUMAIL_API_KEY, value your oqm_live_ key. Properties are not visible in the code or to viewers of the sheet.
4. Paste the script below and save.

The script

function sendPendingRows() {
  const key = PropertiesService.getScriptProperties().getProperty('OQUMAIL_API_KEY');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');
  const rows = sheet.getDataRange().getValues();

  for (let i = 1; i < rows.length; i++) {
    const [email, name, order, sent] = rows[i];
    if (!email || sent) continue;

    const payload = {
      from: 'orders@yourdomain.com',
      to: String(email).trim(),
      subject: 'Order ' + order + ' received',
      html: '<p>Hi ' + escapeHtml(name) + ', we received order <strong>' + escapeHtml(order) + '</strong>. We will email you when it ships.</p>',
      text: 'Hi ' + name + ', we received order ' + order + '. We will email you when it ships.'
    };

    const res = UrlFetchApp.fetch('https://api.oqumail.com/api/v1/emails', {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + key },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true
    });

    if (res.getResponseCode() === 202) {
      sheet.getRange(i + 1, 4).setValue(new Date());
    } else {
      sheet.getRange(i + 1, 4).setValue('ERROR ' + res.getResponseCode());
    }
    Utilities.sleep(300);
  }
}

function escapeHtml(s) {
  return String(s).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;');
}

Triggers

In the Apps Script editor open Triggers (the clock icon) > Add Trigger. Choose sendPendingRows, event source Time-driven, minutes timer, every 5 minutes. The first run asks you to authorise the script for external requests and spreadsheet access. For instant sending when someone submits a Google Form linked to the sheet, add a second trigger on the "On form submit" event. Avoid onEdit for this; it fires on every keystroke in the sheet and you will send half-typed addresses.

Details that keep it reliable

• The Sent column is your idempotency key. Write it immediately after a 202 so a trigger that overlaps with a slow run does not send twice. For extra safety use LockService.getScriptLock() at the top of the function.
• Validate the address before sending: a simple regex catches blanks and typos and avoids a 400 response for the row.
• The 300 ms sleep keeps you well within polite sending rates; a sheet with 200 new rows takes about a minute.
• Apps Script has a daily limit on UrlFetchApp calls (tens of thousands on Workspace accounts, fewer on free Gmail accounts); it is far above what a sheet-driven process needs.
• Log failures to a second sheet with the response body so you can see why a row errored.
• Escape user-typed values in the HTML. A name containing < would otherwise break the message.

Why not MailApp or GmailApp?

Both send from the Google account running the script, with that account's daily quota (100 recipients per day on consumer Gmail, 1,500 on Workspace), and the from address must be that account or one of its verified aliases. Posting to OquMail's API sends from any address on your verified domain, does not consume Gmail quota, and records each message in a delivery log with the receiving server's SMTP response. The orders@ address can also be a normal OquMail mailbox so replies come to your team, and the free plan covers 15 mailboxes across 3 domains.

Common questions

Can I personalise the HTML per row?

Yes, build the html string from any columns you like. For longer templates keep the HTML in a separate sheet cell or a Drive file and replace placeholders with string replacement.

How do I stop the script sending old rows when I first turn it on?

Fill the Sent column for existing rows with the word "skip" before creating the trigger. The script treats any non-empty value as already sent.

Can other editors of the sheet see the API key?

Script Properties are visible to anyone who can open the script editor, which usually means editors of the sheet. If that is a concern, move the script to a standalone project owned by you and access the sheet by ID.