Finance teams often need a budget sheet to stay editable right up until a close date and then become read-only automatically, and checkAndFreezeSheetAfterDate does exactly that by comparing today's date against a Freeze Date cell on a schedule.
Manually protecting a sheet on the right day requires someone to remember the exact date, open the spreadsheet, and click through the Protection dialog - a task that's easy to forget during a busy close period, leaving the numbers editable long after they should be final.
This example runs as a time-driven trigger rather than reacting to an edit, checking once a day whether today has reached or passed the value in cell B1, and only then applying a named protection that restricts editing to a single approved editor.
You'll end up with a Budget Q3 sheet that locks itself on schedule, sends a notification email when it does, and avoids re-protecting a sheet that's already been frozen.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Cell/Setting | Sheet | Purpose |
|---|---|---|
| B1 | Budget Q3 | Freeze Date - the date on or after which the sheet locks |
| Protection description | Budget Q3 | 'Auto-freeze after deadline' - used to detect an existing protection |
| Approved editor | Script constant | Single email address allowed to edit after freezing |
What it does
On each scheduled run, the function reads the Freeze Date from B1 and compares it, with time zeroed out, against today's date. If today hasn't reached the freeze date yet, it exits without making any changes. Once the date has passed, it checks for an existing matching protection before creating a new one, so re-running the trigger daily doesn't stack duplicate protections.
- Runs on a schedule, not in response to any specific edit
- Zeroes out the time component so same-day comparisons work correctly
- Checks for an existing protection with a matching description before adding a new one
Prerequisites
Because this uses an installable time-driven trigger rather than a simple trigger, it must be set up through the Apps Script Triggers UI or ScriptApp.newTrigger, and the script's owner needs edit access to change sheet protections.
- A sheet named 'Budget Q3' with a real Date value in B1
- A time-driven trigger calling checkAndFreezeSheetAfterDate daily
- MailApp send permission for the notification email
- The approved editor's email address confirmed correct before deployment
Walkthrough
Both today and target dates get setHours(0, 0, 0, 0) applied so a Freeze Date of, say, July 20th at midnight compares correctly against a trigger that might run at any hour of July 20th - without zeroing the hours, a trigger firing at 2 AM could miss a same-day freeze.
getProtections(ProtectionType.SHEET) returns any existing sheet-level protections, and the loop checks each one's description; matching on a specific string like 'Auto-freeze after deadline' distinguishes protections this script created from ones a human added manually for other reasons.
Edge cases
If someone changes the Freeze Date to an earlier value after the sheet has already been frozen, the existing-protection check still returns early and the sheet stays frozen under the old protection rather than adjusting to the new date.
- Freeze Date left blank or containing text instead of a Date object causes the function to exit silently
- A human-added protection with a different description won't be detected and could result in two separate protections on the same sheet
- Removing the trigger doesn't remove an already-applied protection - unfreezing requires a separate manual or scripted step
Testing
Set B1 to yesterday's date and run the function manually from the Apps Script editor to confirm the sheet becomes protected and the notification email arrives, then run it again immediately to confirm it exits early instead of creating a duplicate protection.
- Test with a Freeze Date equal to today to confirm same-day freezing works
- Test with a future Freeze Date to confirm no protection is applied yet
- Manually remove the protection and re-run to confirm it gets reapplied
Hardening
The approved editor's email is currently hard-coded, which means updating who can still edit after a freeze requires a code change and redeployment rather than a spreadsheet edit.
- Move the approved editor's email into a named range or script property instead of hard-coding it
- Add a Logger.log or a dedicated audit sheet entry every time a freeze is applied, for a paper trail
- Wrap the whole function body in a try/catch so a permission error on one sheet doesn't stop other scheduled scripts from running
Variations
The same date-comparison pattern works for temporary protections too - instead of freezing forever, you could add an unfreeze date in a second cell and remove the protection automatically once that later date arrives.
- Add an Unfreeze Date cell and a matching removeProtection step once that date passes
- Protect only a specific range (like the totals row) instead of the entire sheet
- Freeze multiple sheets from one trigger by looping over a list of sheet names and freeze dates
Full code: checkAndFreezeSheetAfterDate()
The function is idempotent by design - running it daily is safe because it always checks for an existing matching protection before creating a new one.
function checkAndFreezeSheetAfterDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Budget Q3');
var freezeDateCell = sheet.getRange('B1');
var freezeDate = freezeDateCell.getValue();
if (!(freezeDate instanceof Date)) return;
var today = new Date();
today.setHours(0, 0, 0, 0);
var target = new Date(freezeDate);
target.setHours(0, 0, 0, 0);
if (today < target) return;
var existingProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
for (var i = 0; i < existingProtections.length; i++) {
if (existingProtections[i].getDescription() === 'Auto-freeze after deadline') return;
}
var protection = sheet.protect();
protection.setDescription('Auto-freeze after deadline');
protection.removeEditors(protection.getEditors());
protection.addEditor('finance-lead@example.com');
var owner = Session.getEffectiveUser().getEmail();
if (protection.canDomainEdit()) {
protection.setDomainEdit(false);
}
MailApp.sendEmail(owner, 'Sheet frozen', 'Budget Q3 was frozen because the freeze date has passed.');
}- Line 3: Reads the Freeze Date value directly out of cell B1 on the Budget Q3 sheet.
- Line 6: Exits immediately if B1 doesn't contain an actual Date object, avoiding a bad comparison.
- Line 13: Compares zeroed-out dates so the freeze applies correctly regardless of what hour the trigger runs.
- Line 15: Fetches existing sheet-level protections to check whether this script already froze the sheet.
- Line 20: Creates the new protection object that will restrict editing on the sheet.
- Line 23: Grants edit access to exactly one approved editor while everyone else loses edit rights.
- Line 30: Emails the sheet owner a notification confirming the freeze happened and why.
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 scheduling the freeze trigger
- 1Freeze Date cell (B1) contains a real Date value, not text
- 2Time-driven trigger installed and pointed at the right function
- 3Approved editor's email double-checked for typos
- 4Notification email tested and confirmed to arrive
- 5Existing-protection check verified to prevent duplicates
- 6Plan in place for how the sheet gets unfrozen if needed
Frequently asked questions
The freeze needs to happen even if nobody opens or edits the spreadsheet on the freeze date itself, so a time-driven trigger that runs daily regardless of user activity is the only reliable way to guarantee the sheet locks on schedule.
Their open tab keeps showing the old permissions until they refresh or make another edit, at which point Google Sheets will reject the edit and show a 'you don't have permission' message reflecting the new protection.
Yes - call a range's protect() method instead of the sheet's protect() method; the rest of the addEditor and description logic works the same way on a range protection.
Loop through sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET), find the one with the matching description, and call .remove() on it - this needs to be a separate function or manual step since the freeze function itself never removes protections.
Only if canDomainEdit() is left true; the example explicitly disables domain-wide edit access, so only the single addEditor recipient retains edit rights after the freeze takes effect.
Always add the script owner or a service account as one of the approved editors, otherwise a future run of an unfreeze function could fail with a permission error since the script itself may no longer have edit rights.
Not as written - it targets a single spreadsheet via getActiveSpreadsheet, but you can loop over an array of spreadsheet IDs, calling SpreadsheetApp.openById for each and running the same freeze logic against every one.