Connect Google Sheets to SMTP: Send Email from a Sheet Without Zapier

Google Apps Script cannot open a raw SMTP connection — it has no socket access, so it cannot talk to smtp.yourprovider.com on port 587 the way a Node or Python script can. Nearly every "connect Google Sheets to SMTP" guide skips this, which is why people follow one and then wonder why there is nowhere to paste their SMTP host and password. There are two routes that do work: use Google's built-in mail service for low volume, or call your SMTP provider's HTTP API with UrlFetchApp for anything serious. Both live inside the spreadsheet, both run on a schedule, and neither needs Zapier.

This post covers both directions people actually mean by this search: sending mail out of a sheet, and catching delivery events back into one with a webhook.

Route 1: the built-in sender (no SMTP credentials at all)

If you are sending internal notifications, invoice reminders to your own client list, or a daily digest to your team, you do not need an SMTP provider. Apps Script ships with MailApp, which sends through Google's infrastructure using the account that owns the script.

Open your sheet, go to Extensions → Apps Script, and paste this:

function sendFromSheet() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Outbox');
  const rows = sheet.getDataRange().getValues();
  const header = rows.shift(); // Email | Name | Subject | Body | Status

  const remaining = MailApp.getRemainingDailyQuota();
  Logger.log('Quota left today: ' + remaining);

  rows.forEach((row, i) => {
    const [email, name, subject, body, status] = row;
    if (!email || status === 'SENT') return;      // skip blanks and re-sends
    if (MailApp.getRemainingDailyQuota() < 1) return; // stop before it throws

    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: body.replace('{{name}}', name),
      name: 'Your Company',
    });

    // Write the result back so the sheet is the source of truth
    sheet.getRange(i + 2, header.indexOf('Status') + 1).setValue('SENT');
    sheet.getRange(i + 2, header.indexOf('Status') + 2).setValue(new Date());
  });
}

Three details that matter more than the sending itself:

• Write the status back to the sheet. The SENT flag is what makes the script safe to re-run. Without it, a script that fails halfway through and gets retried will email your first thirty contacts twice.
• Check the quota before each send, not just at the start. MailApp.getRemainingDailyQuota() returns your remaining recipients; hitting zero throws an exception mid-loop and leaves rows in an unknown state.
• Schedule it. In the Apps Script editor, open Triggers (the clock icon) and add a time-driven trigger — hourly, or daily at 8am. That is the whole "automation platform" replaced.

The real limit: 100 recipients per day on consumer Gmail, 1,500 per day on Workspace. That is a hard cap, not a soft one. For a small business emailing customers back, it is plenty. For a newsletter, it is not.

Route 2: your SMTP provider's HTTP API

When you outgrow route 1 — or the moment you send to anyone who did not explicitly ask to hear from you — you want an authenticated sending domain with SPF and DKIM. Every SMTP provider offers an HTTPS send endpoint alongside their SMTP port, and that endpoint is what Apps Script talks to.

Here is Mailjet, which has the largest free tier of the common providers (6,000/month, 200/day):

function sendViaProvider(to, subject, html) {
  const key    = PropertiesService.getScriptProperties().getProperty('MJ_KEY');
  const secret = PropertiesService.getScriptProperties().getProperty('MJ_SECRET');

  const res = UrlFetchApp.fetch('https://api.mailjet.com/v3.1/send', {
    method: 'post',
    contentType: 'application/json',
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(key + ':' + secret),
    },
    payload: JSON.stringify({
      Messages: [{
        From: { Email: 'billing@yourdomain.com', Name: 'Your Company' },
        To:   [{ Email: to }],
        Subject: subject,
        HTMLPart: html,
      }],
    }),
    muteHttpExceptions: true,   // read the error instead of crashing
  });

  const code = res.getResponseCode();
  if (code >= 300) throw new Error('Send failed ' + code + ': ' + res.getContentText());
  return JSON.parse(res.getContentText());
}

Never paste API keys into the code. Use Project Settings → Script properties in the Apps Script editor and read them with PropertiesService, as above. A key hard-coded in a script that lives in a shared spreadsheet is a key you have given to everyone with edit access.

The same shape works for Mailgun, SendGrid, Brevo and Postmark — different URL, different auth header, same twelve lines. That portability is the point: if a provider raises prices, you change three lines instead of rebuilding a connector.

If you are sending in bulk, read our guide to UrlFetchApp quotas, retries and rate-limiting before you loop over a thousand rows — the failure modes there are the ones that actually bite in production.

The other direction: catching events back in a webhook

The second half of "Google Sheets SMTP webhook" is inbound. Your provider can POST every delivery, open, click and bounce to a URL — and Apps Script can be that URL. Add this to the same project:

function doPost(e) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Events');
  const data  = JSON.parse(e.postData.contents);

  // Providers send either one event or a batch — normalise both
  const events = Array.isArray(data) ? data : [data];
  events.forEach(ev => {
    sheet.appendRow([new Date(), ev.event, ev.email, ev.MessageID || '']);
  });

  return ContentService.createTextOutput(JSON.stringify({ ok: true }))
    .setMimeType(ContentService.MimeType.JSON);
}

Then Deploy → New deployment → Web app, set Execute as to yourself and Who has access to Anyone, and paste the resulting /exec URL into your provider's webhook settings. Bounces and spam complaints now land in a sheet you can filter, next to the list you sent from.

Two cautions. A web app set to "Anyone" is genuinely public, so treat the URL as a secret and validate the payload — most providers sign their webhooks, and you should check that signature before trusting a row. And appendRow is convenient but slow; if you expect thousands of events an hour, batch them. Our full webhooks guide covers both patterns in depth.

Which route should you use?

	Built-in MailApp	Provider HTTP API
Setup	None — it just works	Account, domain verification, SPF/DKIM
Daily limit	100 (consumer) / 1,500 (Workspace)	Your plan's limit
From address	Your Gmail/Workspace address	Any address on your verified domain
Deliverability	Inherits your personal reputation	Isolated, monitored, reportable
Bounce/open data	None	Full, via webhook
Cost	Free	Free tier, then usage-based
Right for	Internal alerts, reminders, replies	Customer mail, receipts, anything bulk

The honest rule: start with route 1. Most sheet-driven email is a few dozen notifications a day, and adding a provider before you need one is work you do not have to do. Move to route 2 the day you hit the quota, or the first time deliverability matters more than convenience.

What this replaces

A "Google Sheets to email" connector on an integration platform runs roughly $20–$30/month on entry plans, and those plans meter by task — every email is a task, so the bill grows with your sending. The script above costs nothing to run, has no task ceiling, lives in the spreadsheet your team already opens every morning, and keeps working if a vendor changes their pricing page.

That is the same trade we make across every guide here: a one-time build you own, inside your own Google account, instead of a subscription you rent forever. If you are weighing it up properly, our n8n, Zapier and Make comparison does the arithmetic over three years.

Where scripts like this get genuinely hard is not the sending — it is everything around it: retries that do not double-send, quota headroom, bounce handling, and a log someone can actually audit. If you would rather have that built properly once than debug it on a Friday afternoon, tell us what you are trying to send and we will scope it. A short feasibility review is free, and if the answer is "you can do this yourself in an afternoon," we will tell you that too.