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 (Mailgun dashboard → the domain → webhook settings — it is not the API key).

function mgVerify_(timestamp, token, signature) {
  const key = PropertiesService.getScriptProperties().getProperty('MG_WEBHOOK_KEY');

  const bytes = Utilities.computeHmacSha256Signature(String(timestamp) + token, key);
  const hex = bytes
    .map(b => ('0' + (b & 0xFF).toString(16)).slice(-2))  // bytes are signed in Apps Script
    .join('');

  if (hex !== signature) return false;

  // Reject replays: refuse anything older than 5 minutes
  const ageSeconds = (Date.now() / 1000) - Number(timestamp);
  return ageSeconds < 300;
}

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 matters too: a valid signature stays valid forever, so without an age limit, anyone who captures one request can replay it indefinitely.

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. That difference from the JSON event webhooks is exactly where most handlers quietly break.

function doPost(e) {
  // Inbound routes arrive form-encoded; event webhooks arrive as JSON.
  if (e.parameter && e.parameter['body-plain'] !== undefined) {
    return handleInbound_(e);
  }
  return handleEvent_(e);
}

function handleInbound_(e) {
  const p = e.parameter;
  SpreadsheetApp.getActive().getSheetByName('Inbox').appendRow([
    new Date(),
    p.sender,
    p.recipient,
    p.subject,
    p['stripped-text'] || p['body-plain'],   // stripped-text drops quoted replies
    p['attachment-count'] || 0,
  ]);
  return ContentService.createTextOutput('ok');
}

function handleEvent_(e) {
  const data = JSON.parse(e.postData.contents);
  const sig  = data.signature || {};
  if (!mgVerify_(sig.timestamp, sig.token, sig.signature)) {
    return ContentService.createTextOutput('forbidden');  // do not write the row
  }

  const ev = data['event-data'] || {};
  SpreadsheetApp.getActive().getSheetByName('Events').appendRow([
    new Date(),
    ev.event,                                   // delivered | opened | failed | complained
    (ev.recipient || ''),
    (ev['user-variables'] || {}).row || '',     // -> your sheet row number
    (ev.message || {}).headers ? ev.message.headers['message-id'] : '',
    (ev['delivery-status'] || {}).message || '',
  ]);
  return ContentService.createTextOutput('ok');
}

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.

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.