Workaround: Automatically Installing OnEdit Trigger to Copied Google Spreadsheet using Google Apps Script

GasTips / Spreadsheet / workaround
Google Apps Script / Spreadsheet

Gists

This is a workaround for automatically installing the OnEdit trigger to the copied Google Spreadsheet using Google Apps Script.

The sample situation for this workaround is as follows.

  • You have a Google Spreadsheet.
  • Your Spreadsheet is shared with a user as the writer.
  • Your Spreadsheet has a button for executing a script for copying the active Spreadsheet.
  • Your Spreadsheet has a function installedOnEdit for executing by the installable OnEdit trigger.
  • You want to make the user copy the active Spreadsheet by clicking the button, and also, you want to automatically install the OnEdit trigger to installedOnEdit for the copied Spreadsheet, simultaneously.

This method is from “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users (Author: me)” and “Using OnEdit trigger on Google Spreadsheet Created by Service Account (Author: me)”.

Usage

Please do the following flow.

Preparation

  1. Please create a new Google Spreadsheet and please copy the paste the following script to the script editor and save the script. In this case, you are the owner of the Spreadsheet.

    // This is a simple sample script for executing by the installable OnEdit trigger.
function installedOnEdit(e) {
  const { range } = e;
  range.setValue(`Updated ${range.getValue()}`);
}

// It supposes that this function is run by clicking a button by a user.
function copySpreadsheet() {
  const functionName = "installedOnEdit"; // This function is installed as OnEdit trigger.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const copiedSpreadsheet = ss.copy("Copied_" + ss.getName());
  ScriptApp.newTrigger(functionName)
    .forSpreadsheet(copiedSpreadsheet)
    .onEdit()
    .create();
  const html = `<a href="${copiedSpreadsheet.getUrl()}" target="_blank">Open copied Spreadsheet</a>`;
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(html).setWidth(300).setHeight(100),
    "sample"
  );
}

  2. Please share this Spreadsheet with another user as a writer.

  3. Please set the sheet name. And, please install OnEdit trigger to the function installedOnEdit. And, please create a button and assign the function name of copySpreadsheet to the button. By this, when this button is clicked, the script of copySpreadsheet is run.

  4. As a test, please put a value of “sample” to a cell of “Sheet1”. And, please confirm that the inputted value of “sample” is changed to “Updated sample”. By this, the preparation is finished.

Testing

As a test, please open the Spreadsheet with the shared user. And, please click a button. By this, copySpreadsheet is run. And, the active Spreadsheet is copied and the function installedOnEdit is installed as the OnEdit trigger. In this case, the owner of the copied Spreadsheet is the user who clicked the button, because when a script is run by a button, the script is run as the user who clicked. This method uses this situation.

When this method is used, the following result is obtained.

IMPORTANT

  • As an important point, in this method, the script of installedOnEdit on the original Spreadsheet (the owner of this Spreadsheet is you.) is used as the installable OnEdit trigger. So, when you modify the script of installedOnEdit, installedOnEdit for the copied Spreadsheet is also changed. Please be careful about this.

  • As the current specification of Google side, the maximum number of triggers which can be installed in one Google Apps Script project is 20. Ref

References

 Share!