1

here my specific case:

  1. I have some range protected in google sheets
  2. I need to replace some specific Editor if is editor of those range (var Editor2Replace and Editor2Add are emails)
  3. Logically I tried to, for each sheet:
  • Cycle (FOR) of all the protected range (counter p)
  • For each protected range catch current editors and have it in array
  • Of the Editors read the email ==> this is what generate the mistake
  • Cycle (FOR) all the editors looking if someone of those is == Editor2Replace (that is an email)

Here the code, but something is logically wrong, I doubt in what is an array and what not..

   var Protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var p = 0; p < Protections.length; p++) {
      var Protection_Desc = Protections[p].getDescription();
      var Protection_Editors = [];
      var Protection_Editors = [Protections[p].getEditors()];
      for (var r = 0; r < Protection_Editors.length; r++){
         var Protection_Email[r] = [Protection_Editors[r].getEmail()];
         if (Protection_Idontknow == Editor2Replace){
          Protections[p].addEditor = Editor2Add;
          Protections[p].removeEditor = Editor2Replace;

          var Protection_Range = Protections[p].getRange();
          var Protection_Row = Protection_Range.getRow();
          var Owner1 = sheet.getRange(Protection_Row,5).getValue();
          var Owner2 = sheet.getRange(Protection_Row,6).getValue();
          if (Owner1 == Editor2Replace){
              sheet.getRange(Protection_Row,5).setValue(Editor2Add);
          }
          if (Owner2 == Editor2Replace){
              sheet.getRange(Protection_Row,6).setValue(Editor2Add);
          }
        }
      }

Many thanks for hepling

aynber
  • 22,380
  • 8
  • 50
  • 63
Andrea Miassot
  • 13
  • 2

1 Answers1

0

There were a lot of issues in your script and I will enumerate them one by one. Also, I was able to replace a user in the protected sheet by modifying your script.

Issues:

  1. Duplicate declaration
var Protection_Editors = [];
var Protection_Editors = [Protections[p].getEditors()];
  1. Storing the returned value (array) in another array (which should not be done in your issue, it doesn't help you with anything)
var Protection_Editors = [Protections[p].getEditors()];
...
var Protection_Email[r] = [Protection_Editors[r].getEmail()];
  1. Newly declared variable having an index (which I don't understand why)
var Protection_Email[r] = [Protection_Editors[r].getEmail()];
  1. Variable not declared Protection_Idontknow
if (Protection_Idontknow == Editor2Replace){
  1. Incorrect usage of methods addEditor and removeEditor
Protections[p].addEditor = Editor2Add;
Protections[p].removeEditor = Editor2Replace;

Below code should fix those issues (added some comments):

Code:

  var Protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var p = 0; p < Protections.length; p++) {
    var Protection_Desc = Protections[p].getDescription();
    // returned value of getEditors is already an array, return as is
    var Protection_Editors = Protections[p].getEditors();
    for (var r = 0; r < Protection_Editors.length; r++) {
      var Protection_Email = Protection_Editors[r].getEmail();
      // compare current email with the one you want to replace
      if (Protection_Email == Editor2Replace) {
        // add new and remove the one to replace
        Protections[p].addEditor(Editor2Add);
        Protections[p].removeEditor(Editor2Replace);
      }
    }
  }

Note:

  • I have removed anything that were unrelated to the replacement of editors.

Reference:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • Hi @AndreaMiassot, if we answered your question, please click the accept button. By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. [how to accept answer](https://stackoverflow.com/help/accepted-answer) – NightEye Apr 29 '21 at 14:12