Is is possible in google sheet to protect certain columns, but allow rows to be added/removed?

Question

I'm using arrayformulas to populate certain columns of my sheet, depending on values in other sheets. Occasionally users accidentally will put some value in these columns, and this will stop arrayformula from working. I want to protect these columns, but still allow adding/editing/deleting rows.

Consider this example spreadsheet: I want Id row to be protected, but allow addition/deletion of rows.

https://docs.google.com/spreadsheets/d/1Dnj7OE5XZL09gllHVwPgv-5GRoM-lxVCxTCI_-kURdM/edit#gid=0

Is this possible at all with Google Sheets?

Jose Vasquez Jose Vasquez · Accepted Answer · 2021-01-07T14:24:52

You can't directly disable input but you can use Data Validation instead

By going to Data > Data Validation and filling it with the following:

Cell range: YourSheet!C2:C

Criteria: Custom formula is - =C2:C = A2:A & "["&B2:B&"]"

On invalid data: Reject input

Appeareance: Optional message

Once you've done this, try to fill some cell in the C column and you'll see a message: There was a problem - Your optional message

As a different approach you can use Apps Script Simple Triggers

By going to Tools > Script Editor and copying the following code:

function onEdit(e) {
  var column = e.range.getColumn();
  var sheet = SpreadsheetApp.getActiveSheet();
  if (column === 3) {
    e.range.setValue("");
  }
}

Which is more like an "undo" function.

References

Simple Triggers

Event Objects > onEdit

Is is possible in google sheet to protect certain columns, but allow rows to be added/removed?

2 Answers

You can't directly disable input but you can use Data Validation instead

As a different approach you can use Apps Script Simple Triggers

References