Guide

Everything the add-on checks, every guardrail it can add, and how to fix each problem by hand — the add-on is not needed for any of the fixes below.

Getting started

Open the shared spreadsheet. Extensions › Shared Sheet Guardrails › Open guardrails.
Press Check this sheet. Free and unlimited; nothing is changed.
Want a quick win? Set up › Quick protect (free) puts a warning on the formulas and the header row.
For the full set: Set up › Read this tab, check each column's role, Preview, Apply (Pro).
Check again whenever you like. Collaborators need nothing installed.

Column roles

Set up proposes a role for each column from what's already in it, with the reason (“Formula: 98% of rows use =D2*E2”). A role marked Check is a suggestion the add-on is less sure of. Change any of them before you preview.

Role	What it gets	What collaborators see
Header row	Protected, frozen, a note on each header	A warning (or a lock) if they try to rename it; the note explains the column
Formula	Protected; highlight if a cell stops being a formula	“You are trying to edit a protected cell”
Array output	Protected; highlight when the array errors	As above
Choice	A dropdown, refusing (or marking) anything else	A dropdown; “Pick from the list: …” if they type something else
Date / Number / Email / Web address	A matching rule; a red highlight on anything invalid	A plain-words message when a value is refused
Checkbox	Checkboxes	Tick boxes
Key (ID)	A rule refusing duplicates; duplicates highlighted	“Each entry in this column must be different.”
Free text	Nothing, or “required”	Blank required cells highlighted
Locked	Protected	A warning or a lock

Options: who may edit locked areas (everyone, with a warning · only me · me and people I name); refuse or allow-and-mark invalid entries; highlight problems; add a Sheet rules tab that links collaborators to why can't I edit this?

What protection can't do

