CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tapan Samaddar
Tapan Samaddar

Reputation: 85

How to break / exit out of a For loop in Googlesheets Script

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

Upvotes: 7

Views: 64934

Answers (1)

Tanaike
Tanaike

Reputation: 201428

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

Upvotes: 9

Related Questions