Validate, highlight, and react to Sheets edits

Add rules to a small task table so people pick a status from a dropdown, tick a checkbox, and enter dates and hours that Sheets can check. Then highlight overdue and blocked tasks, and write a timestamp when someone changes a task's status. Each part of the script changes only the rules it created, so formatting and validation that people added by hand stay in place.

You need the batching pattern from Read and write Sheets in batches: read a range once, decide in JavaScript, write once. This chapter adds three kinds of spreadsheet state that are not cell values: data validation rules, conditional format rules, and a protected range. It also adds a function that Sheets runs on its own when a person edits a cell.

Set up a disposable task table

Use a new spreadsheet for this lesson. The script refuses to touch rules it did not create, but a practice file keeps your own trackers out of the experiment.

1. Create a spreadsheet. Rename the first tab Tasks and add a second tab named Lists.

2. Copy the text of sample.csv into Tasks!A1. Select column A, choose Data > Split text to columns, and set the separator to Comma. Check that you have five rows by eight columns.

3. Copy the text of owners.csv into Lists!A1 and split it the same way. Column A should read Owner, Alex, Sam, Jo.

4. Open Extensions > Apps Script. Replace the editor contents with Code.gs. In Project Settings, select Show "appsscript.json" manifest file in editor, then replace that file with the supplied manifest.

Task ID,Title,Owner,Status,Due date,Hours,Urgent,Last changed
T-100,Draft the agenda,Alex,New,2020-01-15,2,,
T-101,Book the room,Sam,In progress,2099-12-31,1,,
T-102,Collect slides,Jo,Blocked,2099-12-31,6,,
T-103,Send the recap,Alex,Done,2020-01-20,3,,

Owner
Alex
Sam
Jo

The Tasks header must stay exactly as supplied:

Due date should split into date values. T-100 has a due date in 2020 so the overdue rule has something to show. Urgent and Last changed start blank.

const SHEET_NAME = 'Tasks'; // The tab that holds the task table.
const LISTS_SHEET_NAME = 'Lists'; // The tab whose column A holds the owner names.
const HEADER = ['Task ID', 'Title', 'Owner', 'Status', 'Due date', 'Hours', 'Urgent', 'Last changed'];
const FIRST_DATA_ROW = 2; // Row 1 is the header.
const RULE_ROWS = 200; // Rules cover rows 2 to 201.
const STATUSES = ['New', 'In progress', 'Blocked', 'Done'];
const MAX_HOURS = 40; // Largest accepted estimate in the Hours column.
const HELP_PREFIX = 'Task tracker: '; // Marks validation rules this script created.
const PROTECTION_DESCRIPTION = 'Task tracker header'; // Marks the header protection.

const OWNER_COLUMN = 3;
const STATUS_COLUMN = 4;
const DUE_COLUMN = 5;
const HOURS_COLUMN = 6;
const URGENT_COLUMN = 7;
const LAST_CHANGED_COLUMN = 8;

/**
 * Checks the Tasks table, then applies this script's validation rules,
 * conditional format rules, timestamp format, and header warning.
 * Rules that this script did not create are kept.
 *
 * @returns {void}
 */
function applyTaskRules() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = getTasksSheet_(spreadsheet);
  checkHeader_(sheet);
  if (sheet.getMaxRows() < FIRST_DATA_ROW + RULE_ROWS - 1) {
    throw new Error('Tasks needs at least ' + (FIRST_DATA_ROW + RULE_ROWS - 1) + ' rows. Add rows at the bottom of the tab.');
  }

  const ownerRange = getOwnerListRange_(spreadsheet);
  const ownerNames = ownerRange.getValues().map(function (row) {
    return row[0];
  });
  checkExistingValues_(sheet, ownerNames);

  const validations = buildValidationRules_(ownerRange);
  validations.forEach(function (item) {
    checkValidationOwnership_(sheet, item.column);
  });

  // Every check has passed. The writes start here.
  validations.forEach(function (item) {
    sheet.getRange(FIRST_DATA_ROW, item.column, RULE_ROWS, 1).setDataValidation(item.rule);
  });
  const formatResult = replaceOwnedFormatRules_(sheet);
  sheet.getRange(FIRST_DATA_ROW, LAST_CHANGED_COLUMN, RULE_ROWS, 1).setNumberFormat('yyyy-mm-dd hh:mm');
  const protectionCreated = protectHeaderWithWarning_(sheet);

  Logger.log('Applied ' + validations.length + ' validation rules.');
  Logger.log('Kept ' + formatResult.kept + ' conditional format rule(s) this script does not own; replaced ' +
    formatResult.replaced + ' owned rule(s) with ' + formatResult.added + '.');
  Logger.log(protectionCreated ? 'Added a warning-only header protection.' : 'Header protection already present.');
}

