How to Store Form Submissions in Google Sheets (advanced)

Why the basic tutorial isn't enough

Most "send your form to Google Sheets" tutorials stop at sheet.appendRow([...]). That works for a personal site collecting two emails a month. The minute you put it on production traffic, four issues show up: dates show as text instead of dates, the header row gets clobbered when you rebuild the sheet, the columns drift if your form fields change, and anyone who finds the web app URL can spam your sheet.

This guide is the version that survives those four issues. We'll deploy a real Apps Script with a fixed schema, proper Date objects, header guarding, and HMAC verification of the splitforms webhook. We'll also cover the no-code path through Make or Zapier for teams without a developer. If you haven't set up the form side yet, point your form at splitforms first — grab a copy-paste contact form from the templates.

Method A — Google Apps Script web app

Apps Script lets you publish a doPost(e)function as a public HTTPS endpoint that runs inside Google's infrastructure with built-in access to any sheet you own. No OAuth, no service account, no enabled APIs.

1. Open your target Google Sheet. Note the tab name (e.g. "Submissions") — the script needs to match it exactly.
2. Click Extensions → Apps Script.
3. Replace the placeholder function myFunction() with the script below.
4. Click Save (Ctrl/Cmd-S), name the project "splitforms webhook".
5. Click Deploy → New deployment → Web app.
6. Configure: Execute as "Me", Who has access "Anyone". Click Deploy.
7. Authorize when prompted (you'll see a Google Account sign-in flow). Copy the Web app URL.

The URL looks like https://script.google.com/macros/s/AKfyc.../exec. That's your splitforms webhook destination. Drop it into the form's Webhooks → Add → Generic JSON in the splitforms dashboard.

Here's the full Apps Script — the entire endpoint, including the upgrades the basic tutorial skipped:

const SHEET_NAME = "Submissions";
const SHARED_SECRET = "PASTE_FROM_SPLITFORMS_FORM_SETTINGS"; // see HMAC section below
const TIMEZONE = "America/New_York"; // or "UTC", "Europe/London", etc.

// Column order — change this list to reorder/rename columns.
const COLUMNS = [
  "submitted_at",
  "name",
  "email",
  "subject",
  "message",
  "company",
  "spam_score",
  "submission_id",
  "ip",
  "referrer",
];

function doPost(e) {
  try {
    const raw = e.postData.contents;
    const sig = e.parameter.sig || (e.parameter && e.parameter["X-Splitforms-Signature"]);

    if (!verifyHmac_(raw, sig)) {
      return _json({ ok: false, error: "bad signature" }, 401);
    }

    const body = JSON.parse(raw);
    const fields = body.fields || body.data || {};

    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
    if (!sheet) return _json({ ok: false, error: "sheet not found" }, 500);

    ensureHeader_(sheet);

    const submittedAt = body.submitted_at
      ? new Date(body.submitted_at)
      : new Date();
    const formatted = Utilities.formatDate(submittedAt, TIMEZONE, "yyyy-MM-dd HH:mm:ss");

    const lookup = {
      submitted_at:  formatted,
      name:          fields.name      || "",
      email:         fields.email     || "",
      subject:       fields.subject   || "",
      message:       fields.message   || "",
      company:       fields.company   || "",
      spam_score:    body.spam_score  ?? "",
      submission_id: body.submission_id || "",
      ip:            body.ip          || "",
      referrer:      body.referrer    || "",
    };

    const row = COLUMNS.map((k) => lookup[k]);
    sheet.appendRow(row);

    return _json({ ok: true, row: sheet.getLastRow() }, 200);
  } catch (err) {
    return _json({ ok: false, error: String(err) }, 500);
  }
}

function ensureHeader_(sheet) {
  if (sheet.getLastRow() === 0) {
    sheet.appendRow(COLUMNS);
    sheet.setFrozenRows(1);
  }
}

function verifyHmac_(raw, sig) {
  if (!SHARED_SECRET) return true; // dev mode: skip verify
  if (!sig) return false;
  const mac = Utilities.computeHmacSha256Signature(raw, SHARED_SECRET);
  const expected = mac.map(function (b) {
    return ("0" + (b & 0xff).toString(16)).slice(-2);
  }).join("");
  return expected === sig;
}

function _json(obj, code) {
  return ContentService.createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

Save, deploy, and test from the terminal:

curl -X POST "https://script.google.com/macros/s/AKfyc.../exec?sig=test" \
  -H "Content-Type: application/json" \
  -d '{
    "submitted_at": "2026-05-02T12:00:00Z",
    "submission_id": "sub_abc",
    "spam_score": 0.04,
    "fields": {
      "name":    "Ada Lovelace",
      "email":   "ada@example.com",
      "subject": "Demo request",
      "message": "Hi — testing."
    }
  }'

You should see {"ok":true,"row":2} back, and a new row in your sheet with the date formatted in your selected timezone. Apps Script web apps run in 200-500ms typically — same speed class as a Cloudflare Worker.

Fixed column mapping

The COLUMNS constant is the most important detail. Most tutorials use Object.values(fields), which means: rename a form field, and your sheet's columns shift. Add a new field, and it appears in a random column. The fixed mapping above ensures every submission writes to the same column every time, regardless of what the form payload contains.

To add a new column, change COLUMNS and lookup together — both need to know about the new key. Re-deploy. The old rows stay where they are; new rows pick up the new column. To rename a column, change COLUMNS and update the header row in the sheet manually (or delete row 1 and let ensureHeader_ rewrite it on the next submission).

For forms with dozens of fields where you don't want to maintain the mapping by hand, change COLUMNS = Object.keys(lookup) and accept the column-drift trade-off. Most production setups want the explicit list.

Timezone formatting

Google Sheets stores datetime values in the spreadsheet's timezone (set under File → Settings → Time zone). Apps Script's default for new Date()objects is the script project's timezone. These can be different. The result is a row that looks correct to you and three hours off to your colleague in another office.

Utilities.formatDate(date, timeZone, format) sidesteps both. It takes a Date and emits a string in the timezone you specify. The trade-off: the cell becomes a text value, not a real datetime, so you lose the ability to sort by date as a date. Three options depending on what you need:

// Option 1 — text in your chosen TZ. Safe, but not date-sortable.
Utilities.formatDate(date, "America/New_York", "yyyy-MM-dd HH:mm:ss")

// Option 2 — real Date object, sorts correctly. Displayed in spreadsheet's TZ.
const cell = new Date(submission.submitted_at);
sheet.appendRow([cell, ...]);

// Option 3 — split into date and time columns. Best for filtering / pivot tables.
const d = new Date(submission.submitted_at);
const dateStr = Utilities.formatDate(d, TIMEZONE, "yyyy-MM-dd");
const timeStr = Utilities.formatDate(d, TIMEZONE, "HH:mm:ss");

The example script above uses Option 1 because contact form rows usually get filtered by humans reading the value, not sorted programmatically. If your sheet feeds a downstream pivot table or chart, switch to Option 2 and set the spreadsheet timezone explicitly under File → Settings.

Header preservation and formula rows

appendRow inserts at the bottom and never touches existing rows. The ensureHeader_ helper writes the header on the first call only — after that it's a no-op. Adding setFrozenRows(1) makes the header pin to the top while you scroll.

If your sheet has formula columns — say, a column =LEN(D2) computing the length of the message — they need to live above the data, or in a separate sheet pulling via =QUERY(Submissions!A:Z, ...). Formulas in column F won't auto-fill into newly-appended rows. The cleanest production setup is two sheets: a raw "Submissions" tab the script writes to, and a "Dashboard" tab with a single QUERY formula doing the analysis. The script never touches the dashboard, so formulas there can't break.

// In a "Dashboard" tab, cell A1:
=QUERY(Submissions!A:J, "select B, C, count(A) where A is not null group by B, C label count(A) 'Submissions'", 1)

HMAC verification inside Apps Script

The web app URL is public. Anyone who finds it can POST junk into your sheet. The fix is verifying every request is signed by the splitforms form's secret. splitforms includes an X-Splitforms-Signatureheader on every webhook — the HMAC-SHA256 of the request body using the form's secret as the key.

Apps Script can't read arbitrary headers from e directly — only query parameters and the body. The cleanest fix: configure splitforms to also pass the signature as a ?sig= query parameter on the webhook URL. (You can do this by configuring the webhook URL with ?sig=${signature} as a templated suffix.) Then e.parameter.sig contains it, and the verifyHmac_ helper above checks it against a freshly-computed HMAC of the body.

// Apps Script side — the helper from the main script:
function verifyHmac_(raw, sig) {
  if (!sig) return false;
  const mac = Utilities.computeHmacSha256Signature(raw, SHARED_SECRET);
  const expected = mac.map(function (b) {
    return ("0" + (b & 0xff).toString(16)).slice(-2);
  }).join("");
  return expected === sig;
}

// splitforms side — set the webhook URL to include sig as a query param:
//   https://script.google.com/macros/s/AKfyc.../exec?sig=${signature}
// where ${signature} is the standard splitforms HMAC token.

The shared secret lives in two places: the SHARED_SECRETconstant at the top of the Apps Script (visible only to people you've shared the script with), and the form's webhook secret in splitforms. They must match. Rotate by updating both.

For the full webhook envelope, retry policy, and signature spec, see the splitforms docs.

Method B — Make / Zapier route

If you don't have a developer or you don't want to maintain Apps Script, both Make.com and Zapier ship native Google Sheets connectors with a visual field mapper.

Make.com (~$9/mo or free for 1k ops/mo):

1. Create a scenario. Add a Webhooks → Custom webhook trigger. Make gives you a URL — paste into splitforms.
2. Run a test event from splitforms so Make learns the payload shape.
3. Add a Google Sheets → Add a Row action. Authorize Google. Pick your spreadsheet and tab.
4. Map each splitforms field onto a sheet column via the visual editor. Done.

Zapier (free 100 tasks/mo, $19.99/mo Starter):identical flow with "Webhooks by Zapier" as the trigger and "Google Sheets — Create Spreadsheet Row" as the action. The only meaningful difference is Zapier's free plan limit kicks in at 100 submissions/month vs Make's 1,000.

Trade-offs vs Apps Script: 5-30 seconds of latency vs ~200ms, an external dependency that can fail or run out of quota, and an eventual subscription cost. Apps Script is free forever and runs as fast as the Google Sheets API. For anything mission-critical, write the Apps Script. For a quick throwaway, use Make.

See the companion post Connect a form to Google Sheets without Zapier for the basic version of Method A. This advanced guide is the production-hardened upgrade — for full splitforms wiring see the contact form playbook.

Tech support and troubleshooting

Five issues account for almost every Apps Script form failure:

• Authorization required loop — The web app's Execute as setting is wrong. Re-deploy with Execute as 'Me' so the script runs under your Google account, not the requester.
• doPost returns HTML instead of JSON — An unhandled exception bubbles up as a Google error page. Wrap doPost in try/catch and return ContentService.createTextOutput on every path.
• Date column shows as text — You passed an ISO string instead of a Date object. Wrap with new Date(submitted_at) or use Utilities.formatDate with the spreadsheet timezone.
• Header row gets overwritten — ensureHeader_ writes the header only when getLastRow() === 0. If you cleared the sheet, restore the header manually or run the helper from the editor once.
• Random IPs spamming the sheet — Anyone with the URL can POST. Set SHARED_SECRET, configure splitforms to append ?sig=${signature}, and verifyHmac_ rejects unsigned requests.

Webhook signature, retry policy, and event payload spec live in the splitforms docs and the API reference. For account or billing questions see the splitforms FAQ.

FAQ

Next steps

• Add a real CRM or database — Airtable, Notion, or HubSpot.
• Get a chat ping when a row lands — Slack.
• Lock things down — webhook security guide and spam controls.
• Ship without a CMS — static HTML form backend.