CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user8879694
user8879694

Reputation:

onEdit installable trigger not firing when defining variable through array object

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

Upvotes: 0

Views: 305

Answers (1)

user8879694
user8879694

Reputation:

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

  1. 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.
  2. 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.
  3. 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.

Upvotes: 0

Related Questions