Build an email-based approval system

Expense approvals stall when they need a manager to log into something. The manager sees the notification, means to deal with it later, and “later” turns into a chase three days on. Northwind wanted approvals to happen in the place managers already are — their inbox.

This automation keeps approvals out of yet another tool. The flow is simple: a request lands as a row in a Requests sheet, a script emails the approver with two links — Approve and Reject — and clicking either link hits a web app that flips the row’s status and stamps the decision time. No portal, no login, no app to install. The Sheet stays the single record of every decision.

What you’ll need

• A Requests sheet with a header row and these columns: id, requester, amount, description, approver, status, notifiedAt, decidedAt. New requests go in with status set to pending and id unique.
• An Apps Script web app deployed with Execute as: Me and Who has access: Anyone, even anonymous — approvers click the link straight from email, so the deployment must accept anonymous requests.
• The web app’s /exec URL, pasted into WEBAPP_URL in the notifier.

The notifier

// The spreadsheet that holds the expense requests.
const REQUESTS_SHEET = '1abcRequestsSheetId';

// The /exec URL of the deployed web app (see "The web app handler").
const WEBAPP_URL = 'https://script.google.com/macros/s/DEPLOYMENT_ID/exec';

/**
 * Emails the approver for every pending request that has not already
 * been notified, then stamps the notifiedAt column so the request is
 * never emailed twice.
 */
function emailPendingRequests() {
  const sheet = SpreadsheetApp.openById(REQUESTS_SHEET).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  if (!rows.length) {
    Logger.log('No requests — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so column order can change.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  let emailed = 0;
  rows.forEach((row, i) => {
    // 3. Only act on pending requests that have not been emailed yet.
    if (row[col.status] !== 'pending') return;
    if (row[col.notifiedAt]) return;

    // 4. Build the two decision links, each carrying the request id
    //    and the action.
    const id = row[col.id];
    const approve = `${WEBAPP_URL}?id=${id}&action=approve`;
    const reject = `${WEBAPP_URL}?id=${id}&action=reject`;

    // 5. Email the approver with both links in the body.
    GmailApp.sendEmail(
      row[col.approver],
      `Approval needed: ${row[col.description]}`,
      `Amount: $${row[col.amount]}\n` +
        `Requested by: ${row[col.requester]}\n\n` +
        `Approve: ${approve}\n` +
        `Reject:  ${reject}`);

    // 6. Stamp notifiedAt in the in-memory copy of the sheet.
    values[i + 1][col.notifiedAt] = new Date();
    emailed++;
  });

  // 7. Write the notifiedAt stamps back in one operation.
  if (emailed) sheet.getDataRange().setValues(values);
  Logger.log('Emailed ' + emailed + ' approval requests.');
}

The web app handler

/**
 * Web app entry point. Clicking an Approve or Reject link from the
 * notification email lands here. It finds the matching request row,
 * flips its status, and stamps the decision time.
 *
 * @param {Object} e  The event object Apps Script passes to doGet.
 */
function doGet(e) {
  const { id, action } = e.parameter;

  // 1. Reject anything that is not a valid id + known action.
  if (!id || !['approve', 'reject'].includes(action)) {
    return ContentService.createTextOutput('Invalid request.');
  }

  const sheet = SpreadsheetApp.openById(REQUESTS_SHEET).getSheets()[0];

  // 2. Read the sheet and map header names to column indexes.
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((h, i) => [h, i]));

  // 3. Find the row whose id matches the link that was clicked.
  const i = rows.findIndex((r) => String(r[col.id]) === String(id));
  if (i === -1) return ContentService.createTextOutput('Unknown request.');

  // 4. Don't let an already-decided request be flipped again.
  const current = rows[i][col.status];
  if (current !== 'pending') {
    return ContentService.createTextOutput(
      `This request was already ${current}.`);
  }

  // 5. Flip the status and stamp the decision time. Row index in the
  //    full values array is i + 1 because the header was sliced off.
  values[i + 1][col.status] = action === 'approve' ? 'approved' : 'rejected';
  values[i + 1][col.decidedAt] = new Date();
  sheet.getDataRange().setValues(values);

  return ContentService.createTextOutput(
    `Marked ${action}d. You can close this tab.`);
}

How it works

1. emailPendingRequests opens the Requests sheet, reads every row, and maps header names to column indexes so the code does not depend on column order.
2. For each row it acts only on requests whose status is pending and whose notifiedAt is still blank — that pairing is what stops the same request being emailed on every run.
3. It builds two URLs to the web app, each carrying the request id and an action of approve or reject, and emails both to the approver.
4. It writes the current time into notifiedAt so the request is skipped next time, then saves all the stamps back to the sheet in one write.
5. When the approver clicks a link, doGet runs. It validates the parameters, finds the matching row by id, and refuses anything already decided.
6. It flips status to approved or rejected, stamps decidedAt, saves the sheet, and shows the approver a short confirmation page.

Example run

A new request is added to the Requests sheet:

id	requester	amount	description	approver	status	notifiedAt	decidedAt
R-104	Sam Okafor	240	New studio mic	[email protected]	pending

Running emailPendingRequests sends this email to the approver:

Subject: Approval needed: New studio mic

Amount: $240
Requested by: Sam Okafor

Approve: https://script.google.com/.../exec?id=R-104&action=approve
Reject:  https://script.google.com/.../exec?id=R-104&action=reject

The approver clicks Approve. The web app shows “Marked approved. You can close this tab.” and the row updates:

id	…	status	notifiedAt	decidedAt
R-104	…	approved	2025-12-23 09:10	2025-12-23 11:32

Trigger it

The notifier should run automatically so new requests get emailed without anyone remembering to.

1. Deploy the web app first: Deploy > New deployment > Web app, with Execute as: Me and Who has access: Anyone, even anonymous. Copy the /exec URL into WEBAPP_URL.
2. In the Apps Script editor open Triggers (the clock icon).
3. Add a trigger for emailPendingRequests, time-driven, every 15 minutes or hourly — whatever matches how quickly requests should go out.
4. Approve the authorisation prompt the first time.

The web app handler needs no trigger — it runs whenever an approval link is clicked. If you redeploy and the URL changes, update WEBAPP_URL.

Watch out for

• Anyone who has a link can click it. The link carries only an id and an action — there is no check that the clicker is the approver. For sensitive approvals, add a signed token to the URL or require the approver’s Google account by deploying with restricted access.
• Email clients sometimes pre-fetch links for security scanning, which can trigger an approval no one intended. A confirmation page with a button — rather than acting on the bare doGet — avoids this.
• The handler reads and rewrites the entire sheet with setValues. If two links are clicked at the same instant, one write can overwrite the other — use LockService around the read-modify-write for a busy queue.
• The “already decided” guard means a second click reports the existing status instead of flipping it again, so re-clicking an old email is harmless.
• Gmail has a daily send quota. A large backlog emailed at once can hit it — the notifiedAt guard means the rest go out on the next trigger run.