Google Apps Script: onEdit breaks on SpreadsheetApp.openById() - Need input on Installable onEdit() Trigger

Question

I have a Spreadsheet with the following data's in each column, "A_email", "B_file_id", "C_Status", "D_Action" and "E_Result". I framed a script to add/remove editors (email - A column) on the google spreadsheets listed (File ID - B Column) based on the value in field "Status" (Enable/Disable - C Column). I can able to execute the desired function for all the rows (around 250) with the conventional script. But, I need to update only a particular row and so, i modified the script with onedit() trigger. The script fails on the line SpreadsheetApp.openById(). I came to know that i have to use onEdit() as Installable Triggers. As I am a beginner in Coding, Can anyone help me out to get the following corrected?

function onEdit(e) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var myRange = sheet.getRange("D2:D5"); 
  var row = e.range.getRow();
  var col = e.range.getColumn();

  if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) { 
    var file_id = sheet.getRange(row,2).getValue();
    var status = sheet.getRange(row,3).getValue();
    var action = sheet.getRange(row,4).getValue();
    var email = sheet.getRange(row,1).getValue();
    
    var file_1 = SpreadsheetApp.openById(file_id);

    if (action == "Enable") {
      const add_editor = [email];                                                   
      file_1.addEditors(add_editor); 
    }
    else if(action == "Disable"){
      var editors = file_1.getEditors();
      for (j = 0; j < editors.length; j++) {
        file_1.removeEditor(editors[j]);
      } 
    }   
    var update_range = sheet.getRange(row, 5).setValue("Updated");
  } 
}

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEdit')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

I can't able to grasp the concept through https://developers.google.com/apps-script/guides/triggers/installable#new-editor

Answer 1

Step 1:

Rename onEdit(e) to a different name for example myFunction(e) since you want to create an installable trigger and not a simple one. Your code can be improved a little bit to take advantage of the event object. Don't execute myFunction(e) as this will be a trigger function, namely it will be executed (triggered) itself upon edits. You will get errors if you execute it manually and it won't work anyways.

function myFunction(e) {
  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();

  // change "Sheet1" to the specific name
  if (sheet.getName() == "Sheet1" && col == 4 && row >= 2 && row <= 5) { 
    var file_id = sheet.getRange(row,2).getValue();
    var status = sheet.getRange(row,3).getValue();
    var action = sheet.getRange(row,4).getValue();
    var email = sheet.getRange(row,1).getValue();
    
    var file_1 = SpreadsheetApp.openById(file_id);

    if (action == "Enable") {
      const add_editor = [email];                                                   
      file_1.addEditors(add_editor); 
    }
    else if(status == "Disable"){
      var editors = file_1.getEditors();
      for (j = 0; j < editors.length; j++) {
        file_1.removeEditor(editors[j]);
      } 
    }   
    var update_range = sheet.getRange(row, 5).setValue("Updated");
  } 
}

Please note here I added an extra checker sheet.getName() == "Sheet1" to ensure that the script will run the code inside the if only upon edits on "Sheet1". Change that to the name of your sheet you want to work with or remove this condition if you want to apply your script to every sheet in the document.

Step 2:

Execute only the following function one time. You don't want to create multiple triggers for the same function. Also make sure to clear all currently created onEdit triggers you may have accidentally created. And execute only and once the following:

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myFunction')
    .forSpreadsheet(ss)
    .onEdit();
}

After both steps, the block of code under the main if statement will be executed upon edits on the range D2:D4 in the sheet "Sheet1".

How to execute the particular function alone?

Just select the function you want to run and click the play button:

how to clear all currently created onEdit triggers?

Click on the current project's triggers:

and delete all the triggers you see in this list (if any).