Connect Google Sheets to Mailgun: Verified Webhooks and Inbound Email to Rows

Mailgun is worth the setup for two things Apps Script users rarely get elsewhere: cryptographically signed webhooks, so nobody can forge rows into your spreadsheet, and inbound routing, which turns email arriving at your domain into structured rows. Sending is the easy part and takes a dozen lines. This post covers all three, plus the region mistake that produces a 401 with a perfectly valid key.

If you are still choosing a provider, our SMTP overview explains why Apps Script cannot speak raw SMTP at all, and the Mailjet guide covers the friendliest free tier. Come here when you want the security and the inbound half.

The region gotcha, first

Mailgun runs separate US and EU infrastructure, and they are not interchangeable. A domain created in the EU region will reject every call to api.mailgun.net with a 401 that looks exactly like a bad key. If your credentials are definitely right and you are getting 401s, this is why:

const MG_BASE   = 'https://api.mailgun.net/v3';     // US
// const MG_BASE = 'https://api.eu.mailgun.net/v3'; // EU — check your dashboard
const MG_DOMAIN = 'mg.yourdomain.com';

The second trap is the auth format. The Basic auth username is the literal string api — not your email, not your domain — and the password is the private API key, not the public validation key.

Sending: form-encoded, not JSON

Most modern APIs take JSON. Mailgun's send endpoint takes form fields, which in Apps Script means passing a plain object as payload and not setting contentType — UrlFetchApp will encode it correctly on its own.

function mgSend_(to, subject, html, rowNumber) {
  const key = PropertiesService.getScriptProperties().getProperty('MG_KEY');

  const res = UrlFetchApp.fetch(MG_BASE + '/' + MG_DOMAIN + '/messages', {
    method: 'post',
    headers: { Authorization: 'Basic ' + Utilities.base64Encode('api:' + key) },
    muteHttpExceptions: true,
    payload: {
      from: 'Your Company <billing@' + MG_DOMAIN + '>',
      to: to,
      subject: subject,
      html: html,
      'v:row': String(rowNumber),   // custom variable, echoed back on every event
      'o:tag': 'invoice-reminder',  // groups these sends in Mailgun's analytics
    },
  });

  if (res.getResponseCode() >= 300) {
    throw new Error('Mailgun ' + res.getResponseCode() + ': ' + res.getContentText());
  }
  return JSON.parse(res.getContentText()).id;  // store this Message-Id in the sheet
}

The v: prefix is Mailgun's custom-variable convention. Anything you set there comes back on every webhook event for that message under user-variables, which is how you join an open or a bounce to the spreadsheet row that produced it. Set it to the row number and your reporting becomes a COUNTIFS rather than a guess.

Keep the returned message id in a column. It is what Mailgun's logs are searchable by when a customer swears they never got the invoice.

Verifying webhooks — do not skip this

An Apps Script web app deployed with access set to Anyone is genuinely public. Anyone who learns the URL can POST to it, and if your handler appends whatever it receives, they can write rows into your spreadsheet. Mailgun signs every webhook precisely so you can refuse that.

Each request carries a signature object with timestamp, token and signature. The signature is an HMAC-SHA256 of timestamp + token, keyed with your webhook signing key. It is account-wide, under Settings → API Security → HTTP webhook signing key, and it is not the API key.

const MAX_AGE_S = 900; // Mailgun warns that delivery can be delayed

function hex_(bytes) {
  // Apps Script returns signed bytes (-128..127)
  return bytes
    .map(b => ('0' + (b & 0xff).toString(16)).slice(-2))
    .join('');
}

// Compare every character, so the time taken leaks nothing.
function sameHex_(a, b) {
  if (typeof a !== 'string' || typeof b !== 'string') return false;
  if (a.length !== b.length) return false;
  let diff = 0;
  for (let i = 0; i < a.length; i++) {
    diff |= a.charCodeAt(i) ^ b.charCodeAt(i);
  }
  return diff === 0;
}

// One check for event webhooks and Routes posts alike.
// Returns '' when the request is genuine, else the reason.
function checkSignature_(sig, key, nowS) {
  if (!sig || !sig.timestamp || !sig.token || !sig.signature) {
    return 'missing signature';
  }
  const mac = Utilities.computeHmacSha256Signature(
    String(sig.timestamp) + sig.token, key);
  const sent = String(sig.signature).toLowerCase();
  if (!sameHex_(hex_(mac), sent)) return 'bad signature';
  const age = nowS - Number(sig.timestamp);
  if (!(Math.abs(age) <= MAX_AGE_S)) return 'stale timestamp';
  return '';
}

// Honor each token once. The lock stops two copies of the
// same request from both passing the check.
function firstUse_(token) {
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const cache = CacheService.getScriptCache();
    if (cache.get('mg:' + token)) return false;
    cache.put('mg:' + token, '1', 2 * MAX_AGE_S);
    return true;
  } finally {
    lock.releaseLock();
  }
}

The & 0xff matters. Apps Script returns signed bytes, so a naive toString(16) produces negative values and a hex string that never matches. It is the single most common reason a correct-looking verification always fails.

The timestamp check is written as "not within 15 minutes" on purpose. A plain age > 900 lets a non-numeric timestamp through, because NaN > 900 is false, and a one-sided age < 300 accepts any timestamp from the future. Mailgun also warns that delivery can be delayed, so a five-minute window drops real events.

The signature covers only the timestamp and the token, not the body, so a captured request could be resent with new content inside the window. firstUse_ remembers each token in CacheService and refuses it a second time, as Mailgun's security guide suggests.

Inbound: email straight into rows