The file's owner can always edit. Google does not allow otherwise.
Anyone with edit access can make a copy, and the copy has no protection.
Viewers and commenters are unaffected: they couldn't edit anyway.
It is guardrails, not security. It prevents accidents; it does not stop a determined editor.
Pasting skips dropdowns (Google's behaviour, not ours). That is exactly why Check exists.

Undo and Remove

Undo last setup (Contract tab, free) takes off everything the last setup or fix added and puts back what was there before: the dropdowns, header notes and frozen rows. Remove guardrails (free) does that for every setup on every tab, then sweeps anything left carrying the add-on's marker. Your own protections and highlight rules are never touched, nor is your data. A Moved by guardrails tab is never deleted — it holds your entries.

Version history (File › Version history) is always there too; the add-on never touches it.

The contract

Your choices are saved with the spreadsheet as a small “contract”, so a second keeper checks against the same rules. It holds the header row, each column's role and allowed values, and any editor emails you named — and never leaves the file. Refresh contract accepts today's sheet after a deliberate change; Export/Import copies a contract to next term's rota; Apply to all tabs named “Week *” sets up forty weekly tabs in one go.

Filter views, not filters

A filter (Data › Create a filter) changes the sheet for everyone. A filter view (Data › Filter views › Create new filter view) changes it only for you, and others can open the same view by its link. In a shared sheet, always use filter views.

Every check

SG-01 · Formula typed over

A cell in a formula column (or a totals row) holds a typed number where its neighbours hold a formula. It stopped updating the moment it was typed, and everything that reads it is quietly wrong. The check compares each cell with the run of formulas around it, including the first and last rows, which Sheets' own checks skip.

By hand: Copy the formula from the cell above or below and paste it back. Then protect the column (Quick protect is free).

SG-02 · Array blocked

An ARRAYFORMULA (or FILTER, QUERY, SEQUENCE…) shows #REF! because something is typed in the cells its results need.

By hand: Move or delete what is typed in its path — the card lists the cells. The formula fills back in by itself. The one-click fix moves the entries to a “Moved by guardrails” tab first, so nothing is lost.

SG-03 · Formula not extended

New rows were added below the formulas: they have entries but their total, status or look-up is blank.

By hand: Copy the last formula cell and paste it down over the new rows. Or convert the column to one ARRAYFORMULA in the first row so it extends itself.

SG-04 · Range stops short

A formula like SUM(F2:F50) or VLOOKUP(…,A2:D100,…) stops at a row, and the data now runs past it.

By hand: Change the range to an open-ended one — F2:F instead of F2:F50 — so rows added later are always included.

SG-05 · Broken references

A formula contains #REF! in its own text: a row, column or tab it used was deleted.

By hand: Open File → Version history to see what was deleted, then point the formula at the right cells again.

SG-06 · Errors spreading

A formula column holds #N/A, #VALUE!, #DIV/0! and friends — and every total that reads the column inherits the error.

By hand: Click the first error and read what it is looking for — usually a missing look-up value, or text where a number should be. Wrap look-ups in IFERROR only once you know why.

SG-10 · Value breaks its column's rule

A value in a column with a dropdown or rule doesn't match it. Pasting skips data validation entirely, so this is how “done”, “Done ” and “Completed” get past a dropdown.

By hand: Pick a value from the list in each cell. For many, use Find and replace with “Match case” and “Match entire cell contents”.

SG-11 · Validation missing on some rows

Most of the column has a dropdown or rule; some rows have none — typically rows inserted or pasted in.

By hand: Copy a cell that has the rule, then Paste special → Data validation only over the rows without it.

SG-12 · Near-duplicate choices

The same answer spelled several ways: case, stray spaces, a slip of a letter. Filters, COUNTIF and pivot tables count them as different answers.

By hand: Use Find and replace on the column with “Match case” and “Match entire cell contents” ticked. The one-click fix previews every change first.

SG-13 · Required cell blank

A column the contract marks as required is empty on a row that has other entries.

By hand: Fill it in, or ask whoever added the row.

SG-14 · Duplicate key

An ID or reference appears more than once (case and spaces ignored). A look-up returns whichever comes first and ignores the other.

By hand: Give each duplicate a new value, or delete the row that was added twice.

SG-15 · Mixed types

Numbers stored as text in a number column, or dates typed as text in a date column. SUM skips them and date filters never match them.

By hand: Retype them, or select them and use Data → Split text to columns so Sheets reads them properly.

SG-20 · Header changed

Compared with the contract: a header was renamed, moved or deleted, or a new column appeared.

By hand: Put the header back — or, if the change was deliberate, Contract → Refresh contract so the check accepts today's sheet.

SG-21 · Sorted out of line

A run of rows where one column no longer matches the row it belongs to — the classic result of sorting one column on its own. The check needs a contract, and only fires on a run of rows, never a single edit.

By hand: Open File → Version history, find the version from just before the sort, and restore it (or copy the column back from it). Don't re-sort by hand: it can make it worse. The add-on deliberately has no automatic fix for this.

SG-22 · Filter left on

A normal filter changes what everyone sees. Collaborators add rows into the gaps or think data is missing.

By hand: Data → Remove filter. For your own filtering use Data → Filter views → Create new filter view: it only changes the sheet for you.

SG-23 · Frozen header lost

The header row was frozen when the guardrails went on and isn't now.

By hand: View → Freeze → 1 row.

SG-24 · Tab renamed, hidden or deleted

A tab this sheet's formulas read was deleted, renamed or hidden since the guardrails went on. Sheets follows a rename in normal formulas, but not in INDIRECT or text that names the tab.

By hand: Restore a deleted tab from Version history; rename it back; unhide it from View → Hidden sheets.

SG-25 · Blank rows splitting the data

An empty row in the middle of the data. Sorting, filters and “select the table” stop at it.

By hand: Delete the empty rows.

SG-26 · Merged cells inside the data

Merged cells stop the table being sorted or filtered — for everyone.

By hand: Select them and choose Format → Merge cells → Unmerge.

SG-30 · Formulas and headers unprotected

Formula columns or the header row have no protection at all.

By hand: Quick protect (free) adds a warning before anyone types over them. Or Data → Protect sheets and ranges by hand.

SG-31 · Warning-only vs locked

Formulas that only warn (one click on OK types over them anyway), or columns people need to fill in that are locked to the owner.

By hand: Data → Protect sheets and ranges → the range → Set permissions: lock formulas once people are used to the sheet; switch input columns to “Show a warning”.

SG-32 · Dead protections

Protections over deleted or empty ranges, protected twice over, or naming editors no longer on the file.

By hand: Data → Protect sheets and ranges: remove or update each one listed.

SG-33 · Stale exceptions

The whole tab is protected except some ranges, but the exceptions stop above where the data now runs. Collaborators can fill in old rows but not new ones.

By hand: Widen each exception to the whole column (C2:C rather than C2:C100).

SG-34 · Protections you can't manage

Set by another editor. The add-on lists them and never touches them.

By hand: Ask whoever set them, or the file's owner.

Support

Email support@appgarden.co.uk. Common questions:

“I'm locked out of my own sheet.” If you're the owner you can always edit. Otherwise ask the keeper, or use Remove guardrails.
“A collaborator can still edit a formula.” The lock is warning-only (the default), or they are the owner.
“The dropdown let a paste through.” Pasting skips validation in Google Sheets™. Check finds them; Merge fixes them.
“I need my licence on another account.” Enter the key there; if all 3 seats are used, email us to free one.