/**
 * Removes only the validation rules, conditional format rules, and header
 * protection that applyTaskRules created. Cell values are not changed.
 *
 * @returns {void}
 */
function removeTaskRules() {
  const sheet = getTasksSheet_(SpreadsheetApp.getActiveSpreadsheet());
  const columns = [OWNER_COLUMN, STATUS_COLUMN, DUE_COLUMN, HOURS_COLUMN, URGENT_COLUMN];
  columns.forEach(function (column) {
    checkValidationOwnership_(sheet, column);
  });

  columns.forEach(function (column) {
    sheet.getRange(FIRST_DATA_ROW, column, RULE_ROWS, 1).setDataValidation(null);
  });

  const ownedSignatures = buildFormatRules_(sheet).map(ruleSignature_);
  const kept = sheet.getConditionalFormatRules().filter(function (rule) {
    return ownedSignatures.indexOf(ruleSignature_(rule)) === -1;
  });
  sheet.setConditionalFormatRules(kept);

  sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(function (protection) {
    if (protection.getDescription() === PROTECTION_DESCRIPTION) {
      protection.remove();
    }
  });
  Logger.log('Removed this script\'s rules. Kept ' + kept.length + ' other conditional format rule(s).');
}

/**
 * Simple trigger. When a person edits Status in a task row, writes the
 * current time into that row's Last changed cell.
 *
 * @param {Events.SheetsOnEdit} e
 * @returns {void}
 */
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== SHEET_NAME) {
    return;
  }

  const firstColumn = range.getColumn();
  const lastColumn = range.getLastColumn();
  if (firstColumn > STATUS_COLUMN || lastColumn < STATUS_COLUMN) {
    return; // The edit did not touch Status.
  }

  const firstRow = Math.max(range.getRow(), FIRST_DATA_ROW);
  const lastRow = Math.min(range.getLastRow(), sheet.getLastRow());
  if (firstRow > lastRow) {
    return; // Header-only edit, or an edit below the table.
  }

  const isSingleCell = range.getNumRows() === 1 && range.getNumColumns() === 1;
  if (isSingleCell && e.value === e.oldValue) {
    return; // The same value was entered again.
  }

  const header = sheet.getRange(1, 1, 1, HEADER.length).getValues()[0];
  if (header[STATUS_COLUMN - 1] !== 'Status' || header[LAST_CHANGED_COLUMN - 1] !== 'Last changed') {
    return; // Columns moved. Do not guess where the timestamp belongs.
  }

  const rowCount = lastRow - firstRow + 1;
  const rows = sheet.getRange(firstRow, 1, rowCount, HEADER.length).getValues();
  const now = new Date();
  const stamps = rows.map(function (row) {
    const hasTaskId = row[0] !== '';
    return [hasTaskId ? now : row[LAST_CHANGED_COLUMN - 1]];
  });
  sheet.getRange(firstRow, LAST_CHANGED_COLUMN, rowCount, 1).setValues(stamps);
}

/**
 * @param {SpreadsheetApp.Spreadsheet} spreadsheet
 * @returns {SpreadsheetApp.Sheet}
 */
function getTasksSheet_(spreadsheet) {
  const sheet = spreadsheet.getSheetByName(SHEET_NAME);
  if (!sheet) {
    throw new Error('Tasks sheet is missing. Import sample.csv into a sheet named Tasks.');
  }
  return sheet;
}

/**
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {void}
 */
function checkHeader_(sheet) {
  const header = sheet.getRange(1, 1, 1, HEADER.length).getValues()[0];
  const matches = header.every(function (value, index) {
    return value === HEADER[index];
  });
  if (!matches) {
    throw new Error('Tasks must use this header row: ' + HEADER.join(', ') + '.');
  }
}