This is Mailgun's best trick. In the dashboard, open Receiving → Create Route, match on a recipient (for example match_recipient("orders@mg.yourdomain.com")) and set the action to forward("https://script.google.com/.../exec").

Mailgun then parses each incoming message and POSTs the pieces to your script — form-encoded, so you read e.parameter, not e.postData.contents. Mailgun signs these posts too, with the same key: timestamp, token and signature arrive as three form fields next to sender and body-plain. An inbound branch that skips the check is a public URL that writes whatever anyone sends it into your sheet, so the handler below verifies before it decides which kind of request it has.

function doPost(e) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('MG_SIGNING_KEY');
  if (!key) throw new Error('MG_SIGNING_KEY is not set');

  const req = parseRequest_(e);
  const why = checkSignature_(req.sig, key, Date.now() / 1000) ||
    (firstUse_(req.sig.token) ? '' : 'token already used');
  if (why) {
    logRejected_(req, why);
    return ContentService.createTextOutput('rejected');
  }
  if (req.kind === 'event') writeEvent_(req.body);
  else writeInbound_(req.body);
  return ContentService.createTextOutput('ok');
}

// Pure: pick the shape from the content type. e.parameter also
// holds the query string, so a field test can be fooled.
function parseRequest_(e) {
  const type = (e.postData && e.postData.type) || '';
  if (type.indexOf('application/json') === 0) {
    let body = {};
    try {
      body = JSON.parse(e.postData.contents);
    } catch (err) {
      body = {};
    }
    return { kind: 'event', type: type,
      sig: body.signature || {}, body: body };
  }
  const p = e.parameter || {};
  return { kind: 'inbound', type: type, body: p,
    sig: { timestamp: p.timestamp, token: p.token,
      signature: p.signature } };
}

parseRequest_ picks the path from the content type, not from whether a field like body-plain is present: e.parameter also holds the query string, so a field check can be steered by the URL alone. A rejected request leaves one row in a Rejected tab with the reason and nothing from the body. If that tab starts filling up, someone has your /exec URL.

A valid signature proves Mailgun delivered the request, not that the text inside is safe. Inbound mail is written by whoever emailed you, and appendRow treats any value that starts with = as a formula. So every string goes through cell_ before it reaches the sheet:

const FORMULA_START = /^[=+\-@\t\r]/;
const CELL_MAX = 50000;

// Inbound text was written by whoever emailed you. A valid
// signature means Mailgun delivered it, not that it is safe.
function cell_(v) {
  let s = v == null ? '' : String(v);
  if (FORMULA_START.test(s)) s = "'" + s;
  if (s.length > CELL_MAX) s = s.slice(0, CELL_MAX - 1) + '…';
  return s;
}

// Pure: one Inbox row. stripped-text drops the quoted thread.
function inboundRow_(p, now) {
  return [now, cell_(p.sender), cell_(p.recipient),
    cell_(p.subject),
    cell_(p['stripped-text'] || p['body-plain']),
    Number(p['attachment-count']) || 0];
}

// Pure: one Events row. user-variables.row joins it to a send.
function eventRow_(data, now) {
  const ev = data['event-data'] || {};
  const headers = (ev.message || {}).headers || {};
  return [now, cell_(ev.event), cell_(ev.recipient),
    cell_((ev['user-variables'] || {}).row),
    cell_(headers['message-id']),
    cell_((ev['delivery-status'] || {}).message)];
}

function writeInbound_(p) {
  sheet_('Inbox').appendRow(inboundRow_(p, new Date()));
}

function writeEvent_(data) {
  sheet_('Events').appendRow(eventRow_(data, new Date()));
}

// No body here: a forged request leaves a trace in the sheet,
// but none of its text.
function logRejected_(req, why) {
  sheet_('Rejected').appendRow(
    [new Date(), req.kind, cell_(req.type), why]);
}

function sheet_(name) {
  const ss = SpreadsheetApp.getActive();
  return ss.getSheetByName(name) || ss.insertSheet(name);
}

Prefer stripped-text over body-plain for replies — it removes the quoted thread underneath, so your sheet holds what the person actually wrote rather than the last six messages again. Messages with attachments arrive as multipart/form-data; send yourself one after you deploy and check that it lands in Inbox, not in Rejected.

What you can build once mail lands in a sheet

Inbound routing is the piece that changes what is possible, because it makes email a data source rather than a place work goes to die:

• Order intake. Suppliers email order confirmations to a dedicated address; each becomes a row, and a second script parses reference numbers into columns.
• Support triage. Every message to help@ is logged with a timestamp, so first-response time becomes a formula instead of a feeling.
• Document capture. Combine with attachment handling and an AI extraction step to pull totals off invoices — we cover that pattern in extracting invoices with AI vision.

None of this needs a helpdesk subscription. It needs a route, a verified webhook, and a sheet you already own.

Where the real work is

The code above is the honest 80%. The remaining 20% is what separates a demo from something you can leave running: retry handling when Apps Script's execution window closes mid-batch, batching setValues instead of appendRow once volume climbs, a suppression check so bounced addresses never get mailed again, and attachment storage in Drive with sane naming. Our guide to UrlFetchApp quotas and retries covers the failure modes that show up first.

A connector platform that offers Mailgun triggers will do the simple half of this for $20–$30/month, metered per task — and will not sign-verify anything for you, because the webhook terminates on their servers, not yours. The script terminates on yours. That is the difference worth paying attention to, more than the money.

If you want an inbound-email pipeline built and handed over working — routing, verification, parsing, the lot — tell us what arrives in that inbox and what should come out. The feasibility review is free, and if a Mailgun route plus forty lines of script covers you, that is what we will tell you.