While Loop does not stop when it should

Question

I'm new to Google Apps Script. I am trying to write a while loop for a spreadsheet, but the loop isn't stopping when it should.

The spreadsheet contains a formula that returns randomized Sudoku puzzles but the randomized results will sometimes include #N/A errors.

I have another formula in L3 that returns the string "ERROR" if the randomized result contains an error otherwise it returns "WORKS".

My while loop in GAS is intended to trigger the random puzzle generator to re-run when it detects the string "ERROR" in L3 by adding 1 to the value in N2.

My Script

function IterationMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  var errorValue = spreadsheet.getRange('L3').getValue();
    while(errorValue == 'ERROR'){
      var modRange = spreadsheet.getRange('N2');
      var modValue = modRange.getValue();
      modRange.setValue(modValue + 1);
      var errorValue = spreadsheet.getRange('L3').getValue();
    };
};

In theory, the script should continue to add 1 to the value in N2 until an error-free puzzle is generated, which would cause the value in L3 to be "WORKS" instead of "ERROR"

Unfortunately, it doesn't seem to stop. I have run the macro and watched it add 1 until cell L3="WORKS" but the value change is ignored and the script continues adding 1 to the value in N2 indefinitely.

Why doesn't the script stop when the value changes?

Answer 1

I suggest you add SpreadsheetApp.flush() as a line prior to when it re-evaluates the value. It will force the script to wait until the re-calculations are done

Answer 2

Instead of working with read/write operations in a loop, do it in an array, then once the task to be done in the look is finished, "all" the write operations at once.

The above is because the Google Apps Script Spreadsheet service might not apply the write changes to the spreadsheet until the execution finishes or when a statement like SpreadsheetApp.flush(); is called.

Related (Oldest first)

• Looping through the range of cells to test the dates
• Google Apps Script - While loop
• Loop through each row on one sheet coping specific cells of each row to a second sheet until an empty row(last row) is found