Hello I am looking for a specific functionality in google sheets. I tried to make a script that on every edit, if the cell is empty everyone has access to edit it. If someone edits it, only that person and the owner can edit it. I have to mention that every user is anonymous, and I can’t get the emails. This script’s scope is to limit the access of an edited cell so that users can’t modify each other’s cells, the first who types in that cells has control over it.

I tried to use a workaround with the protections, but that worked partially, nobody could edit the owner’s cells but everyone could write on top of each other’s cells. Got errors on the trigger for every person except the owner: you can’t remove yourself as an editor and Cannot read properties of undefined (reading ‘columnStart’) and the parameters (String,number) doesn’t correspond with the signature of the method SpreadsheetApp.Spreadsheet.getRange. at onEdit

This is the code I tried to edit it to make it work. The code was taken from this stackoverflow post and was written by Wim den Herder: Get the user that changed specific cell

// Test it with colors
// var edittedBackgroundColor = "RED"; // makes the change visible, for test purposes
// var availableBackgroundColor = "LIGHTGREEN"; //  makes the change visible, for test purposes

function onEdit(e) {
  Logger.log(JSON.stringify(e));
  var alphabet = "abcdefghijklmnopqrstuvwxyz".toUpperCase().split("");
  var columnStart = e.range.columnStart;
  var rowStart = e.range.rowStart;
  var columnEnd = e.range.columnEnd;
  var rowEnd = e.range.rowEnd;
  var startA1Notation = alphabet[columnStart-1] + rowStart;
  var endA1Notation = alphabet[columnEnd-1] + rowEnd;
  var range = SpreadsheetApp.getActive().getRange(startA1Notation + ":" + endA1Notation);

  if(range.getValue() === "") {
    Logger.log("Cases in which the entry is empty.");
    if(typeof availableBackgroundColor !== 'undefined' && availableBackgroundColor) 
      range.setBackground(availableBackgroundColor)
    removeEmptyProtections();
    return;
  }

  // Session.getActiveUser() is not accesible in the onEdit trigger
  // The user's email address is not available in any context that allows a script to run without that user's authorization, like a simple onOpen(e) or onEdit(e) trigger
  // Source: https://developers.google.com/apps-script/reference/base/session#getActiveUser()

  var protection = range.protect().setDescription('Cell ' + startA1Notation + ' is protected');
  if(typeof edittedBackgroundColor !== 'undefined' && edittedBackgroundColor)
    range.setBackground(edittedBackgroundColor);

  // Though neither the owner of the spreadsheet nor the current user can be removed
  // The next line results in only the owner and current user being able to edit

  protection.removeEditors(protection.getEditors());
  Logger.log("These people can edit now: " + protection.getEditors());

  // Doublecheck for empty protections (if for any reason this was missed before)

  removeEmptyProtections();
}

function removeEmptyProtections() {
  var ss = SpreadsheetApp.getActive();
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    if(! protection.getRange().getValue()) {
      Logger.log("Removes protection from empty field " + protection.getRange().getA1Notation());
      protection.remove();
    }
  }
  return;
}

function isEmptyObject(obj) {
    for(var prop in obj) {
        if(obj.hasOwnProperty(prop))
            return false;
    }
    return JSON.stringify(obj) === JSON.stringify({});
}