Set trigger in script in Google Sheets programmatically

Question

I am trying to get a Google Apps Script to trigger on edit of a particular cell. Right now I have achieved this using a run-time trigger option built in Google Sheets. However, I lose the run-time trigger when I make a copy. Hence, I have managed to find a programmable trigger from various sources and patched something together, but it is not working. I have no programming knowledge, so I am unable to understand where I am going wrong.

My objective is to run the script when user edits the Named Range "monthfilter".

With script trigger creation

function HideColumns() {
  //open the current spreadsheet and get the value of the named range and trigger project
   var ss = SpreadsheetApp.getActive();
   ScriptApp.newTrigger("monthfilter").forSpreadsheet(ss).onedit() 
   var name = ss.getRangeByName("monthfilter");
   var namevalue = name.getValue();

My previous function:

function HideColumns() {
  //open the current spreadsheet and get the value of the named range
  var ss = SpreadsheetApp.getActive();
  var name = ss.getRangeByName("monthfilter");
  var namevalue = name.getValue();

  //show or hide February
  if (namevalue == "February") {
    var sheet = ss.getSheetByName("MIS");
    sheet.hideColumns(30);
    /** other stuff */

Carr Carr · Accepted Answer · 2018-03-06T12:53:46

Not sure what is the 'runtime trigger' you mean, there are three kinds of trigger:

Simple trigger
Installable trigger
Time driven trigger

onEdit(e) is just a Simple trigger.

Let's take a look to the following situation:

yourMainSpreadsheet -   (You are the owner)
                     |
           binding script functions: - 1. function makeCopy( ) {...}
                                       2. function monthFilter(e) {...}
                                       3. function onEdit(e) { monthFilter(e); }

                            
                              |
                              |  After copying, new spreadsheet remains all functions and simple trigger.
                              V


newSpreadsheet-   (You are the owner, maybe share with other editors.)
               |
     binding script functions: - 1. function makeCopy() {...}
                                 2. function monthFilter(e) {...}
                                 3. function onEdit(e) { monthFilter(e); }

No matter what kind of trigger you use or just manually run makeCopy() in yourMainSpreadsheet, onEdit(e) would be copied to new spreadsheet too, and it works when you or other editors make edits, you don't need to do anything:

function makeCopy(){
    var mainSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var newSpreadsheet = mainSpreadsheet.copy('newSheet');
}


function monthFilter(e) {
    var thisSheet = e.source.getActiveSheet();
    var name = thisSheet.getRangeByName("rangeMonthfilter");
    var nameValue = name.getValue();
                //...
}

function onEdit(e) {
    monthFilter(e);
}

What you have done is set a installable trigger to new spreadsheet.

Highlight of documentation about difference between them.

Installable triggers, however, offer more flexibility than simple triggers: they can call services that require authorization ...

For example:

function monthFilter(e) {
    var thisSheet = e.source.getActiveSheet();
    var name = thisSheet.getRangeByName("rangeMonthfilter");
    var nameValue = name.getValue();

    // If you have operation like:
    var onlyYouHavePermission = SpreadsheetApp.openById('xxxxxxxxx');
    /* This is not allowed if you just use Simpler trigger onEdit(e) to 
       drive monthFilter(). */
}

For this, now is the time to use installable trigger:

function makeCopy(){
    var mainSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var newSpreadsheet = mainSpreadsheet.copy('newSheet');



    ScriptApp.newTrigger('monthFilter')
      .forSpreadsheet(newSpreadsheet)
      .onEdit()
      .create();
    /* The host of this trigger is you, it's like you 
       deploy a trigger to the newSpreadsheet, and make other editors 
       available to do something on "onlyYouHavePermission" through it.*/
}

Update

If all you want to do is just in HideColumns() you provide, you just need to add a new function which is a simpler trigger in script editor:

function onEdit(e) {
   var ss = SpreadsheetApp.getActive();
   var name = ss.getRangeByName("monthfilter");
   var namevalue = name.getValue();

   if (namevalue == "February") {
      /* If you copy the spreadsheet, of course including the sheets 
         inside it, and you share the permission of the new spreadsheet to 
         other editors, because they own the permission, authorization 
         is not required here.
      */
      var sheet = ss.getSheetByName("MIS");
      sheet.hideColumns(30);
   }
}

Set trigger in script in Google Sheets programmatically

1 Answers