-1

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?

1
  • 1
    This maybe an XY Problem You might consider posting your original formula that generates the errors in order to incorporate error checking directly in the formula rather than re-running it. For example a recursive LAMBDA function could re-run itself based on ISERROR
    – Blindspots
    Commented Jul 25, 2023 at 16:52

2 Answers 2

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

0

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.