How do I break out of a loop / stop a function when a specific logical condition is true in Googlesheets script. In my case, I have a program which, in a loop continuously sets the value of cell B1 and evaluates the result in cell D11.
What I want is that if the result is a string NNN, then the program must stop immediately.
Following is what I have, but the program doesn't exit / stop / quit when the logical condition is true (the program otherwise works fine). Any help appreciated.
function loopX() { var xx; var yy; ...... for (var i = 0; i < data.length; i++) { sheet.getRange('B1').setValue(data[i][0]); SpreadsheetApp.flush(); Utilities.sleep(4000); if (sheet.getRange('D11').getValue() == 'NNN') exit(); } ...... } Updated
function loopC() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('SheetN'); // name of your sheet var data = ss.getSheetByName('data').getRange('A625:A2910').getValues(); if (sheet.getRange('D11').getValue() != "NNN") { for (var i = 0; i < data.length; i++) { sheet.getRange('B1').setValue(data[i][0]); SpreadsheetApp.flush(); Utilities.sleep(4000); } } } 1 Answer
You can use break to go out from FOR LOOP. I think that this is a simple way.
Sample script :
var ar = [["e1"], ["e2"], ["e3"], ["e4"], ["NNN"], ["e6"]]; for (var i = 0; i < ar.length; i++) { if (ar[i][0] == 'NNN') { break; } } Logger.log(i) // 4 Modified your script :
If this is reflected to your script, it can modify as follows.
function loopX() { var xx; var yy; ...... for (var i = 0; i < data.length; i++) { sheet.getRange('B1').setValue(data[i][0]); SpreadsheetApp.flush(); Utilities.sleep(4000); if (sheet.getRange('D11').getValue() == 'NNN') { break; } } ...... } But, in your script, I think that sheet.getRange('D11').getValue() might be able to be written to out of FOR LOOP like below. About this, since I don't know the detail your script, please confirm it.
if (sheet.getRange('D11').getValue() != 'NNN') { for (var i = 0; i < data.length; i++) { sheet.getRange('B1').setValue(data[i][0]); SpreadsheetApp.flush(); Utilities.sleep(4000); } } 
