appScript to concatinate and incriment Query from reference cells while data exisits. What am I missing?

Question

I'm going to go bald scratching my head so I really need as much help as I can get please. Can I please also learn from you too rather than get a solution i don't understand.

I have an appScript which concatinates substrings and an incrimentation variable which together, result in a comprehensive query to parse transaction data for each 'chart of account' referenc-code's key words, and then outputs "coded' matching transactions accordingly.

I've tested the query by manually incrimenting and stacking on a separate WIP_Query sheet:

={QUERY(Transactions, "Select A, B, C, D, P, "&COA!$B21&" where D MATCHES '\s?.*?\s?"& TEXTJOIN("\s?.*\s?|",True,Indirect("AutoCodeKeys!"&COA!$C21&"2:"&COA!$C21))&"\s?.*?\s?'LABEL "&COA!$B21&" 'CODE'",1); QUERY(Transactions, "Select A, B, C, D, P, "&COA!$B22&" where D MATCHES '\s?.*?\s?"& TEXTJOIN("\s?.*\s?|",True,Indirect("AutoCodeKeys!"&COA!$C22&"2:"&COA!$C22))&"\s?.*?\s?'LABEL "&COA!$B22&" 'CODE'",1)}

(I've tested more stacked than posted above)

It works perfectly, but when I try the auto-incrimented appScript one I get:

"Error: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."

The auto query appears to read fine to me???? (I am going cross-eyed)

function incrimentingQuerystring() {

  Logger.clear();

  var app = SpreadsheetApp.getActive();
  var targetSheet = app.getSheetByName("scriptedQuery");
  var targetCell = targetSheet.getRange(2, 1);
  var refSheet = app.getSheetByName("ref");
  var query;

  //buildQueryFromSubStrings:

  var beginString = ("={"); // Only to be added to first iteration.
  var s1 = refSheet.getRange(3, 9).getValue();
  var s2 = refSheet.getRange(4, 9).getValue();
  var s3 = refSheet.getRange(5, 9).getValue();
  var s4 = refSheet.getRange(6, 9).getValue();
  var s5 = refSheet.getRange(7, 9).getValue();
  var endString = (";"); // To be deducted from last iteration  *************HOW????????*****************

  //Get Chart Of Account Reference Codes to Query:

  var data = app.getSheetByName("COA").getDataRange().getValues();

  //Incriment though each accont in Chart of Acconts Sheet.

  for (var i = 6; i < data.length; i++) {

    var queryIteration = (s1 + (i - 1) + s2 + (i - 1) + s3 + (i - 1) + s4 + (i - 1) + s5 + endString);

    //Logger.log(queryIteration);

    var thisIteration = (queryIteration + thisIteration);

    //Logger.log(thisIteration);
  }

  //Logger.log(i);
  
  //******My attempt at removing var endString****

  var finalIteration = (s1 + ("5") + s2 + ("5") + s3 + ("5") + s4 + ("5") + s5 + "}"); 

  //Logger.log(finalIteration) // This seems correct but is preceded by "undefined" when added to query below:

  var query = (beginString + thisIteration + finalIteration); // results "undefined" being added prior to finalIteration.

  //Logger.log(query); //Runs out of space to see error. Error can be seen in targetCell in spreadsheet.

  targetCell.setValue(query);
}

LinkToSpreadSheet

Answer 1

Silly me....I hadn't defined var b before the for loop, so the loop result started with "undefined". I also had "'" in my "COA" sheet 'account description string' which when concatinated was making appscript exit from string value.

The funtion below is my simplification of the loop in my original question. It can be used to concatinate incremented cell references with static query sub strings to build lenghty stacked queries.

I thought it best to leave all the various cell.gets in my original question out, and just ensure I had the basic loop correct using static coded strings. Once I'd defined var b correctly ahead of the loop it all worked fine.

Thanks to anyone who spent time trying to get to the bottom of my long winded question for me.

function loopy() {

  var app = SpreadsheetApp.getActive();

  var ss = app.getSheetByName("results");

  var resultCell = ss.getRange(1, 1);

  var b = 1;

  var beginString = "EQUALS SIGN TO GO HERE{ This is only to appear at the very start..."

  var s1 = "someText, ";

  var s2 = "someMoreText, ";

  var s3 = "evenMoreText, ";

  var midEndString = "Phew End of Text  ;  ";

  var veryEndString = "This is only to appear at the very end...}"

  for (var i = 1; i <= 20; i++) {

    var a = i;

    var b = b + i + s1 + i + s2 + i + s3 + i + midEndString;

    Logger.log(b); // How do remove "undefined" from the first iteration?

  }

  resultCell.setValue(beginString + b + veryEndString);
}

var beginString = "EQUALS SIGN TO GO HERE{ This is only to appear at the very start..."

var s1 = "someText, ";

var s2 = "someMoreText, ";

var s3 = "evenMoreText, ";

var midEndString = "Phew End of Text ; ";

var veryEndString = "This is only to appear at the very end...}"

for(var i=1;i<=20;i++){

   //var iterationCounta = i;

   var a = i;

   var b = b+i+s1+i+s2+i+s3+i+midEndString;

   Logger.log(b); // How do remove "undefined" from the first iteration?

  }

resultCell.setValue(beginString+b+veryEndString); }