A checklist that fills up with completed items becomes hard to scan, and onEditHideCompletedRows solves that by hiding a row the moment its Done checkbox turns TRUE, keeping only outstanding tasks visible without deleting any data.
Manually hiding rows one at a time via right-click is tedious on a long checklist, and it's easy to hide the wrong row by accident when the list has shifted since the last time someone cleaned it up.
This example watches a single checkbox column, calls hideRows only after flushing pending changes so the hide takes effect immediately, and leaves a note on the row recording when it was hidden.
You'll finish with a Checklist sheet that self-organizes as tasks get checked off, and a matching unhide path if someone accidentally reopens a completed item.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Field | Purpose |
|---|---|---|
| A | Task | The checklist item text, stays visible when the row is shown |
| B | Done | Checkbox - watched column that triggers hide or show |
| Last column | Hidden note | Records the date the row was auto-hidden |
What it does
Every edit to the Checklist sheet is inspected, but the function only reacts when the edited cell is in column B, the Done checkbox. Checking the box hides that row after leaving a timestamped note; unchecking it calls showRows to bring the row back.
- Only column B is watched - editing the Task text never hides anything
- showRows runs immediately when the checkbox is unchecked, restoring visibility
- A note is added to the last column documenting when the row was hidden
Prerequisites
hideRows and showRows require the script to run as an installable trigger with edit access to the sheet, and the Done column must genuinely contain checkboxes (Insert > Checkbox), not text like 'yes' or 'no'.
- A sheet named 'Checklist' with a real checkbox in column B
- An installed onEdit trigger with the necessary sheet edit permissions
- No other automation that also hides or shows rows on the same sheet, to avoid conflicting logic
Walkthrough
e.value on a checkbox edit comes through as the string 'TRUE' or 'FALSE', not a boolean, which is why the comparison is a strict string comparison rather than a truthy check against the boolean true.
SpreadsheetApp.flush() forces any pending spreadsheet changes to apply before hideRows runs; without it, the hide can occasionally appear to lag behind the checkbox toggle in the UI.
Edge cases
Unchecking a box on a row that was never actually hidden simply calls showRows on an already-visible row, which is a harmless no-op rather than an error.
- Checking multiple boxes via a paste hides each affected row individually since onEdit fires per cell in that case for checkbox toggles
- Manually hiding a row outside the script and then checking its box will still add a note, even though the row was already hidden for a different reason
- Filtering the sheet while rows are hidden can interact unexpectedly with Sheets' own filter-hidden state
Testing
Check a Done box on a test row and confirm it disappears from view along with a note appearing in the last column, then uncheck the same box and confirm the row reappears.
- Edit the Task text directly and confirm no hide or show occurs
- Check and immediately uncheck the same box to verify the row toggles back to visible
- Inspect the note on a hidden row to confirm the timestamp is accurate
Hardening
Right now, unchecking a box only calls showRows without clearing the note left behind, so a previously-hidden-then-reopened row keeps a stale 'Hidden automatically on...' note.
- Clear the note with cell.clearNote() in the showRows branch to avoid stale hidden-date notes
- Add a check for e.range.getSheet().getName() to guard against running on the wrong tab
- Batch-hide multiple rows in one call using Sheet.hideRows(startRow, numRows) if you expect many boxes to be checked in quick succession
Variations
The same checkbox-driven visibility pattern can move rows to a separate 'Completed' sheet instead of just hiding them in place, combining this example with the row-copying pattern used elsewhere.
- Move the row to a Completed tab instead of hiding it, freeing up the Checklist for active items only
- Auto-check a linked 'Approved' checkbox in a different column when Done is checked
- Add a periodic cleanup that permanently deletes rows hidden for more than 30 days
Full code: onEditHideCompletedRows()
The function treats hidden state as a direct function of the checkbox value, so hiding and showing stay in sync without needing to track state anywhere else.
function onEditHideCompletedRows(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== 'Checklist') return;
var checkboxColumn = 2;
if (e.range.getColumn() !== checkboxColumn) return;
var row = e.range.getRow();
if (row === 1) return;
var isChecked = e.value === 'TRUE';
if (!isChecked) {
sheet.showRows(row);
return;
}
var lastColumn = sheet.getLastColumn();
var noteColumn = lastColumn;
sheet.getRange(row, noteColumn).setNote('Hidden automatically on ' + new Date().toLocaleDateString());
SpreadsheetApp.flush();
sheet.hideRows(row);
}- Line 3: Restricts the function to the Checklist sheet so checkboxes on other tabs don't hide rows unexpectedly.
- Line 6: Only reacts to edits in column B, the Done checkbox column.
- Line 11: Compares e.value as a string since checkbox edits report 'TRUE' or 'FALSE' as text, not as a boolean.
- Line 13: Un-hides the row immediately if the box was unchecked, treating that as the default visible state.
- Line 19: Leaves a timestamped note on the row explaining why it disappeared from view.
- Line 21: Flushes pending spreadsheet changes so the hide takes effect without a visible delay.
- Line 22: Hides the row once the note has been written, completing the checked-box path.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before enabling row auto-hide
- 1Done column confirmed to contain real checkboxes, not text
- 2Sheet name in the guard matches your actual tab
- 3Tested both the hide and the show path
- 4Note-clearing added if you want to avoid stale hidden-date notes
- 5Trigger installed as an installable onEdit, not left as a simple trigger
- 6Confirmed no other script also manages visibility on the same sheet
Frequently asked questions
Checkbox edits in Apps Script report their new value through e.value as a string, so a direct comparison to the boolean true would always be false; comparing to the string 'TRUE' is the correct way to detect a checked box.
Pasting a column of TRUE values typically fires onEdit once per affected cell for checkbox ranges, so each row gets hidden individually in quick succession rather than all at once - the end result looks the same to the user.
Yes, hiding a row only affects its visibility, not whether formulas include its data; use SUBTOTAL with the appropriate function code if you specifically want totals that exclude hidden rows.
Sheets tracks filter-hidden and manually-hidden rows separately in most cases, so a row hidden by hideRows can still be affected by a filter independently, and the combination can occasionally look confusing in the row-number gutter.
Run sheet.showRows(1, sheet.getMaxRows()) once from the script editor to unhide every row in a single call, rather than manually toggling each checkbox back to FALSE.
No - conditional formatting only changes appearance, not the underlying value, so e.value would reflect whatever the cell's actual typed content is rather than TRUE or FALSE, and the comparison would never match.