/**
 * Returns Lists!A2:A<last> after checking that it holds at least one name.
 *
 * @param {SpreadsheetApp.Spreadsheet} spreadsheet
 * @returns {SpreadsheetApp.Range}
 */
function getOwnerListRange_(spreadsheet) {
  const lists = spreadsheet.getSheetByName(LISTS_SHEET_NAME);
  if (!lists || lists.getRange(1, 1).getValue() !== 'Owner') {
    throw new Error('Lists sheet with Owner in A1 is missing. Import owners.csv into a sheet named Lists.');
  }
  const lastRow = lists.getLastRow();
  if (lastRow < 2) {
    throw new Error('Lists needs at least one owner name below A1.');
  }
  return lists.getRange(2, 1, lastRow - 1, 1);
}

/**
 * Stops before any rule changes if existing task rows would break a rule.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {Array<*>} ownerNames
 * @returns {void}
 */
function checkExistingValues_(sheet, ownerNames) {
  const lastRow = sheet.getLastRow();
  if (lastRow > FIRST_DATA_ROW + RULE_ROWS - 1) {
    throw new Error('Tasks has more than ' + RULE_ROWS + ' data rows. Increase RULE_ROWS first.');
  }
  if (lastRow < FIRST_DATA_ROW) {
    return;
  }

  const rows = sheet.getRange(FIRST_DATA_ROW, 1, lastRow - 1, HEADER.length).getValues();
  rows.forEach(function (row, index) {
    const rowNumber = index + FIRST_DATA_ROW;
    const owner = row[OWNER_COLUMN - 1];
    const status = row[STATUS_COLUMN - 1];
    const due = row[DUE_COLUMN - 1];
    const hours = row[HOURS_COLUMN - 1];
    const urgent = row[URGENT_COLUMN - 1];

    if (owner !== '' && ownerNames.indexOf(owner) === -1) {
      throw new Error('Row ' + rowNumber + ': owner "' + owner + '" is not listed on the Lists tab.');
    }
    if (status !== '' && (typeof status !== 'string' || STATUSES.indexOf(status) === -1)) {
      throw new Error('Row ' + rowNumber + ': status "' + status + '" is not one of ' + STATUSES.join(', ') + '.');
    }
    if (due !== '' && !(due instanceof Date)) {
      throw new Error('Row ' + rowNumber + ': Due date is not a date value.');
    }
    if (hours !== '' && (typeof hours !== 'number' || hours < 0 || hours > MAX_HOURS)) {
      throw new Error('Row ' + rowNumber + ': Hours must be a number from 0 to ' + MAX_HOURS + '.');
    }
    if (urgent !== '' && typeof urgent !== 'boolean') {
      throw new Error('Row ' + rowNumber + ': Urgent must be blank, TRUE, or FALSE.');
    }
  });
}

/**
 * @param {SpreadsheetApp.Range} ownerRange
 * @returns {Array<{column: number, rule: SpreadsheetApp.DataValidation}>}
 */
function buildValidationRules_(ownerRange) {
  return [
    {
      column: OWNER_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireValueInRange(ownerRange, true)
        .setAllowInvalid(false)
        .setHelpText(HELP_PREFIX + 'choose an owner listed on the Lists tab.')
        .build()
    },
    {
      column: STATUS_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireValueInList(STATUSES, true)
        .setAllowInvalid(false)
        .setHelpText(HELP_PREFIX + 'choose ' + STATUSES.join(', ') + '.')
        .build()
    },
    {
      column: DUE_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireDate()
        .setAllowInvalid(true)
        .setHelpText(HELP_PREFIX + 'enter a date. Other values show a warning.')
        .build()
    },
    {
      column: HOURS_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireNumberBetween(0, MAX_HOURS)
        .setAllowInvalid(false)
        .setHelpText(HELP_PREFIX + 'enter hours from 0 to ' + MAX_HOURS + '.')
        .build()
    },
    {
      column: URGENT_COLUMN,
      rule: SpreadsheetApp.newDataValidation()
        .requireCheckbox()
        .setHelpText(HELP_PREFIX + 'tick for urgent tasks.')
        .build()
    }
  ];
}

/**
 * Throws if any cell in the column's rule rows has a validation rule whose
 * help text does not start with HELP_PREFIX.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @param {number} column
 * @returns {void}
 */
