Build a form-driven expense claim system

Expense claims at Northwind used to mean a thread of emails, a photo of a receipt, and a finance assistant copying numbers into a spreadsheet by hand. Claims got lost, amounts were mistyped, and nobody could say what was still waiting on approval.

A Google Form fixes the front end: anyone can file a claim in a minute, and a file-upload question stores the receipt in Drive automatically. This script handles the back end. The moment a form is submitted, it stamps the claim with a unique ID, logs it to a tracking sheet with a pending status, and emails finance the details so an approver can act straight away. It is the first half of a claim-to-payment workflow — capture and notify — with the approval status ready to drive whatever comes next.

What you’ll need

• A Google Form with these questions, named exactly: Claimant, Amount, Category, and a file-upload question named Receipt. A file-upload question requires the form to be restricted to your organisation.
• A Google Sheet to track claims. The script appends rows with these columns: id, claimant, category, amount, receiptUrl, status, submittedAt. A header row is optional but helps.
• The Sheet’s ID, taken from its URL.
• A finance address that should be notified of each new claim.

The script

// The spreadsheet that logs every claim.
const CLAIMS_SHEET_ID = '1abcClaimsId';

// Who gets told when a new claim comes in.
const FINANCE_EMAIL = '[email protected]';

/**
 * Runs on every Form submission. Records the claim in the tracking sheet
 * with a "pending" status and emails finance the details.
 *
 * @param {Object} e The Form-submit event, with answers in e.namedValues.
 */
function onFormSubmit(e) {
  // 1. Guard: a manual run with no event would have no answers.
  if (!e || !e.namedValues) {
    Logger.log('No form data — run this from a Form-submit trigger.');
    return;
  }

  // 2. Pull each answer out of the event. namedValues keys are the
  //    question titles; each value is an array, so take the first entry.
  const id = Utilities.getUuid();
  const claimant = e.namedValues.Claimant[0];
  const amount = parseFloat(e.namedValues.Amount[0]);
  const category = e.namedValues.Category[0];

  // The Receipt question may be unanswered, so fall back to an empty string.
  const receiptUrl = e.namedValues.Receipt?.[0] || '';

  // 3. Append the claim to the tracking sheet with a "pending" status.
  SpreadsheetApp.openById(CLAIMS_SHEET_ID).getSheets()[0]
    .appendRow([id, claimant, category, amount, receiptUrl,
      'pending', new Date()]);

  // 4. Email finance so an approver can act on the claim.
  GmailApp.sendEmail(
    FINANCE_EMAIL,
    'Expense claim: £' + amount + ' (' + category + ')',
    'From ' + claimant + '. Receipt: ' + receiptUrl + '\nID: ' + id
  );

  Logger.log('Logged claim ' + id + ' from ' + claimant + '.');
}

How it works

1. onFormSubmit runs automatically whenever the linked Form receives a submission. It first checks the event object exists, so a stray manual run in the editor fails cleanly instead of throwing.
2. It generates a unique claim ID with Utilities.getUuid — a stable handle that the row, the email and any later approval step can all refer to.
3. It reads the answers from e.namedValues, keyed by the question titles. Every value is an array, so each answer is taken with [0]. The Receipt answer uses optional chaining and an empty-string fallback in case the upload question was left blank.
4. It appends a row to the tracking sheet: the ID, claimant, category, amount, the Drive link to the receipt, a pending status and a timestamp.
5. It emails finance a short summary with the amount in the subject line, so an approver can triage from the inbox before opening the sheet.

Example run

A designer submits the Form:

Question	Answer
Claimant	Priya Shah
Amount	64.00
Category	Software
Receipt	(uploaded PDF)

The script appends this row to the claims sheet:

id	claimant	category	amount	receiptUrl	status	submittedAt
7f3a…e21	Priya Shah	Software	64	drive.google.com/…	pending	2026-05-24 10:12

And finance receives:

Subject: Expense claim: £64 (Software) From Priya Shah. Receipt: https://drive.google.com/… ID: 7f3a…e21

The approver opens the receipt link, checks the claim, and changes the status cell to approved or rejected — the hook the rest of the workflow builds on.

Run it

This automation is event-driven: it should fire on each Form submission, not on a schedule.

1. Open the script bound to (or linked with) your Form.
2. In the editor, open Triggers (the clock icon) and click Add Trigger.
3. Choose the onFormSubmit function, an event source of From form, and an event type of On form submit.
4. Save and approve the authorisation prompt.
5. File a test claim through the Form and confirm a row appears and the email arrives.

Watch out for

• Question names must match exactly. e.namedValues.Claimant fails if the question is titled “Claimant name” or “claimant”. Rename the questions or the keys so they line up.
• File-upload questions only work on Forms restricted to your Google Workspace organisation, and the uploaded receipt lands in the form owner’s Drive — make sure approvers have access to that folder.
• parseFloat will return NaN if someone types “£64” or “64 GBP” in the Amount field. Set the Amount question to validate as a number, or sanitise the value before parsing.
• This script captures and notifies but does not move the claim through approval. Wire the next step to the status column — for example, a separate function that watches for rows changing to approved and queues them for payment.
• Gmail’s daily send limit applies. Normal claim volume is nowhere near it, but keep it in mind if claims ever spike.