onEdit installable trigger not firing when defining variable through array object

Question

Hej! I have a script that is supposed to be triggered onEdit. onEdit() as a simple trigger is not an option for me, since the script needs authorizations. When setting an installable trigger with the editor the trigger is only fired when a specific variable is manually defined, but not when it is defined with an array. Since I have no clue where the problem could be (I have tried to comment out the code part by part, but it always ends up not working when comming to the last part.

        function myFunction() {
     //Source Sheet for different sheets
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var activeSheet = ss.getActiveSheet();
      var activeRange = ss.getActiveRange();
      var activeRow = activeRange.getRowIndex();
      var CategoryValues = ss.getSheetValues(activeRow, 4, 1, 14);
      var ID = activeSheet.getRange(activeRow,2).getValue();
      var red = '#e06666'

  // Defining the sourceWeekNumber in the sourceSheet with a while loop
      var sourceWeeks = activeSheet.getRange(1,1,activeRow).getValues();
      sourceWeeks.reverse();
      var sourceWeekColor = activeSheet.getRange(1,1,activeRow).getBackgrounds();
      sourceWeekColor.reverse();
      var sourceWeekNumber = [];
      var sourceWeekIndex=1;
        while (sourceWeekColor[sourceWeekIndex-1]!=red) {
          if (sourceWeekColor[sourceWeekIndex]==red) {
          sourceWeekNumber.push(sourceWeeks[sourceWeekIndex])
          }
          sourceWeekIndex++;
      }

      //Target sheet is always mastersheet
      var ts = SpreadsheetApp.openById('ID').getSheetByName('mastersheet');

      //Finding the right spot in mastesheet
      //Validate the weeknumber
      var validWeek = ts.getRange(2, 1, ts.getLastRow()).getValues();
      var rowIndex = [];

  //-----> var sourceWeekNumber = 'v50'; <----- 
  // When defining this variable manually, onEdit works fine.
  // When defining sourceWeek with the while loop above, the onEdit does not trigger.

      // Find the right week and it's row in the mastersheet
      var validWeekIndex = 1; 
      while (validWeek[validWeekIndex] != sourceWeekNumber) {
        if (validWeek[validWeekIndex] == sourceWeekNumber) {
        var validID = ts.getRange(validWeekIndex+3,2, ts.getLastRow()).getValues();
        rowIndex.push(validWeekIndex);
        }
        validWeekIndex++;
      }
    }

I have tried different things, always using Logger.log() or Browser.msgBox() trying to identify the problem. Any ideas or workarounds? I have been stuck with this for 3 days now and can't find any solution. I have prepared a sample sheet as well, if it is needed then I can edit it in. Thanks in advance!

Edit: Here is the link to the sample sheet: https://docs.google.com/spreadsheets/d/1xCZur6gpfsQFPtwKcTl0XPPfG8zlTqGVlm1GZZ8X1xM/edit?usp=sharing

One problem with the code is that the arrays that you're populating with the range.getValues() calls are 2D, but you're treating them as 1D. The code sorta works because you have Nx1 arrays -- it would not work for Nx2, for example. Also, what does the execution transcript say about the script being triggered? Perhaps you should share the sample sheet. — Dean Ransevycz
I have added the sample sheet link now. The code seems to work fine, and I'm aware of some flaws that might lead to problems in a later stage, but the neither the execution transcript nor the logs say anything about the script being triggered. The script doesn't run at all. I have email notifications enabled, but nothing there either. — user8879694

Unknown Unknown · Accepted Answer · 2017-11-17T19:10:56

I figured it out. It was of course not the trigger, but different factors that made me think it was:

Trigger notifications: I was sure I set notification to immidiatly, but it was set to 4pm every day. That's the reason I didn't get the failure notifications.
The while loop: Because the while loop can't identify when the value from the 1D array turns up in the 2D array, it keeps running and times-out. Therefore the script runs for 5 minutes without possibility to cancel it.
I learned that Google got a quota of 30 min / day of script running time for triggers, so that's why I could only run 6 failed scripts before the quota was full and the script woulnd't run at all until the next day without me realizing it was an issue.

Edit: For future references, I simply used sourceWeekNumber.toString() to be able to get it to match with the other array, comparing a string instead of and object inside of an array.

onEdit installable trigger not firing when defining variable through array object

1 Answers