The Danger of Shared Spreadsheets
Collaboration is Google Sheets’ greatest strength, but it is also its greatest vulnerability. If you build a complex financial dashboard and share it with ten colleagues, it is almost guaranteed that someone will accidentally delete a crucial formula or type a number over a static header, breaking the entire model.
Google Sheets allows you to manually protect specific ranges (highlighting a column, right-clicking, and selecting “Protect range”), which prevents other users from editing those cells. However, if your spreadsheet dynamically generates new data every day (e.g., adding a new row of calculations automatically), you cannot expect a human to manually lock the new rows every single morning.
Using Google Apps Script, you can build an automated guardian. You can write a script that analyzes the spreadsheet, finds the crucial data, and applies cell protection dynamically and programmatically.
Step 1: The Automation Scenario
Assume you have a sheet named “Sales Data”. Column A contains the “Date”, and Column B contains a complex “Total Revenue” formula.
You want to allow your team to add new rows of data at the bottom of the sheet, but you want to completely lock Column B so no one can ever overwrite the revenue formula.
Step 2: Write the Protection Script
- Open your Google Sheet.
- Click on Extensions > Apps Script.
- Delete the default code and paste the following script:
function protectFormulaColumn() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales Data");
// 1. Define the range we want to protect (e.g., Column B from row 2 down to row 1000)
var rangeToProtect = sheet.getRange("B2:B1000");
// 2. Apply the protection to the range
var protection = rangeToProtect.protect();
// 3. Give the protection a description (visible in the "Protected sheets and ranges" menu)
protection.setDescription("Automated Formula Lock");
// 4. Remove all editing permissions from everyone except the owner (you)
protection.removeEditors(protection.getEditors());
// Ensure the script owner (you) can still edit it
var me = Session.getEffectiveUser();
protection.addEditor(me);
// Optional: If you want to explicitly allow one specific manager to edit it:
// protection.addEditor("[email protected]");
Logger.log("Column B is now fully protected.");
}
Understanding the Code
rangeToProtect.protect()creates the actual protection object on the grid.protection.getEditors()fetches an array of every user who currently has access to edit the sheet.protection.removeEditors()instantly revokes edit access to that specific range for all of those users. They can still view the data, and they can still edit Column A, but if they try to type in Column B, Google will block them.
Step 3: Handling Duplicate Protections
If you run the script above five times, Google Sheets will stupidly apply five overlapping layers of protection to the exact same column, cluttering your settings.
To make the script bulletproof (especially if you want it to run automatically on a trigger), you must tell it to search for existing protections and delete them before applying the fresh lock.
Replace the code with this advanced version:
function refreshProtections() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sales Data");
// 1. Find all existing protections on this sheet
var existingProtections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
// 2. Loop through and delete any protection named "Automated Formula Lock"
for (var i = 0; i < existingProtections.length; i++) {
if (existingProtections[i].getDescription() == "Automated Formula Lock") {
existingProtections[i].remove();
}
}
// 3. Apply the fresh protection (dynamically finding the last row of data)
var lastRow = sheet.getLastRow();
// Only protect if there is actually data in the sheet
if(lastRow > 1) {
var newRange = sheet.getRange(2, 2, lastRow - 1, 1); // Row 2, Column B
var newProtection = newRange.protect();
newProtection.setDescription("Automated Formula Lock");
newProtection.removeEditors(newProtection.getEditors());
newProtection.addEditor(Session.getEffectiveUser());
}
}
Click the Save (floppy disk) icon.
Step 4: Execute the Lockdown
Click the Run button at the top of the editor. You will be prompted to authorize the script. Once authorized, return to your Google Sheet.
Right-click anywhere in Column B and select View more cell actions > Protect range. The sidebar will open, and you will see your “Automated Formula Lock” perfectly applied.
If any other user opens this spreadsheet, they will see a faint striped pattern over Column B (indicating it is locked), and their keyboard inputs will be ignored.
Conclusion
By scripting your cell protections, you bridge the gap between collaborative freedom and data integrity. Apps Script ensures that your complex models are shielded from human error, allowing you to distribute powerful spreadsheets without the anxiety of accidental sabotage.