javascriptloopsgoogle-sheetsgoogle-apps-scriptdo-while

Infinite execution of the code in Google apps script


I have this code which runs infinitely. I checked step by step and the error is in the last part of it where there is a loop.

I have in cell(f,3) the value 400 and respectively in cell(f,4) cell(f-1,4) cell(f-2,4) cell(f-3,4) : 200, 50, 20, 100

The cell(f,12) should show the value 270.

function myFunction() {
  
      var ss = SpreadsheetApp.getActiveSpreadsheet();      
      
      var sheet = ss.getSheetByName("Feuille 1");
      var active = sheet.getActiveCell();

      var f = active.getRowIndex();           
      var r = sheet.getRange(f,3).getValues();        
      var i = f          
      var cellule = sheet.getRange(i,4).getValues();      
      var C = 0
      
      do {            
        C = C + cellule;
        var destverif = sheet.getRange(f,12);      
        destverif.setValue(C);
        i = i-1;            
      }while (C + cellule <= r);      
     
 }

Solution

  • You are using getValues() instead of getValue() which returns an array. Your while condition is essentially checking while(NaN + [["200"]] <= [["400"]]) which will not really work out, it seems as though it was returning true and you had a loop that was essentially while(true).

    To correct this, use getValue() instead. This immediately corrects your infinite loop.

    I chatted with you over gchat and determined your logic was also faulty for the output you expected. You are trying to take a set of data like this:

    enter image description here

    Get the value of the highlighted column starting at 200 and going up till your total is greater than or equal to 400. Once it hits this value, write into column 12 of that row 400 / AVERAGE(200,100,101)

    I corrected your code, this code does the trick:

    function getValues(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Feuille 1");
      var active = sheet.getActiveCell();  
    
      var rowIndex = active.getRowIndex(); //Get the rowindex of the current active cell
      var maxValue = sheet.getRange(rowIndex,3).getValue(); //Your max value is the active cells value
    
      var currentValue = 0;
      var activeRowIndex = rowIndex
      var loopIndex = 0;
      do{
    
        var cellData = sheet.getRange(activeRowIndex, 4).getValue(); //Get the value of the cell next to the selectedCell
        if(cellData !== ''){ //If the cell is not blank continue
          currentValue += cellData; //Add up the total as we go
          activeRowIndex --; //Subtract the row index so we can keep movign up
          loopIndex++; //Iterate the loopIndex so we know how many numbers we have added up to calculate the average
        } else {      
          break; //If the cell is blank, break out of the loop
        }
    
      } while (currentValue <= maxValue);
    
      sheet.getRange(rowIndex, 12).setValue(maxValue/(currentValue/loopIndex)); //set the ranges value and do the math in line for the average.
    }