Build a multi-stage approval form workflow

Northwind’s purchase requests need two sets of eyes before money moves: a line manager reviews, then a budget owner signs off. That used to happen over email, where requests stalled in inboxes and nobody could say which stage a given one had reached.

This workflow chains two Google Forms and a shared tracking sheet. Submitting the request form logs it, marks it submitted, and emails the reviewer a link to the review form with the request ID prefilled. When the reviewer responds, the tracking sheet advances to reviewed or rejected. One row per request gives everyone a single, honest view of where each one stands.

What you’ll need

• A request form — the entry point — with fields including Requester and Reviewer.
• A review form — separate — with an id field (prefilled via the URL) and a Decision field offering approve / reject.
• A Requests Google Sheet with columns: id, requester, stage (submitted / reviewed / rejected / approved), reviewer, approver.
• Both forms’ response sheets, so you can attach the submit triggers.

The trigger (on the first form’s response sheet)

// Base URL of the review form. The request ID is appended as ?id=...
const REVIEW_FORM = 'https://forms.gle/REVIEW_FORM_ID';

// Spreadsheet that tracks every request through its stages.
const REQUESTS_SHEET_ID = '1abcRequestsId';

/**
 * Runs when the request form is submitted. Logs the request,
 * marks it "submitted", and emails the reviewer a prefilled link
 * to the review form.
 *
 * @param {Object} e - The form-submit event object.
 */
function onFormSubmit(e) {
  // 1. Give the request a stable ID that survives across both forms.
  const id = Utilities.getUuid();
  const requester = e.namedValues.Requester[0];
  const reviewer = e.namedValues.Reviewer[0];

  // 2. Log the request at the first stage. Approver is filled in later.
  SpreadsheetApp.openById(REQUESTS_SHEET_ID).getSheets()[0]
    .appendRow([id, requester, 'submitted', reviewer, '']);

  // 3. Email the reviewer a link to the review form with the ID prefilled.
  GmailApp.sendEmail(reviewer, 'Review needed',
    `${requester} submitted a request. Review here: ${REVIEW_FORM}?id=${id}`);
}

On the review form (separate, with id prefilled)

// Same tracking spreadsheet used by the request form trigger.
const REQUESTS_SHEET_ID = '1abcRequestsId';

/**
 * Runs when the review form is submitted. Finds the request by ID
 * and advances its stage based on the reviewer's decision.
 *
 * @param {Object} e - The form-submit event object.
 */
function onReviewSubmit(e) {
  // 1. Read the ID (prefilled in the link) and the reviewer's decision.
  const id = e.namedValues.id[0];
  const decision = e.namedValues.Decision[0];

  const sheet = SpreadsheetApp.openById(REQUESTS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();

  // 2. Locate the row for this request ID.
  const row = values.findIndex((r) => r[0] === id);
  if (row === -1) {
    Logger.log(`No request found for id ${id} — skipping.`);
    return;
  }

  // 3. Advance the stage: approved at review becomes "reviewed".
  values[row][2] = decision === 'approve' ? 'reviewed' : 'rejected';
  sheet.getDataRange().setValues(values);
  Logger.log(`Request ${id} is now ${values[row][2]}.`);
}

How it works

1. onFormSubmit fires on the request form’s response sheet. It mints a UUID so the same request can be referenced from the second form.
2. It appends a row to Requests at stage submitted, recording the requester and reviewer. The approver column is left blank for the final stage.
3. It emails the reviewer a link to the review form with ?id=<uuid> appended, so the reviewer’s response carries the request ID with it.
4. onReviewSubmit fires on the review form’s response sheet. It reads the prefilled id and the Decision.
5. It finds the matching row by ID. If none is found — a stray submission, a mistyped link — it logs and stops rather than corrupting the sheet.
6. It advances the stage: approve moves the request to reviewed, anything else sets rejected. A reviewed request is then ready for the sign-off stage.

Example run

The Requests sheet after a request is submitted:

id	requester	stage	reviewer	approver
7f3a…	Priya	submitted	manager@northwind

After the reviewer approves on the review form:

id	requester	stage	reviewer	approver
7f3a…	Priya	reviewed	manager@northwind

The reviewer never touched the sheet — they clicked the emailed link, picked approve, and the stage advanced on its own.

Trigger it

Each form needs an installable submit trigger:

1. Open the request form’s bound script. Go to Triggers, add a trigger for onFormSubmit, event source From spreadsheet, type On form submit.
2. Open the review form’s bound script and do the same for onReviewSubmit.
3. Approve the authorisation prompts. Submit a test request to confirm the row appears and the reviewer email arrives.

Watch out for

• Form-submit triggers must be installable, added through the Triggers panel. A plain onFormSubmit function on its own only runs as a simple trigger, which cannot send email.
• getDataRange().setValues() rewrites the whole sheet. If two reviews land in the same instant, one can overwrite the other’s change. For higher volume, update just the single changed row with getRange(row + 1, 3).setValue(...).
• The sign-off stage is left as an exercise — add a third form and an onApprovalSubmit handler that moves reviewed rows to approved and fills the approver column, mirroring the review step.
• e.namedValues keys must match the form question titles exactly, including capitalisation. Rename a question and the handler breaks silently.
• A prefilled ?id= link can be edited by anyone who has it. For a sensitive workflow, validate that the submitter is the expected reviewer before advancing the stage.