function checkValidationOwnership_(sheet, column) {
  const rules = sheet.getRange(FIRST_DATA_ROW, column, RULE_ROWS, 1).getDataValidations();
  rules.forEach(function (row, index) {
    const rule = row[0];
    if (rule && (rule.getHelpText() || '').indexOf(HELP_PREFIX) !== 0) {
      throw new Error('Column ' + HEADER[column - 1] + ', row ' + (index + FIRST_DATA_ROW) +
        ' already has a validation rule this script did not create. Nothing was changed.');
    }
  });
}

/**
 * The conditional format rules this script owns, in priority order.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {SpreadsheetApp.ConditionalFormatRule[]}
 */
function buildFormatRules_(sheet) {
  const tableRange = sheet.getRange(FIRST_DATA_ROW, 1, RULE_ROWS, HEADER.length);
  const statusRange = sheet.getRange(FIRST_DATA_ROW, STATUS_COLUMN, RULE_ROWS, 1);
  const hoursRange = sheet.getRange(FIRST_DATA_ROW, HOURS_COLUMN, RULE_ROWS, 1);

  const overdue = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=AND($E2<>"", $E2<TODAY(), $D2<>"Done")')
    .setFontColor('#b3261e')
    .setBold(true)
    .setRanges([tableRange])
    .build();
  const blocked = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Blocked')
    .setBackground('#fce8e6')
    .setRanges([statusRange])
    .build();
  const done = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('Done')
    .setBackground('#e6f4ea')
    .setFontColor('#5f6368')
    .setRanges([statusRange])
    .build();
  const hoursGradient = SpreadsheetApp.newConditionalFormatRule()
    .setGradientMinpointWithValue('#ffffff', SpreadsheetApp.InterpolationType.NUMBER, '0')
    .setGradientMaxpointWithValue('#f6b26b', SpreadsheetApp.InterpolationType.NUMBER, String(MAX_HOURS))
    .setRanges([hoursRange])
    .build();

  return [overdue, blocked, done, hoursGradient];
}

/**
 * Keeps every rule this script does not own, then appends fresh copies of
 * the owned rules. Kept rules stay first, so they keep priority.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {{kept: number, replaced: number, added: number}}
 */
function replaceOwnedFormatRules_(sheet) {
  const ownedRules = buildFormatRules_(sheet);
  const ownedSignatures = ownedRules.map(ruleSignature_);
  const existing = sheet.getConditionalFormatRules();
  const kept = existing.filter(function (rule) {
    return ownedSignatures.indexOf(ruleSignature_(rule)) === -1;
  });
  sheet.setConditionalFormatRules(kept.concat(ownedRules));
  return { kept: kept.length, replaced: existing.length - kept.length, added: ownedRules.length };
}

/**
 * Describes a rule by its kind and ranges, plus its text for text rules.
 * Rules this script does not recognize get an empty signature.
 *
 * @param {SpreadsheetApp.ConditionalFormatRule} rule
 * @returns {string}
 */
function ruleSignature_(rule) {
  const ranges = rule.getRanges().map(function (range) {
    return range.getA1Notation();
  }).join(',');
  if (rule.getGradientCondition()) {
    return 'gradient|' + ranges;
  }
  const condition = rule.getBooleanCondition();
  if (!condition) {
    return '';
  }
  const type = condition.getCriteriaType();
  if (type === SpreadsheetApp.BooleanCriteria.TEXT_EQUAL_TO) {
    return 'text|' + ranges + '|' + condition.getCriteriaValues().join(',');
  }
  if (type === SpreadsheetApp.BooleanCriteria.CUSTOM_FORMULA) {
    return 'formula|' + ranges;
  }
  return '';
}

/**
 * Adds a warning-only protection to the header row once.
 *
 * @param {SpreadsheetApp.Sheet} sheet
 * @returns {boolean} true when a protection was created.
 */
function protectHeaderWithWarning_(sheet) {
  const existing = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).filter(function (protection) {
    return protection.getDescription() === PROTECTION_DESCRIPTION;
  });
  if (existing.length > 0) {
    return false;
  }
  sheet.getRange(1, 1, 1, HEADER.length)
    .protect()
    .setDescription(PROTECTION_DESCRIPTION)
    .setWarningOnly(true);
  return true;
}

