For loop with IF statement will not exit

Question

I am trying to create an Apps Script project that grabs a MySQL query from a list and executes them.

The very last cell in the list contains the word "EXIT".
My intention is to exit the for loop with the If break.

It is processing the update query's and executing them, but will not exit the loop to continue

Any guidance would be greatly appreciated.

Logger.log(ipAddress)
Logger.log(userPassword)
Logger.log(user)
Logger.log(dbUrl)


var connection = Jdbc.getConnection(dbUrl, user, userPassword);
var stmt = connection.createStatement()

Logger.log(connection);

for (var i = 2;; i++) {
    var queryCell = querySheet.getRange(i, 1).getValue();

    Logger.log(queryCell);

    if (queryCell == "EXIT") {
        log.setValue(queryCell);
        break;
    }

    var rs = stmt.executeUpdate(queryCell);
}

rs.close();
stmt.close();
connection.close();
Logger.log("Loop Exited")

var toast = SpreadsheetApp.getActiveSpreadsheet().toast('Complete', 'Status', 10);

var time = new Date();
loadEndCell.setValue(time);

"Exceeded maximum execution time" it is the error google provides for the max time a gs script is allowed. So it times out 5 minutes or so later. The logs reflect the query's being executed ,it is definitely not leaving the loop — Jake, Aug 28 '18 at 18:32
are you sure the string is `EXIT` and not `exit` try to log all of your lines. Imagine you have 100 lines set the `for` loop to `for (var i = 2; i <101; i++)` and log each cells. — JSmith, Aug 28 '18 at 18:38
Yes, it is "EXIT". I am running about 5k querys through it. I can see them entering into the DB from workbench too. Sometimes does not finish all of them for some reason. — Jake, Aug 28 '18 at 18:41
i mean perhaps its the formatting that makes it uppercase also do you have a fomula in your cell? @TheWizEd provides you a good solution too; — JSmith, Aug 28 '18 at 18:43
This is an example. INSERT INTO collectionsmax.log (filenumber,logtime,logdateandtime,logdate,logmessage) SELECT id,'11:10:17', '08/28/2018 11:10:17' , '08/28/2018' , 'ACCOUNT HAS BEEN IMPORTED INTO THE DATABASE' FROM collectionsmax.dbase WHERE custom60 = '3422503' ; — Jake, Aug 28 '18 at 18:44
Probably the issue is that you violate the Apps Script best practices, and query the value of a cell within a loop. Batch-read the values first, then access the JavaScript `Array` element indices as needed. Review https://stackoverflow.com/questions/49020131/how-much-faster-are-arrays-than-accessing-google-sheets-cells-within-google-scri/49020786#49020786 Consider that you may not have even gotten to the end of your defined statements when the script runs out of time (i.e. `i` << the row in which `EXIT` appears) — tehhowch, Aug 28 '18 at 19:13
Your right tehhowch, i guess you can format your sheet to the headers of your tables and insert as an array, versus compiling statements and having them execute line by line. For anyone else with this issue this video does a great job illustrating the correct way to do this. https://www.youtube.com/watch?v=LCt7mRT0pBE — Jake, Aug 28 '18 at 20:41

TheWizEd · Accepted Answer · 2018-08-28T19:40:08.887

2

Try something like this instead. Every getValue()/setValue() is a call to the server and can affect performance. It is better to use getValues()/setValues().

  var queryData = querySheet.getDataRange().getValues();
  for( var i=1; i<queryData.length; i++ ) {
    Logger.log(queryData[i][0]);
    if( queryData[i][0] === "EXIT" ) {
      break;
    }
  }

edited Aug 28 '18 at 19:40

answered Aug 28 '18 at 18:38

TheWizEd

7,517
2
11
19

Where would I place the updateQuery function in that loop TheWizEd? – Jake Aug 28 '18 at 18:54
You can improve this answer with some links and explanation of best practices :) – tehhowch Aug 28 '18 at 19:14
@Jake, what is updateQuery function? instead of queryCell use queryData[i][0] – TheWizEd Aug 28 '18 at 19:31
The SQL statement, also do I define the data range for getDataRange and getValues? Or leave them as is. – Jake Aug 28 '18 at 20:36
@Jake, getValues() and getDataRange() are methods that take no parameters. getDataRange() is the same as getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()). In my opinion, the best source of description of Spreadsheet properties and methods is https://developers.google.com/apps-script/reference/spreadsheet/ – TheWizEd Aug 29 '18 at 00:58
Thanks for the response, so obviously in that scenario I cannot have other information on the spreadsheet then correct? I am assuming the methods can not discern, – Jake Aug 29 '18 at 19:05
@Jake I don't understand your question. If you mean by log.setValue(), getValues() takes the values from the server (spreadsheet) and put them in client memory. At this point there is no connection between spreadsheet cells and client 2D array. If you change values in the 2D array you have to put them back in the spreadsheet using setValues(). Or if you only want to replace one value you can use setValue(). You can getValues() from another range and manipulate them and put them back using setValues() or one cell using setValue(). – TheWizEd Aug 29 '18 at 20:11
Thank you for the clarification. – Jake Aug 30 '18 at 10:00

For loop with IF statement will not exit

1 Answers1