Pipeline Stage Automation: Copy-Paste Apps Script Pattern

What this script does

Prerequisites

Walkthrough

Edge cases

How to test

Hardening for production

Variations

Full code: pipelineStageAutomation()

Maintain checklist columns, then run pipelineStageAutomation() on a trigger or after bulk edits.

/**
 * Move deals to the next stage when checklist columns are complete.
 */
const DEALS = "Deals";
// A id, B stage, C discoveryDone, D demoDone, E proposalSent, F lastTransition

const STAGE_ORDER = ["New", "Discovery", "Demo", "Proposal", "Won"];

function pipelineStageAutomation() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(DEALS);
  const values = sheet.getDataRange().getValues();

  for (let i = 1; i < values.length; i++) {
    let stage = values[i][1];
    const discovery = truthy_(values[i][2]);
    const demo = truthy_(values[i][3]);
    const proposal = truthy_(values[i][4]);
    let next = stage;

    if (stage === "New" && discovery) next = "Discovery";
    else if (stage === "Discovery" && demo) next = "Demo";
    else if (stage === "Demo" && proposal) next = "Proposal";
    // Won is manual or via deal-won flow

    if (next !== stage) {
      values[i][1] = next;
      values[i][5] = new Date();
    }
  }
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

function truthy_(v) {
  return v === true || v === "TRUE" || v === "Yes" || v === "y" || v === 1;
}

1. Line 7: Documents allowed stages (Won manual).
2. Line 15: Normalizes checkbox/Yes values.
3. Line 25: Only writes when advancing.
4. Line 5: Column F timestamp.
5. Line 30: Batch-writes stage updates.