Validate, format, and track Sheets edits
Add dropdowns, checkboxes, and conditional formatting to a Google Sheets table with Apps Script, and timestamp status edits with onEdit(e).
Apply data validation and conditional formatting to a known Sheets table from a script that replaces only its own rules, and record when a person changes a row's status with an onEdit simple trigger that checks which cells changed.
Add data validation rules, conditional formatting, a warning-only header protection, and an onEdit status timestamp to a task table, without overwriting rules that someone added by hand. Before changing the sheet, the script checks the existing cells and identifies its own rules. You need Read and write Sheets in batches first.
Set up a disposable task table
This chapter uses a dedicated eight-column Tasks practice table so you can test every validation and formatting rule in one sheet. The Requests table returns in Install and remove time-driven triggers and Build a Sheets request tracker.
Create a new disposable spreadsheet. Rename the first tab
Tasksand add a second tab namedLists.Copy the text of
sample.csvintoTasks!A1. Select column A, choose Data > Split text to columns, and set the separator to Comma. Verify that the table spans five rows by eight columns.Copy the text of
owners.csvintoLists!A1and split it the same way so column A readsOwner,Alex,Sam,Jo.Open Extensions > Apps Script and replace
Code.gswith the example file. In Project Settings, select Show "appsscript.json" manifest file in editor, then replaceappsscript.jsonwith the included 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
Keep the Tasks header row in this exact order:
| Task ID | Title | Owner | Status | Due date | Hours | Urgent | Last changed |
|---|
Sheets converts the Due date column to date values. T-100 has a 2020 due date, so the overdue highlight appears at once. 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) {
setColumnValidation_(sheet, item.column, 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) {
setColumnValidation_(sheet, column, 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) {
if (!e || !e.range) return;
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.');
}
});
}
/**
* @param {SpreadsheetApp.Sheet} sheet
* @param {number} column
* @param {SpreadsheetApp.DataValidation | null} rule
* @returns {void}
*/
function setColumnValidation_(sheet, column, rule) {
sheet.getRange(FIRST_DATA_ROW, column, RULE_ROWS, 1).setDataValidation(rule);
}
/**
* 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 limits the script to the bound spreadsheet. Run applyTaskRules once and approve access when prompted.
Check the table before adding rules
applyTaskRules validates the sheet before its first write:
The
Taskstab exists and row 1 matches all eight headers in order.The tab has at least 201 rows, covering the target range in rows 2 through 201.
Lists!A1isOwnerwith at least one name below it.Every existing row already satisfies the rules: owners appear on
Lists, statuses match one of the four allowed values, due dates are valid dates, hours are numbers from0to40, andUrgentis blank,TRUE, orFALSE.No cell in the target columns has a validation rule created outside this script.
If any check fails, the function throws an error that names the row or column, and the sheet is unchanged.
RULE_ROWS = 200 fixes the managed range to rows 2 through 201. If your table grows past row 201, the checks stop the run and ask you to increase RULE_ROWS.
Build data validation rules
A data validation rule restricts which values a cell accepts. To attach a rule:
Call
SpreadsheetApp.newDataValidation()to get aDataValidationBuilder.Set its criteria and options.
Call
build()to get aDataValidationobject.Apply it to a column range with
Range.setDataValidation(rule). If a range lookup or array index returnsundefinedbefore that call, see Cannot read properties of undefined (reading 'setDataValidation').
See the DataValidationBuilder reference and Range reference.
buildValidationRules_ creates one { column, rule } entry per managed column, and applyTaskRules passes each entry to setColumnValidation_(sheet, item.column, item.rule). That helper calls setDataValidation(rule) on that column's 200-row range without touching Task ID, Title, or Last changed:
| Column | Builder call | What a person sees |
|---|---|---|
Owner | requireValueInRange(ownerRange, true) | A dropdown of the names in Lists!A2:A4 |
Status | requireValueInList(STATUSES, true) | A dropdown of New, In progress, Blocked, Done |
Due date | requireDate() | A warning on any value that is not a date |
Hours | requireNumberBetween(0, MAX_HOURS) | A rejection for numbers outside 0 to 40 |
Urgent | requireCheckbox() | A checkbox in each cell |
Dropdowns from values and from a range
requireValueInList(values, showDropdown) limits input to an array of strings written in the code. requireValueInRange(range, showDropdown) limits input to the values in a spreadsheet range. Passing true for showDropdown shows the in-cell dropdown arrow (DataValidationBuilder reference).
Use requireValueInList when the code depends on fixed strings. In this project, onEdit and the conditional format rules check the exact strings in STATUSES. Use requireValueInRange when spreadsheet editors maintain the options, as in Lists!A2:A4. After you add an owner below Jo, run applyTaskRules again so the validation range grows to the new last row.
Checkboxes, dates, and numbers
requireCheckbox() requires a boolean value and renders the cell as a checkbox. The overloads requireCheckbox(checkedValue) and requireCheckbox(checkedValue, uncheckedValue) store custom checked and unchecked values. This example keeps TRUE and FALSE.
requireDate() accepts any valid date. requireDateOnOrAfter(date) and requireDateBetween(start, end) restrict dates to a window. requireNumberBetween(start, end) accepts numbers within the inclusive range [start, end]. See the DataValidationCriteria reference for all supported criteria.
Warn or reject, and explain the rule
setAllowInvalid(allowInvalid) decides what happens when an entry breaks the rule. false rejects the edit. true accepts the value and marks the cell with a warning triangle. The script rejects invalid owners, statuses, and hours, and only warns on Due date, so someone can enter a placeholder such as TBD before a date is confirmed.
setHelpText(helpText) sets the rule's hover text. Every rule this script creates starts its help text with Task tracker: (HELP_PREFIX). Later runs read that prefix to tell script-owned rules from manual ones.
Replace only your own validation rules
setDataValidation(rule) overwrites any validation rule already on the range. So before any write, checkValidationOwnership_ calls Range.getDataValidations() once on each 200-row target column to inspect the 2D array of rules in memory instead of reading cells one by one. Each cell must return either null or a DataValidation whose getHelpText() starts with HELP_PREFIX. Any other rule stops the function before it changes a column:
Column Owner, row 7 already has a validation rule this script did not create. Nothing was changed. The prefix is the only ownership mark. If you edit an owned rule's help text in the Sheets sidebar, the next run treats that rule as manual and stops. If you type HELP_PREFIX into a manual rule, the script replaces that rule.
removeTaskRules runs the same ownership check, then calls setColumnValidation_(sheet, column, null) on each managed column, which passes null to setDataValidation on those 200 rows. It does not call clearDataValidations() on a wider range, because that call would also remove manual rules.
Pasting copied cells over a validated column in the Sheets UI replaces the destination cells' validation rules unless the editor chooses Edit > Paste special > Values only. Re-running applyTaskRules restores missing script-owned rules on blank validation cells while stopping if a pasted cell brought its own manual rule (Range.setDataValidation).
Highlight rows with conditional formatting
A conditional format rule changes how cells look when a condition is true. It does not change cell values. Build one with SpreadsheetApp.newConditionalFormatRule(), set a boolean or gradient condition, set the target ranges with setRanges(ranges), and call build() (ConditionalFormatRuleBuilder reference).
buildFormatRules_ creates four rules:
Overdue rows.
whenFormulaSatisfied('=AND($E2<>"", $E2<TODAY(), $D2<>"Done")')onA2:H201applies bold red text when a row has a past due date and is notDone.Blocked status.
whenTextEqualTo('Blocked')onD2:D201applies a pink background.Done status.
whenTextEqualTo('Done')onD2:D201applies a green background and grey text.Hours gradient.
setGradientMinpointWithValueandsetGradientMaxpointWithValueonF2:F201shade cells from white at0to orange at40.
Write a custom formula for the top-left cell
Write a custom formula rule as if for the top-left cell of its target range, here A2. Sheets shifts the row number for each row. The $ before the column letter locks the column, so the formula checks the same column from every cell in A2:H201 (conditional formatting help). $E2 always means column E of the current row. Without the $, the rule evaluated in column B would look at column F instead.
In =AND($E2<>"", $E2<TODAY(), $D2<>"Done"), $E2<>"" skips rows with no due date, $E2<TODAY() compares the due date with today in the spreadsheet's time zone, and $D2<>"Done" skips finished tasks.
Shade numbers with a gradient
setGradientMinpointWithValue(color, type, value) and setGradientMaxpointWithValue(color, type, value) take a hex color, a SpreadsheetApp.InterpolationType, and a bound as a string. InterpolationType.NUMBER pins the gradient to the 0 to 40 scale of the Hours validation rule. MIN, MAX, PERCENT, and PERCENTILE scale to whatever data is present instead.
Rule order decides conflicts
Sheets evaluates conditional format rules in list order (conditional formatting help). Whenever two matching rules set the same format property, such as font color, the earlier rule wins. Because overdue sets bold red text without setting a fill color, a task that is both Blocked and overdue gets bold red text from overdue and the pink background from blocked. Reorder the array in buildFormatRules_ if status rules should override the overdue font color.
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). To preserve manual rules:
Read the sheet's rules with
getConditionalFormatRules().Replace only the script-owned entries in the array.
Pass the combined list to
setConditionalFormatRules().
A conditional format rule has no ID and no help text, so replaceOwnedFormatRules_ recognizes its own rules by a signature. ruleSignature_ builds it from getRanges(), getGradientCondition(), getBooleanCondition(), and the BooleanCriteria enum:
Gradient rules:
gradient|F2:F201Text-equality rules:
text|D2:D201|BlockedCustom-formula rules:
formula|A2:H201All other rules:
''(never treated as script-owned)
The helper builds the four script rules, drops the existing rules whose signatures match, and writes the kept rules followed by the script's rules. Manual rules therefore stay ahead of the script's rules in priority.
Matching by signature has three consequences:
Changing an owned rule's color in the sidebar leaves its signature unchanged, so the next run restores the script's color.
Changing an owned rule's range or criteria text changes its signature, so the next run keeps the changed rule and appends a fresh script copy.
A manual gradient on
F2:F201or a manual custom formula onA2:H201has the same signature as the script's rule, so the next run replaces it.
Cutting and pasting cells in Google Sheets splits existing conditional format ranges into smaller fragments, and calling rules.push(...) on repeated runs without replacing old entries can bloat sheet.getConditionalFormatRules() into hundreds of slow rules. Build a fresh array of clean ranges and pass it to sheet.setConditionalFormatRules(rules) to keep the sheet's rule list compact.
Warn before header edits
protectHeaderWithWarning_ calls Range.protect() on A1:H1 and sets the description Task tracker header. It also calls setWarningOnly(true), so an editor sees a confirmation prompt before changing a header (Protection reference). Before it creates the protection, it checks sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE) and skips creation if a protection named Task tracker header already exists.
To block edits instead of showing a warning, omit setWarningOnly(true) and remove other accounts from the Protection. Calling range.protect() copies all current spreadsheet editors onto the protection's editable list, so restricting edits to yourself requires protection.removeEditors(protection.getEditors()) and, when protection.canDomainEdit() is true, protection.setDomainEdit(false). The Protection reference notes that "the spreadsheet owner is always able to edit protected ranges and sheets."
React to a status edit with onEdit
A function named onEdit(e) in a bound script is a simple trigger. It runs "when a user changes the value of any cell in a spreadsheet" (simple triggers guide), with no trigger to install.
What the event object contains
Apps Script passes an event object e (Events.SheetsOnEdit in the type declarations) with these properties (event objects guide):
| Field | Meaning |
|---|---|
e.range | The edited Range (may span multiple cells, such as after a paste). It is a property, not a method (see e.range is not a function). |
e.value | The new value as a string when a single cell was edited, or undefined for multi-cell edits or clears. |
e.oldValue | The prior value for a single-cell edit, or undefined if the cell was previously blank or multiple cells were edited. |
e.source | The bound Spreadsheet. |
e.authMode | ScriptApp.AuthMode.LIMITED for simple triggers. |
e.user | The active user, when visible under Google's security rules. |
e.triggerUid | Present only on installable triggers. |
What a simple trigger may do
Simple triggers run in ScriptApp.AuthMode.LIMITED (AuthMode reference) under the restrictions listed in the simple triggers guide:
They cannot call services that require OAuth authorization (such as Gmail or Drive) or open other spreadsheets.
They can modify the bound spreadsheet when triggered from an edit session, and do not run in view-only or comment-only sessions.
They must finish within 30 seconds.
Writing the timestamp with
setValues()insideonEditdoes not re-triggeronEditin a loop.onEditqueues at most two pending events, so rapid bursts of edits can skip timestamps. Use version history when you need a complete audit log.
Guard the edited range
onEdit runs on every manual edit anywhere in the spreadsheet, so the function returns early unless the edit touches task statuses:
range.getSheet().getName()must beTasks.The edited columns, from
getColumn()togetLastColumn(), must includeSTATUS_COLUMN(column D).The function clips the edited rows to row 2 through
sheet.getLastRow()to ignore header edits and edits below the table.For a single-cell edit (
range.getNumRows() === 1 && range.getNumColumns() === 1), ife.value === e.oldValue, the status did not change, and the function returns.Row 1 must still have
Statusin column D andLast changedin column H.
When a person pastes a block of cells, drags the fill handle, or clears a multi-cell selection, onEdit(e) runs once for the entire e.range, and both e.value and e.oldValue are undefined (Google Sheets events). Because undefined === undefined evaluates to true, testing e.value === e.oldValue without first checking that e.range is a single cell skips every multi-cell paste or clear. Edits made by scripts, Google Forms submissions, and recalculated formulas never fire onEdit(e).
When all five checks pass, onEdit reads the affected rows once and puts the current Date in Last changed for each row that has a Task ID. It then writes the timestamps in one setValues() call. applyTaskRules formats H2:H201 as yyyy-mm-dd hh:mm, so the stored Date values show date and time in the spreadsheet's time zone.
Try it
Change
T-101's status fromIn progresstoBlockedusing the dropdown.H3updates to the current timestamp andD3turns pink.Edit
T-101's title in column B.H3does not change.Type
Waitinginto a status cell. Sheets rejects the input.
Open Executions in the Apps Script editor to see onEdit runs and errors. Do not click Run on onEdit in the editor. A manual run passes undefined for e and throws when it reads e.range.
Choose an installable edit trigger when you need more
Use an installable edit trigger instead of a simple onEdit trigger when the handler needs to:
Call a service that requires OAuth authorization, such as Gmail or another spreadsheet.
Run for longer than 30 seconds (up to the 6-minute execution limit on the quotas page).
Run as one specific account.
Create an installable edit trigger in code or from the Triggers page:
function installTaskEditTrigger() {
ScriptApp.newTrigger('handleTaskEdit')
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onEdit()
.create();
} Two requirements apply (installable triggers guide):
Installable triggers always run under the account of the user who created the trigger.
Name the handler something other than
onEdit(such ashandleTaskEdit). Otherwise Apps Script runs it twice per edit, once as a simple trigger and once as an installable trigger.
Creating triggers in code also needs the script.scriptapp scope and the duplicate checks covered in Install and remove time-driven triggers.
Remove the script's rules
removeTaskRules removes only the rules and protection created by applyTaskRules:
Checks validation ownership and clears each managed column's validation through
setColumnValidation_(sheet, column, null).Filters out conditional format rules matching the script's four signatures and keeps any manual rules.
Removes range protections whose description is
Task tracker header.
Cell values, existing timestamps, and number formats stay as they are. To stop onEdit from writing more timestamps, delete or rename onEdit in Code.gs.
Common failures
Tasks sheet is missing.Rename the tabTasksand re-importsample.csv.Tasks must use this header row...Restore the eight headers in row 1 in exact order.Lists sheet with Owner in A1 is missing.Create theListstab and importowners.csv.Row N: Due date is not a date value.Retype the date in rowN, or set File > Settings > Locale before splitting the CSV.Row N: status "..." is not one of...Change the cell in rowNto one of the four allowed statuses before runningapplyTaskRules.Column X, row N already has a validation rule this script did not create.Open Data > Data validation and remove or keep the manual rule before re-running.Duplicate highlight after a second run. Someone modified an owned rule's range or criteria text in Format > Conditional formatting. Delete the duplicate in the sidebar.
Timestamp does not update. Check that a person edited column D of
Taskson a row with aTask ID, and look in Executions for errors.TypeError: Cannot read properties of undefined (reading 'range'). You startedonEditwith Run in the editor. Test it by editing a cell in the spreadsheet instead.Authorization error inside
onEdit. Move any call requiring OAuth consent into an installable trigger with a different function name.
Exercise: add a priority column
Add a ninth column, Priority, with allowed values Low, Medium, and High:
Append
'Priority'toHEADER, typePriorityintoTasks!I1, and addconst PRIORITY_COLUMN = 9;(keepingLast changedin column H).In
buildValidationRules_, add arequireValueInList(['Low', 'Medium', 'High'], true)rule withsetAllowInvalid(false)and help text starting withHELP_PREFIX.Validate existing priority cells in
checkExistingValues_and includePRIORITY_COLUMNinremoveTaskRules.In
buildFormatRules_, add awhenTextEqualTo('High')rule onI2:I201with bold text.Add one manual conditional format rule on column B, then run
applyTaskRulestwice. Confirm your manual rule remains first in the Conditional formatting sidebar and no duplicate rules appear.
Next, read Write Sheets formulas, rules, and rich-text links to generate row-independent R1C1 formulas and multi-link cells before moving on to Named ranges, filters, and pivots.
Project files
Complete source files for this chapter's examples.
Validate, highlight, and timestamp a task table
Code.gs
Download fileconst 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) {
setColumnValidation_(sheet, item.column, 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) {
setColumnValidation_(sheet, column, 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) {
if (!e || !e.range) return;
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.');
}
});
}
/**
* @param {SpreadsheetApp.Sheet} sheet
* @param {number} column
* @param {SpreadsheetApp.DataValidation | null} rule
* @returns {void}
*/
function setColumnValidation_(sheet, column, rule) {
sheet.getRange(FIRST_DATA_ROW, column, RULE_ROWS, 1).setDataValidation(rule);
}
/**
* 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;
}
appsscript.json
Download file{
"timeZone": "Etc/UTC",
"runtimeVersion": "V8",
"oauthScopes": [
"https://www.googleapis.com/auth/spreadsheets.currentonly"
]
}
sample.csv
Download fileTask 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,,
owners.csv
Download fileOwner
Alex
Sam
Jo