Prevent Google spreadsheet editor from accessing GAS by using detached GAS

Question

The solution is sketched in this post, but it seems key steps are not identified. This code works fine if in GAS bound to the spreadsheet:

function onEdit(e) {
   var sheet      = e.source.getActiveSheet();
   var activeCell = sheet.getActiveCell();
   var row        = activeCell.getRow();
   if(row != 1) {
     sheet.getRange(row, 4).setValue(new Date().toISOString()) 
   }
  
}

In the owner account's G-drive, I created a script as follows:

function onEdit(e) {
   var ss         = SpreadsheetApp.OpenFileById("[spreadsheet ID]");
   var sheet      = ss.getActiveSheet();
   var activeCell = sheet.getActiveCell();
   var row        = activeCell.getRow();
   if(row != 1) {
     sheet.getRange(row, 4).setValue(new Date().toISOString()) 
   }
}

But this script does not set the date value in the active sheet when an edit is made. I deployed this detached script (as webapp) and gave it permission to access the spreadsheet. But I suspect there are other steps or I have not properly deployed the script so that it can respond to edits in the active sheet of the spreadsheet.

Answer 1

onEdit is a reserved keyword used for naming a simple trigger. This kind of trigger can only be used on bounded scripts and on Editor add-ons.

As you are using a standalone project, and assuming that your script is only relevant for a single spreadsheet, instead of using a simple trigger you might use an installable trigger created programmatically.

function createEditTrigger(){
  const spreadsheetId = 'Put here your spreadsheet id';
  const functionName = 'Put here your name of the function to be called on edit';
  ScriptApp.createTrigger(functionName)
  .forSpreadsheet(spreadsheetId)
  .onEdit()
  .create();
}

Notes:

• Don't use onEdit to name the function to be called by an edit installable trigger otherwise the function will be called twice.
• If your script will be used on multiple spreadsheets, please bear in mind that there is a limit on the number of triggers by a user/script Ref. https://developers.google.com/apps-script/quotas

Resources

• https://developers.google.com/apps-script/guides/triggers
• https://developers.google.com/apps-script/guides/triggers/installable
• https://developers.google.com/apps-script/reference/script/trigger-builder?hl=en#forSpreadsheet(String)

Answer 2

You are trying to prevent spreadsheet editors from accessing your code. Simple triggers however require that the script project is bound to the spreadsheet.

The proper way to keep your code out of reach is to deploy your project as an add-on. The trigger will fire in spreadsheets where the add-on has been installed. Note that there is a learning curve with add-ons.

Your code is not taking much advantage of the event object. See onEdit(e) best practices.