How to run an onEdit function across multiple Google Sheets using a shared Apps Script library

I have 8 different Google Sheets with the same structure, and I want to run the same onEdit() trigger-based script in all of them.

However:

• I don’t want users to access or modify the script because of sensitive data.
• So, I created a standalone Apps Script project (not bound to any specific sheet), pasted my logic there, and added it as a library in all 8 Sheets.

Each sheet now includes this library. My goal is to have the onEdit() trigger in each spreadsheet call a function from the library, so the logic is centralized and protected.

My Question: Do I need to manually create a basic onEdit(e) function in each spreadsheet that calls the library function, or is there a way to automatically bind the trigger to the library without modifying each sheet?

Requirements:

• Users should not see or edit the script logic.
• The onEdit() function should work as if it’s native to the sheet, but the logic should live in the library.

Thanks in advance!

From your question, I guessed your expected result is as follows.

• You have 8 Google Spreadsheets.
• You have a function with the OnEdit trigger. This function can be used for all Spreadsheets.
• You want to use this function for 8 Google Spreadsheets. Also, you don't want to show the script.

In this case, how about using the installable OnEdit trigger? The installable trigger can be also installed by a Google Apps Script project different from the target Google Apps Script project. So, I guessed that when the installable OnEdit trigger is used, your goal might be able to be achieved. The references are as follows.

• My answer: Creating an onEdit trigger on a sheet owned by a service account
• My blog: Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users

When this is used, the usage is as follows.

1. Prepare Google Apps Script project.

From I created a new appscript project from google drive pasted my script there, I guessed that you already had a Google Apps Script project. You can use this project.

2. Sample script.

Please copy and paste the following script to the script editor of the Google Apps Script project. And, please set the 8 Spreadsheet IDs of your Spreadsheets to spreadsheetIds. And, please set your OnEdit script to the function installedOnEdit. Please be careful about the variable name of the event object.

// This function is used as the OnEdit trigger.
function installedOnEdit(e) {

  // Please set your script of OnEdit.

}

function sample() {
  const spreadsheetIds = ["### spreadsheetId 1 ###", "### spreadsheetId 2 ###", "### spreadsheetId 3 ###",,, ];
  const functionName = "installedOnEdit";

  spreadsheetIds.forEach(id => {
    const ss = SpreadsheetApp.openById(id);
    ScriptApp.newTrigger(functionName).forSpreadsheet(ss).onEdit().create();
  });
}

• When sample function is run, the function installedOnEdit is installed to the 8 Spreadsheets as the installable OnEdit trigger. And, for example, when one of the Spreadsheets is edited, the function installedOnEdit is automatically run.

Note:

• For example, when you wanted to use the OnEdit script for only one Google Spreadsheet, please set the Spreadsheet ID to spreadsheetIds like const spreadsheetIds = ["### spreadsheetId 1 ###"];.

• In this case, it supposes that your script of OnEdit works fine and you have permission to use 8 Google Spreadsheets. Please be careful about this.

References:

• Installable Triggers
• newTrigger(functionName)
• Class TriggerBuilder