{
  "timeZone": "Etc/UTC",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly"
  ]
}

The manifest requests spreadsheets.currentonly, which lets the manual functions read and change only the spreadsheet the project is bound to. Run applyTaskRules once and approve that access. The later sections explain what the run did.

Check the table before adding rules

applyTaskRules does every check before its first write:

• The Tasks tab exists and row 1 matches the eight headers in order.

• The tab has at least 201 rows, because the rules cover rows 2 to 201.

• Lists!A1 reads Owner and at least one name sits below it.

• Every existing row already satisfies the rules about to be applied: owners appear on the Lists tab, statuses are one of the four allowed values, due dates are dates, hours are numbers from 0 to 40, and Urgent is blank, TRUE, or FALSE.

• No cell in the target columns carries a validation rule that this script did not create.

If any check fails, the function throws an error naming the row or column and changes nothing. Checking existing values first matters because a new rule describes what future entries must look like. If the current data already breaks it, you would have a sheet full of warnings and no record of which values were wrong before the script arrived.

The rule rows are fixed at RULE_ROWS = 200. A fixed block keeps every range the script owns predictable, which the later ownership checks depend on. If your table grows past row 201, the check stops the run and asks you to raise the constant first.

Build data validation rules

A data validation rule tells Sheets which values a cell accepts. You create one with SpreadsheetApp.newDataValidation(), which returns a DataValidationBuilder. Each require... method sets the criteria, other methods set options, and build() returns the finished DataValidation. You attach it with Range.setDataValidation(rule), which applies the same rule to every cell in the range. See the DataValidationBuilder reference and the Range reference.

buildValidationRules_ creates five rules, one per column:

Dropdowns from values and from a range

requireValueInList(values, showDropdown) requires the input to equal one of the given strings. requireValueInRange(range, showDropdown) requires it to equal a value in a range. Passing true as the second argument shows the dropdown arrow; false keeps the rule and hides the arrow. Both are documented in the DataValidationBuilder reference.

Choose between them by who maintains the list. The four statuses belong to the script: onEdit and the conditional format rules depend on those exact words, so the list lives in code as STATUSES. The owner names belong to the people using the sheet. Keeping them on the Lists tab lets someone add a name without editing the script. The range is read when applyTaskRules runs, so after you add a name below Jo, run applyTaskRules again to extend the range to the new last row.

Checkboxes, dates, and numbers

requireCheckbox() requires a boolean value and renders it as a checkbox. The overloads requireCheckbox(checkedValue) and requireCheckbox(checkedValue, uncheckedValue) let a checkbox store other values, such as Yes and No. The example keeps the default booleans so a formula can test G2=TRUE.

requireDate() accepts any date. Builder methods such as requireDateOnOrAfter(date) and requireDateBetween(start, end) narrow it to a period. requireNumberBetween(start, end) accepts a number between the two values or equal to either one. The DataValidationCriteria reference lists every criteria type a rule can report.

Warn or reject, and explain the rule

setAllowInvalid(allowInvalid) decides what happens when an entry fails the rule. With true, Sheets keeps the value and shows a warning. With false, Sheets rejects the input. The example rejects invalid owners, statuses, and hours, because later logic depends on them. It only warns on due dates, so a person can type TBD while waiting for a date and fix it later.

setHelpText(helpText) sets the text that appears when a person hovers over a cell with the rule. Every help text in this script starts with Task tracker: . That prefix is how the script recognizes its own rules on a later run.

Replace only your own validation rules

setDataValidation(rule) replaces whatever rule a cell had before. If someone had already added a validation rule to the Owner column by hand, a blind call would delete it without a trace.

checkValidationOwnership_ reads the current rules with getDataValidations(), which returns a two-dimensional array with one entry per cell. An empty cell's entry is null. For every rule it finds, it reads getHelpText(). If the text does not start with HELP_PREFIX, the function throws:

Column Owner, row 7 already has a validation rule this script did not create. Nothing was changed.

The check runs for all five columns before any setDataValidation call, so a conflict in the last column still prevents changes to the first.

The help-text prefix is a convention, and it has limits. A person can edit a rule's help text in the Sheets sidebar, and the script then treats that rule as foreign and stops. A person could also copy the prefix into their own rule, and the script would replace it. For a practice tracker this is acceptable; keep the prefix distinctive and mention it in the sheet's notes if others maintain the file.

