How to Automatically Lock Specific Cells After Data is Entered in Google Sheets

When collaborating on a shared Google Sheet, you might want to allow your team to input new data without giving them the ability to accidentally overwrite or delete information that has already been submitted. Unfortunately, Google Sheets does not have a built-in “lock upon entry” button. However, you can achieve this exact functionality by combining Data Validation rules with a custom Apps Script to automatically lock a cell the moment data is typed into it.

How the Locking Script Works

Because native protections in Google Sheets require manual configuration for every single cell, we must use Google Apps Script to automate the process. We will write a short script that uses an onEdit trigger. Whenever a user types something into a designated column, the script will instantly wrap a protection rule around that specific cell, restricting edit access exclusively to you (the spreadsheet owner).

How to Create the Apps Script

Before you begin, ensure you are the owner of the Google Sheet, as only the owner can apply hard permissions.

1. Open your Google Sheet.
2. Click on Extensions in the top menu and select Apps Script. A new tab will open containing a blank code editor.
3. Delete any existing code in the editor and paste the following script:

   function onEdit(e) {
     // Define which column you want to lock (e.g., Column A = 1, Column B = 2)
     var targetColumn = 1;
     var sheet = e.source.getActiveSheet();
     var cell = e.range;
   
     // Check if the edited cell is in the target column and is not empty
     if (cell.getColumn() == targetColumn && cell.getValue() !== "") {
       var protection = cell.protect().setDescription('Automatically Locked');
       var me = Session.getEffectiveUser();
       
       // Remove all other editors and only allow the owner
       protection.addEditor(me);
       protection.removeEditors(protection.getEditors());
       
       if (protection.canDomainEdit()) {
         protection.setDomainEdit(false);
       }
     }
   }

4. In the code above, change the targetColumn variable to match the column you want to protect (A=1, B=2, C=3, etc.).
5. Click the Save icon (the floppy disk) in the toolbar.

How to Test the Automation

Close the Apps Script tab and return to your Google Sheet. Share the document with a colleague and grant them “Editor” access.

When your colleague types data into a blank cell in your target column and presses Enter, the script will run silently in the background. If they immediately try to delete what they just typed, Google Sheets will block them, displaying a warning message that they do not have permission to edit the protected range. As the owner, you can still edit or delete the cell at any time.