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);
}
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:
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.
}