When you need to remove the script's validation, removeTaskRules runs the same ownership check and then calls setDataValidation(null) on each column. Passing null removes the rule from the range. The script never calls clearDataValidations() on a wider range, because that would also remove rules it does not own.

Highlight rows with conditional formatting

A conditional format rule changes how cells look when a condition is true. It does not change values. You build one with SpreadsheetApp.newConditionalFormatRule(), set a condition with a when... method or a gradient, set the format, choose ranges with setRanges(ranges), and call build(). See the ConditionalFormatRuleBuilder reference.

buildFormatRules_ creates four rules:

1. Overdue rows. whenFormulaSatisfied('=AND($E2<>"", $E2<TODAY(), $D2<>"Done")') on A2:H201 sets bold red text when a row has a due date in the past and is not done.

2. Blocked status. whenTextEqualTo('Blocked') on D2:D201 sets a pink background.

3. Done status. whenTextEqualTo('Done') on D2:D201 sets a green background and grey text.

4. Hours gradient. setGradientMinpointWithValue and setGradientMaxpointWithValue on F2:F201 shade cells from white at 0 to orange at 40.

Write a custom formula for the top-left cell

A custom formula rule is written as if for the first cell of its range, here row 2. Relative references move with each cell the rule covers, so E2 in row 5 is evaluated as E5. The dollar sign before a column letter fixes the column. $E2 therefore always reads column E of the current row, even when the rule is evaluating column A or H. Without the $, the rule applied to column B would read column F. The Google Sheets help page on conditional formatting describes this use of $.

The formula's three conditions guard against blank and finished rows. $E2<>"" keeps empty rows from counting as overdue, $E2<TODAY() compares the date with today in the spreadsheet's time zone, and $D2<>"Done" stops highlighting a task once it is complete. TODAY() recalculates as the date changes, so a row can become overdue overnight without any script running.

Shade numbers with a gradient

setGradientMinpointWithValue(color, type, value) and setGradientMaxpointWithValue(color, type, value) take a color, a SpreadsheetApp.InterpolationType, and the value as a string. InterpolationType.NUMBER treats the value as a fixed number. Other types, such as MIN, MAX, PERCENT, and PERCENTILE, scale to the data instead. A fixed 0 to 40 scale matches the Hours validation rule, so the same shade always means the same estimate. setGradientMidpointWithValue adds a third color when you need one.

Rule order decides conflicts

Google Sheets evaluates rules in list order: "The first rule found to be true will define the format of the cell or range" (conditional formatting help). In the example, the overdue rule comes first. A blocked task that is also overdue shows bold red text in its Status cell without the pink background, because the overdue rule already matched. Reorder the array returned by buildFormatRules_ if you want the status colors to win.

Keep the user's conditional format rules

Sheet.setConditionalFormatRules(rules) "replaces all currently existing conditional format rules in the sheet with the input rules" (Sheet reference). There is no method to add one rule. To add rules, you read the current list with getConditionalFormatRules(), change the array, and write the whole list back. clearConditionalFormatRules() removes every rule on the sheet, including rules people created by hand, so this example never calls it.

A conditional format rule has no ID or owner field. replaceOwnedFormatRules_ identifies its own rules by a signature built in ruleSignature_:

• A gradient rule's signature is gradient plus its ranges in A1 notation, such as gradient|F2:F201.

• A text rule's signature includes the criteria values, such as text|D2:D201|Blocked.

• A custom formula rule's signature is formula plus its ranges.

• Any other kind of rule gets an empty signature and is never treated as owned.

ConditionalFormatRule.getRanges(), getGradientCondition(), and getBooleanCondition() supply these parts, and BooleanCondition.getCriteriaType() returns a value from the BooleanCriteria enum such as TEXT_EQUAL_TO or CUSTOM_FORMULA.

The function then:

1. Builds fresh copies of the four owned rules and computes their signatures.

2. Reads the existing rules and keeps every rule whose signature does not match.

3. Writes the kept rules first and the fresh owned rules after them.

Kept rules go first so that, under the first-true-rule-wins order, a person's own highlight keeps priority over the script's. A second run of applyTaskRules finds the four owned rules, drops them, and appends four new copies, so the total stays the same.

