1

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.

3
  • Hi, welcome to WebApps. If you have an unbound script then, by definition, there is no spreadsheet for it to monitor for edits. And, you can't install an installable onEdit trigger for the same reason (in fact, an unbound script is limited to "time-driven" or "Calendar" triggers). You could prove/disprove this in your case by including some Logger statements to report the value of variables at different stages of the script. I'm not saying that your goal can't be met (though I am skeptical), but that one needs to think more creatively for solutions.
    – Tedinoz
    Jul 1 at 1:31
  • @Tedinoz It's possible to create installable trigger from a standalone code by using code.
    – Rubén
    Jul 1 at 9:48
  • Argh. Doug, how embarrassing. @Rubén is 100% right and I am 100% wrong - it IS possible to create installable trigger from a standalone code by using code. newTrigger(functionName). In fact, the documentation actually gives the example using a spreadsheet opened by ID! I replicated your scenario and it works perfectly.
    – Tedinoz
    Jul 2 at 5:00

2 Answers 2

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

2
  • This answer provides a working solution within the context of the OP's question and referenced post. @doubleunary remarks that the proper way to keep your code out of reach is to deploy your project as an add-on. Do you have a view on this?
    – Tedinoz
    Jul 2 at 5:10
  • @Tedinoz Creating an add-on is the only way to "hide" the code from spreadsheet editor but I'm not sure that the OP is looking that.
    – Rubén
    Jul 3 at 6:20
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.