Using OnEdit trigger on Google Spreadsheet Created by Service Account

Gists

In the current stage, by the current specification, Google Apps Script cannot be directly run on Google Spreadsheet created by Service Account. But, there is a case in that we want to use the OnEdit trigger on the Spreadsheet that the service account is the owner. In this post, I would like to introduce the method for achieving this.

Recently, I published “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users”. Here, this method is used.

In order to test this, please do the following flow.

Flow

1. Create a new Google Spreadsheet by service account.

Please create a new Google Spreadsheet by the service account. And, please copy the Spreadsheet ID. In this case, the owner of the Spreadsheet is the service account.

And, please share this Spreadsheet with your Google account as a writer. This is an important point.

2. Create a standalone script by your account.

Please create a new standalone script with your account. In this case, the owner of the standalone script is your account.

And, please copy and paste the following script to the script editor. And, please set the Spreadsheet ID of Spreadsheet of the service account 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 the Spreadsheet of the service account. By this, the script of your standalone script is run, and you can see the event object in the edited cell.

By this flow, it was found that the installable OnEdit trigger can be used on Google Spreadsheet created by the service account.

References

 Share!