Automatically protect a cell / range after inputting data in Google Sheets

Question

I have a public spreadsheet, where people can enter their names under the column "Name". Everything in this sheet is protected, except for the cells in the column "Name". Since the spreadsheet is public, I want to avoid a situation where someone can troll and delete all the names that have been inputted. Hence, I'm trying to set up a script using the on edit triggers to protect the cell in this range after anyone has entered their name in a cell. So far I've been manually protecting the cells after a name has been entered.

I've found out that the best way to do this would be to use the on edit trigger. I have used javascript before but as I'm new to google spreadsheet scrips, I can't get my script to run like it's supposed to. The current script is supposed to automatically protect the range on edit, and add a description of when the protection was done. Sample spreadsheet with the script in it here: https://docs.google.com/spreadsheets/d/18NlVKcaeyOkgqIa6WAuDsu5TSYK37m_vXLmp7p7Q8kc/edit?usp=sharing

function protectOnEdit(event) {
  var ss = SpreadsheetApp.getActive();
  var range = ss.getRange('Sheet1!A2:A1000');
  var timeZone = Session.getScriptTimeZone();
  var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
  var description = 'Protected on ' + stringDate;
  var protection = range.protect().setDescription(description);

  // below code taken directly from Google's documentation

  var me = Session.getEffectiveUser();

  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Reference: https://developers.google.com/apps-script/reference/spreadsheet/range#protect()

The data range in question is A2:A1000 and currently it -seems- to partially work, however, it protects the WHOLE range after editing a single cell, instead of just protecting the edited cell like it's supposed to.

Are there any steps I'm missing in order for the script to lock the cells individually, instead of the whole range? Any insights are very appreciated!

Refer my answer (stackoverflow.com/a/57642867/1330560). I think it provided 99% of the information you need. — Tedinoz

Александр Ермолин Александр Ермолин · Accepted Answer · 2019-08-27T14:42:16

I have made some corrections:

function protectOnEdit(e) {
  var range = e.range;
  // Be sure to have edited the sheet "Sheet1"
  if (range.getSheet().getName() != "Sheet1") return;
  // Be sure to have a single cell as edited range
  if (range.getWidth() != 1 || range.getHeight() != 1) return;
  // Be sure to have edited cell inside A2:A1000
  if (range.getColumn() != 1 || range.getRow() < 2) return;
  // Be sure to have non-blank new value
  if (!e.value) return;

  // Protect this range
  var timeZone = Session.getScriptTimeZone();
  var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
  var description = 'Protected on ' + stringDate;
  var protection = range.protect().setDescription(description);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) protection.setDomainEdit(false);
}

At first the script checks several conditions for desired protection. They seem to me to be important for the task. If all of them are TRUE, then protects a single cell. The spreadsheet owner has no restrictions to edit protected cells but other editors have.

Of course, the owner should install a trigger for this function properly to automate the process.

Automatically protect a cell / range after inputting data in Google Sheets

1 Answers