The signature is a heuristic, and you should know where it fails:

• If a person changes the color of an owned rule in the sidebar, the signature still matches and the next run restores the script's color.

• If a person changes an owned rule's range or text, the signature no longer matches. The next run keeps the edited rule as theirs and adds a new owned copy.

• If a person creates their own gradient on exactly F2:F201, or their own custom formula on exactly A2:H201, the script treats it as owned and replaces it.

Formula rules are matched by kind and range only, because the reference does not document whether getCriteriaValues() returns a formula exactly as the script wrote it. If your file needs stronger ownership, give the script's rules ranges that people are told not to reuse, or keep a list of owned rule descriptions in a hidden tab that the script reads.

Warn before header edits

The timestamp and the rules both depend on the header staying where it is. protectHeaderWithWarning_ calls Range.protect() on A1:H1, which returns a Protection object, then sets a description and setWarningOnly(true). A warning-only protection lets every editor change the header but asks for confirmation first. See the Protection reference.

The function first reads sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE) and skips creation if a protection with the description Task tracker header already exists. Without that check, each run would add another protection on the same row.

A warning is the right strength for this example because it changes nobody's access. A full protection that removes editors is an access-control change, and the reference notes that "the spreadsheet owner is always able to edit protected ranges and sheets." Decide who may edit a range with the people who share the file before a script restricts it.

React to a status edit with onEdit

An onEdit(e) function in a bound project is a simple trigger. Apps Script recognizes the reserved name and runs the function "when a user changes the value of any cell in a spreadsheet" (simple triggers). You do not install anything. Saving the project with a function named onEdit is enough.

What the event object contains

Apps Script passes one argument, conventionally named e. For a Sheets edit, the event objects guide lists these fields:

In the generated declarations, the parameter type is Events.SheetsOnEdit, and value and oldValue are optional strings. Treat both as possibly undefined.

What a simple trigger may do

Simple triggers run in a restricted mode. The AuthMode reference describes LIMITED as "a mode that allows access to a limited subset of services," used when a bound script runs an onOpen(e) or onEdit(e) simple trigger. The simple triggers guide lists the consequences:

• They cannot use services that require authorization. Sending a Gmail message from onEdit fails.

• They can change the file they are bound to, but cannot open other files.

• They may or may not be able to identify the current user.

• They do not run when the file is open in view or comment mode.

• They cannot run for longer than 30 seconds.

• Script executions and API requests do not cause them to run. A setValue() call from a script does not start onEdit.

• onEdit queues up to two events.

The last two points shape the design. The timestamp write in onEdit does not start another onEdit, so there is no loop. A burst of rapid edits can be dropped after the queue fills, so treat Last changed as a convenience. If you need every change recorded, use the spreadsheet's version history.

Guard the edited range

onEdit runs for every value edit in the spreadsheet, on every tab and column. Most edits have nothing to do with Status, so the function checks the edit before it reads or writes anything:

1. Tab. range.getSheet().getName() must be Tasks. Edits on Lists return immediately.

2. Column. The edited block must include column D. The test compares getColumn() and getLastColumn() with STATUS_COLUMN, so a paste across columns B to E still counts, while an edit in Title does not.

3. Rows. The first row is clamped to row 2 and the last row to sheet.getLastRow(). An edit to the header, or a delete on empty rows below the table, leaves no rows and returns.

4. No-op entry. For a single cell, if e.value equals e.oldValue, the person re-entered the same status, and the function returns.

5. Header position. The function reads row 1 and returns without writing if Status is no longer in column D or Last changed is no longer in column H. If someone inserted a column, it does not guess where the timestamp belongs.

Only then does it read the affected rows once, put the current Date in each row that has a Task ID, and write the Last changed cells with one setValues() call. Rows without an ID keep their existing value, so clearing a block of empty status cells does not scatter timestamps.

applyTaskRules set the number format of H2:H201 to yyyy-mm-dd hh:mm, so the written Date is shown as a date and time in the spreadsheet's time zone. The script stores a date value; the format changes only its display.

Try it

1. Change T-101's status from In progress to Blocked using the dropdown. Within a few seconds, H3 shows the current date and time, and the status cell turns pink.

2. Edit T-101's title. H3 does not change.

