Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users

GasTips / spreadsheet
Google Apps Script / spreadsheet

Gists

This is a method for using OnEdit Trigger to Google Spreadsheet by hiding Google Apps Script from other users.

A sample flow for achieving this is as follows.

Flow

1. Create a new Google Spreadsheet.

Please create a new Google Spreadsheet. In this flow, this Google Spreadsheet is used for testing the script. And, please copy the Spreadsheet ID. This spreadsheet ID is used.

In this case, even when Spreadsheet has no container-bound script, this goal can be achieved. Only the below standalone script can be used.

2. Prepare sample script.

Please create a new standalone script. For example, when you access https://script.google.com/intro, you can create a new Google Apps Script project as a standalone script.

And, please copy and paste the following script to the script editor. And, please set your Spreadsheet ID to spreadsheetId and save the script.

function installTrigger() {
  const spreadsheetId = "###"; // Please set the spreadsheet ID of your Spreadsheet.

  const ss = SpreadsheetApp.openById(spreadsheetId);
  ScriptApp.newTrigger("installedOnEdit").forSpreadsheet(ss).onEdit().create();
}

// In this pattern, please set your script in this function.
function installedOnEdit(e) {
  e.range.setValue(JSON.stringify(e));
}

3. Install the OnEdit trigger.

In order to install the OnEdit trigger, please run installTrigger(). By this, the OnEdit trigger is installed to the function installedOnEdit. By this, when a cell of the above Spreadsheet is edited, installedOnEdit is run.

4. Testing.

Please edit the cell in your Spreadsheet. By this, the script of the standalone script is run, and you can see the event object in the edited cell.

And, in this case, even when you share the Spreadsheet with other users, the users cannot see your standalone script. Because your standalone script is not shared with them. By this, the above goal is achieved.

IMPORTANT POINTS

  • As an important point, it supposes the following sample situation.

    • User “A” is the owner of Google Spreadsheet.

    • User “B” has the writer’s permission for the Spreadsheet of user “A”.

    • Under this condition, when user “A” creates a standalone script including the following script. And, run installTrigger().

      function installTrigger() {
  const spreadsheetId = "###"; // Spreadsheet ID of the owner's spreadsheet.

  const ss = SpreadsheetApp.openById(spreadsheetId);
  ScriptApp.newTrigger("installedOnEdit")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

function installedOnEdit(e) {
  e.range.setValue("ok1");
}

    • And, when user “B” creates a standalone script including the following script. And, run installTrigger().

      function installTrigger() {
  const spreadsheetId = "###"; // Spreadsheet ID of the owner's spreadsheet.

  const ss = SpreadsheetApp.openById(spreadsheetId);
  ScriptApp.newTrigger("installedOnEdit")
    .forSpreadsheet(ss)
    .onEdit()
    .create();
}

function installedOnEdit(e) {
  e.range.offset(0, 1).setValue("ok2");
}

    • In the above situation, when user “A” or “B” edit a cell of the Spreadsheet, the values of ok1 and ok2 are put into the edited cell and the right side of the edited cell. From this situation, it seems that both scripts are run to the same Spreadsheet. It is considered that when other users have the write permission, when the above method is used, the edited information can be retrieved.

  • As another important point, when this method is used,

    • In the case of Google Documents, this can be used as the installed OnOpen trigger.

    • In the case of Google Forms, this can be used as the installed OnOpen trigger and the installed OnSubmit trigger.

    • In the current stage, for example, when the owner of a Spreadsheet is the service account, the Google Apps Script cannot be used. But, it was found that when this method is used, Google Apps Script can be used for such a Google Spreadsheet. In this case, when the above flow is used, the owner of the Spreadsheet is the service account, and the owner of the standalone script is your account.

References

 Share!