How to Automatically Lock Cells in Google Sheets Right After Editing (Simple Script Guide)

Last summer, my colleague Mark accidentally wiped out an entire column of complex lookup formulas in our shared client tracker. It happened at 4:30 PM on a Thursday—just as we were preparing to send our monthly performance deck to a major account. We spent two painful hours manually restoring cell histories and fixing broken references.

That exact evening, I promised myself I’d find a better way. Sure, Google Sheets lets you protect ranges manually. But who has time to right-click, select protection options, and add permissions every single time someone types in a new row?

I wanted something slick: the minute someone inputs data into a cell, boom—it automatically locks down. No accidental backspaces, no copy-paste disasters, and no ruined formulas.

If you’ve felt that same cold sweat after seeing a #REF! error caused by a teammate's errant keystroke, you’re in the right place. Let's build an automatic locking mechanism using a short, painless Google Apps Script.

Why Standard Google Sheets Protection Falls Short

Don't get me wrong; built-in Google Sheets protection tools are fantastic for static data. If you have a budget template where Column A and B should never change, locking those ranges ahead of time is straightforward.

However, collaborative workflows are rarely static. You have dynamic sheets where team members daily enter new sales leads, project updates, or inventory figures.

Manually locking every cell after entry requires constant vigilance. It’s tedious work. You miss one row, and suddenly a team member accidentally pastes plain text over a critical VLOOKUP formula. We need an automated safeguard that acts like a digital concrete layer over freshly poured data.

The Secret Sauce: Google Apps Script

To make auto-locking work, we turn to Google Apps Script—Google's lightweight JavaScript-based development platform built right into Workspace apps.

Don't panic if you aren't a programmer! You don't need a computer science degree to copy, paste, and adjust three lines of text. I've streamlined this script so you can drop it into your sheet and have it running in under three minutes.

Here is how it works under the hood: every time a user edits a cell, a trigger fires off a script. The script checks if the cell contains data, applies range protection to that specific cell (or row), and restricts editing privileges exclusively to you (the sheet owner).

Pro Tip: Always test your script on a dummy duplicate sheet before deploying it to your main operational file. This gives you a safe playground to make sure permissions align with your team's workflow without accidentally locking yourself out of critical data.

Step-by-Step Guide: Setting Up Your Auto-Lock Script

Ready to secure your spreadsheet? Follow these numbered steps precisely.

1. Open your Google Sheet. Navigate to the spreadsheet where you want to enforce automatic cell locking.
2. Launch the Script Editor. In the top menu bar, click on Extensions, then select Apps Script. A new browser tab will open displaying the editor window.
3. Clear the default code. Delete any boiler-plate code (like function myFunction() {}) inside the Code.gs file.
4. Paste the custom script. Copy the code block below and paste it directly into the blank editor window:

function autoLockOnEdit(e) {
  // Get active sheet and edited range
  var sheet = e.source.getActiveSheet();
  var range = e.range;

  // TARGET SHEET NAME: Replace 'Tracker' with your tab name
  if (sheet.getName() === "Tracker") {

    // Check if cell is filled and not already protected
    if (range.getValue() !== "") {
      var protection = range.protect().setDescription('Auto-Locked Cell');

      // Set active owner as sole editor
      var me = Session.getEffectiveUser();
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());

      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
  }
}

5. Customize your sheet name. Change "Tracker" on line 7 to match the exact name of the tab you want to monitor (e.g., "Q3 Budget" or "Data Input").
6. Save the project. Click the Save icon (the small floppy disk) at the top of the editor interface.
7. Create an Installable Trigger. On the left menu of the Apps Script page, click the Triggers icon (it looks like a small alarm clock).
8. Add a new trigger. Click the blue + Add Trigger button in the bottom right corner.
9. Configure trigger settings. Set Choose which function to run to autoLockOnEdit. Set Select event source to From spreadsheet. Set Select event type to On edit.
10. Save and authorize. Click Save. Google will prompt you to authorize permissions. Click through your account settings, select Advanced, and click Go to Project (unsafe) to grant the script permission to manage protection settings on your behalf.

Breaking Down How the Code Protects You

Understanding what code does makes you far more confident using it.

The line range.protect() establishes a fresh security perimeter around whatever single cell or range was just updated.

Next, protection.addEditor(me) assigns ultimate control to your Google account, while protection.removeEditors(...) strips away permissions from everyone else—including the person who just entered the data. The instant they hit Enter or press Tab, their edit rights on that specific block disappear.

Advanced Tweaks: Customizing Your Auto-Lock Rules

Not every team needs every single cell locked instantly. Sometimes, you only want to lock cells within a specific column, like an "Approved Status" or "Price" column.

You can easily restrict the trigger by adding a simple column condition. Here is how you modify the conditional statement:

// Lock cells only in Column 3 (Column C)
if (sheet.getName() === "Tracker" && range.getColumn() === 3) {
  // Locking code goes here...
}

By adding && range.getColumn() === 3, the script will completely ignore entries made in Columns A, B, D, or E, locking down only when someone edits Column C.

I personally use this tweak for signed client contracts: contractors fill in their rates and contact info freely in Columns A through D, but once they enter their final billing confirmation in Column E, that entire row freezes permanently.

Frequently Asked Questions (FAQ)

Can users unlock cells if they make a mistake while typing?

Once the auto-lock script fires, regular editors cannot unlock the cell themselves. If a teammate makes a typo, they will need to request edit access from you, or you (as the sheet owner) will need to manually remove protection or fix the typo for them.

Will this auto-lock script work on the Google Sheets mobile app?

Yes! Because installable On edit triggers execute on Google's cloud servers rather than within your desktop browser, any edits made through the Google Sheets iOS or Android app will trigger the auto-lock routine identically.

Does running this script slow down performance on large spreadsheets?

If you are editing dozens of cells per minute simultaneously with a massive team, Apps Script execution times might experience a slight delay (1–2 seconds per cell). For heavy bulk entries, it is often better to lock entire ranges periodically rather than locking cell-by-cell on edit.

Final Thoughts

Setting up automated cell locking transformed how my team collaborates. We no longer spend Friday evenings hunting down who accidentally deleted dynamic formulas or typed over historical revenue numbers.

Give this script a shot on your busiest collaborative sheet this week. It takes less than five minutes to set up, but it will save you countless hours of spreadsheet troubleshooting down the road. If you run into any hiccups while setting up your triggers, feel free to adapt the column filters to fit your unique workflow!