Build a dependent dropdown system

A flat dropdown of every project Northwind has ever run is no help when you are filling in a timesheet. Pick the wrong one, log time against the wrong client, and the invoice is wrong too. The fix everyone wants is the obvious one: choose a client, and the project dropdown should only show that client’s projects.

Sheets has no built-in dependent dropdown, but an onEdit trigger gets you there. When someone picks a client, the script looks up that client’s projects and rewrites the data-validation rule on the project cell next to it. The second dropdown is always scoped to the first selection.

What you’ll need

• A Timesheets sheet where the client goes in column B and the project in column C.
• A Lookups sheet with two columns — client and project — listing every valid client/project pair, one per row.
• An installable onEdit trigger (set up below). The simple trigger cannot be used here because the script reads another spreadsheet.

The script

// The spreadsheet that holds the client -> project pairs.
const LOOKUPS_SHEET_ID = '1abcLookupsSheetId';

// Column numbers on the Timesheets sheet (1-based).
const CLIENT_COL = 2;   // column B — the dropdown you pick first
const PROJECT_COL = 3;  // column C — the dropdown that gets filtered

// The tab the dependent dropdown applies to.
const TARGET_SHEET = 'Timesheets';

/**
 * Installable onEdit handler. When a client is chosen in column B,
 * rewrites the project dropdown in column C to that client's projects.
 *
 * @param {Object} e  The edit event passed in by the trigger.
 */
function onEdit(e) {
  // Guard: ignore calls with no event or range (e.g. manual runs).
  if (!e || !e.range) return;

  const sheet = e.range.getSheet();

  // Only react to edits in column B of the Timesheets tab.
  if (sheet.getName() !== TARGET_SHEET || e.range.getColumn() !== CLIENT_COL) {
    return;
  }

  const client = e.value;

  // Guard: a cleared client cell means there is nothing to filter on.
  if (!client) return;

  // Look up the projects for this client.
  const projects = getProjectsFor(client);

  // Build a fresh data-validation rule from that project list.
  const cell = sheet.getRange(e.range.getRow(), PROJECT_COL);
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(projects, true)  // true = show as a dropdown
    .setAllowInvalid(false)              // reject anything off the list
    .build();

  // Apply the rule and clear any stale project from the old client.
  cell.setDataValidation(rule);
  cell.clearContent();
}

/**
 * Returns the list of projects belonging to one client,
 * read from the Lookups sheet.
 *
 * @param {string} client  The client name to filter by.
 * @return {string[]}      That client's project names.
 */
function getProjectsFor(client) {
  const [, ...rows] = SpreadsheetApp.openById(LOOKUPS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  return rows
    .filter((row) => row[0] === client)  // match the client column
    .map((row) => row[1]);               // keep the project column
}

How it works

1. The installable onEdit trigger fires onEdit after every edit and passes an event object describing what changed.
2. The handler bails out unless the edit was a real range edit in column B of the Timesheets tab — every other edit on the sheet is ignored.
3. It reads the newly entered client from e.value. If the cell was cleared, there is nothing to filter on, so it stops.
4. getProjectsFor opens the Lookups sheet, drops the header row, keeps only rows whose first column matches the client, and returns their project names.
5. The handler builds a new data-validation rule with requireValueInList, passing the filtered project list. setAllowInvalid(false) blocks any value not on the list.
6. It applies that rule to column C of the same row, then clears the cell — so a project left over from a previous client cannot stay behind invalidly.

Example run

The Lookups sheet holds:

client	project
Atlas Foods	Q3 rebrand
Atlas Foods	Packaging refresh
Borealis	Website build

A user opens a timesheet row and picks Atlas Foods in column B. The script runs and the column C dropdown immediately offers only:

• Q3 rebrand
• Packaging refresh

Change column B to Borealis and column C clears, then re-opens showing just Website build. The project list can never contradict the chosen client.

Wire it up

The simple onEdit cannot open another spreadsheet, so an installable trigger is required:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose the onEdit function, event source From spreadsheet, event type On edit.
4. Save and approve the authorisation prompt — installable triggers run with full permissions, which is what lets the script read the Lookups sheet.

Watch out for

• The simple trigger is not enough. A function named onEdit runs as a simple trigger by default, but simple triggers cannot call openById. You must add the installable trigger above, or the lookup silently fails.
• e.value is only set for single-cell edits. A paste or fill across column B will not carry a e.value, so those rows are skipped — paste with care.
• Changing the client clears column C. That is intentional, but a user who re-picks the same client by accident still loses their project entry.
• Every client edit reads the whole Lookups sheet. For a large lookup table this adds latency to each edit; cache the projects with CacheService if it feels sluggish.
• New client/project pairs must be added to Lookups to appear. The dropdown is only ever as current as that sheet.