3. Type Waiting into a status cell. Sheets rejects it because the status rule does not allow invalid input.

To see what onEdit did, open Executions in the Apps Script editor, which lists recent runs and their errors. You cannot click Run on onEdit to test it meaningfully: a manual run has no event object, so e.range throws.

Choose an installable edit trigger when you need more

A simple trigger is the right fit when the edit handler stays inside the bound spreadsheet and finishes quickly. Use an installable edit trigger when the handler needs something a simple trigger cannot do, for example:

• It must use a service that needs authorization, such as sending email or writing to another spreadsheet.

• It needs more than 30 seconds. Installable triggers run under the general per-execution limit; the quotas page lists 6 minutes per execution and a daily total for trigger runtime.

• It must run with one designated account's access.

An installable trigger is created in code or from the Triggers page:

function installTaskEditTrigger() {
  ScriptApp.newTrigger('handleTaskEdit')
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

Two differences matter before you switch. First, "installable triggers always run under the account of the person who created them" (installable triggers). Every editor's change then runs code with the installer's access, which is a permission decision for the people sharing the file. Second, give the handler a name other than onEdit. Apps Script runs a function named onEdit as a simple trigger on every edit regardless of other triggers, so pointing an installable trigger at it would run the same code twice per edit. Installing triggers also needs the script.scriptapp scope and the duplicate checks from Install time-driven triggers safely. This chapter's example stays with the simple trigger and does not install one.

Remove the script's rules

removeTaskRules undoes applyTaskRules without touching anything else:

• It runs the validation ownership check, then sets each owned column's validation to null.

• It rebuilds the owned rule signatures and writes back every conditional format rule that does not match.

• It removes range protections whose description is Task tracker header.

It does not change cell values, delete timestamps, or reset the Last changed number format. onEdit keeps running as long as the function exists in the project; delete or rename it in the editor to stop the timestamps.

Common failures

• Tasks sheet is missing. Rename the tab exactly Tasks and repeat the import.

• Tasks must use this header row... Restore the eight headers in order in row 1. Do not add a title row above them.

• Lists sheet with Owner in A1 is missing. Create the Lists tab and import owners.csv so A1 reads Owner.

• Row N: Due date is not a date value. The split may have stored the date as text, depending on the spreadsheet locale. Retype the date in that cell, or set File > Settings > Locale before importing.

• Row N: status "..." is not one of... Fix the value to one of the four statuses. The script does not rewrite your data to fit its rules.

• Column X, row N already has a validation rule this script did not create. Someone added a rule by hand, or edited a rule's help text. Open Data > Data validation, decide whether to keep that rule, and remove it yourself if the script should own that column.

• Two copies of a highlight after a second run. A person edited the range or text of an owned conditional format rule, so the signature no longer matched. Delete the extra copy in Format > Conditional formatting.

• The timestamp does not appear. Check the tab name, that the edit touched column D in a row with a Task ID, and that the header still matches. Open Executions to see whether the trigger ran or failed. Edits made by a script, by an API, or from a view-only session do not start onEdit.

• TypeError: Cannot read properties of undefined (reading 'range'). You clicked Run on onEdit. Test it by editing the sheet instead.

• An authorization error inside onEdit. The handler called a service that needs authorization. Move that work to an installable trigger with a different function name.

Exercise: add a priority column

Add a ninth column, Priority, with values Low, Medium, and High.

1. Add Priority to the end of HEADER and type it into Tasks!I1. Add a PRIORITY_COLUMN = 9 constant. Keep Last changed in column H so onEdit needs no change.

2. In buildValidationRules_, add a requireValueInList(['Low', 'Medium', 'High'], true) rule with setAllowInvalid(false) and a help text starting with HELP_PREFIX.

3. In checkExistingValues_, reject priorities outside that list, and add the new column to the list in removeTaskRules.

4. In buildFormatRules_, add a whenTextEqualTo('High') rule on the priority column with bold text.

5. Run applyTaskRules, then run it again. The log should report the same number of replaced and added rules on the second run, and the Conditional formatting sidebar should show no duplicates.

Before you run step 5, add one conditional format rule of your own by hand on column B. Confirm it is still present, and still first in the sidebar, after both runs.

Next, fetch data from web APIs to bring outside data into a sheet.