Auto-protect ranges after a deadline

When Northwind runs a vendor RFP, suppliers fill in their responses on a shared Google Sheet. The moment the deadline passes, that sheet should be frozen — nobody should be able to slip in a “corrected” figure an hour late, and the evaluators need to know they are scoring the same numbers everyone else saw. Doing this by hand means remembering the exact closing time and being at a keyboard when it arrives, which never quite happens.

This script checks the clock against a fixed deadline and, once that deadline has passed, protects the sheet — stripping every editor except the script owner so the responses are locked in place. Run on a timer, it is a no-op until the deadline and then enforces the freeze automatically.

What you’ll need

• A Google Sheet whose first tab holds the RFP responses, and its ID from the URL.
• A firm closing date and time for the RFP.
• The list of admin email addresses that should keep edit access after the freeze — for adjudication or corrections.

The script

// The spreadsheet holding the RFP responses.
const RFP_SHEET_ID = '1abcRfpSheetId';

// When the RFP closes. Use an explicit timezone offset (here UTC).
const DEADLINE = new Date('2026-06-01T17:00:00Z');

// Admins who keep edit access after the freeze, beyond the script owner.
const ADMIN_EDITORS = ['[email protected]'];

// Description stamped on the protection so its purpose is obvious.
const PROTECTION_NOTE = 'Closed after deadline';

/**
 * If the deadline has passed, protects the RFP response sheet so only
 * the script owner and named admins can still edit it. Does nothing
 * before the deadline.
 */
function protectIfPast() {
  // 1. Bail out early while the RFP is still open.
  if (new Date() < DEADLINE) {
    Logger.log('Deadline not reached yet — nothing to do.');
    return;
  }

  const sheet = SpreadsheetApp.openById(RFP_SHEET_ID).getSheets()[0];

  // 2. Skip if the sheet is already protected — keeps repeat runs clean.
  if (sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET).length > 0) {
    Logger.log('Sheet already protected — nothing to do.');
    return;
  }

  // 3. Protect the whole sheet and label why.
  const protection = sheet.protect().setDescription(PROTECTION_NOTE);

  // 4. Remove every existing editor from the protection.
  protection.removeEditors(protection.getEditors());

  // 5. Re-add the script owner and the named admins.
  protection.addEditor(Session.getEffectiveUser());
  if (ADMIN_EDITORS.length) {
    protection.addEditors(ADMIN_EDITORS);
  }

  Logger.log('RFP sheet protected after the deadline.');
}

How it works

1. protectIfPast compares the current time with DEADLINE. If the deadline has not arrived, it logs and returns — so the function is safe to run on a frequent timer.
2. Once the deadline has passed, it opens the spreadsheet and takes the first tab.
3. It checks whether a sheet-level protection already exists. If one does, the freeze is already in place and the function stops, so repeated timer runs do not stack up duplicate protections.
4. It calls protect() to create a sheet-level protection and stamps it with a description, so anyone inspecting the sheet later sees why it is locked.
5. removeEditors strips every editor the protection inherited, which is what actually locks other people out.
6. It re-adds the script owner with Session.getEffectiveUser() and adds the ADMIN_EDITORS list, so adjudicators keep the access they need.

Example run

The RFP closes at 17:00 on 1 June 2026:

When the timer fires	What happens
31 May, 14:00	Before deadline — logs “nothing to do”, sheet stays editable
1 June, 17:05	After deadline — sheet protected, only owner + admins can edit
1 June, 18:05	Already protected — logs “already protected”, no change

Vendors who open the sheet after 17:00 can still read their responses but can no longer change them.

Trigger it

Run this on a timer so the freeze happens on time, unattended:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose protectIfPast, event source Time-driven, and an Hour timer running hourly.

The script is a no-op before the deadline, so an hourly trigger costs almost nothing and guarantees the sheet is locked within an hour of closing. For a tighter cut-off, use a minutes timer instead.

Watch out for

• An hourly trigger means the sheet can stay editable for up to an hour past the deadline. If the cut-off must be exact, run the trigger every 5 minutes, or note the closing time as “by the next sweep”.
• Protection applies to the whole first tab only. If responses live on several tabs, loop over getSheets() and protect each, or protect specific ranges with getRange().protect() instead.
• removeEditors does not affect the spreadsheet’s owner or anyone with full ownership rights — they can always edit. It also cannot lock out a Workspace super-admin.
• The deadline is hard-coded in DEADLINE. For a recurring RFP, move it into a Script Property or a cell so you can change it without editing code.
• Once protected, the freeze stays until someone removes the protection by hand. There is no automatic un-freeze — that is deliberate, but worth remembering if you reuse the sheet.