Query(Importrange) with insertion of needed rows to avoid data overwrite

Question

I would like to develop a script that allows to apply the same function in column A while refering to all the rows below (from A1 to A600) that is a Query(Importrange) for which the number of rows to be inserted is not fixed (from 1 to 300) and so has to be inserted before copying the data.

I started by developing a formula in cell A1 that is working very well but that implies to copy the block 600 times in the cell to cover all the lines :

`={Query(IMPORTRANGE('Master Table Projects'!T503,"Impacted Formula!A5:R1000"),"where Col1 is not Null");Query(IMPORTRANGE('Master Table Projects'!T467,"Impacted Formula!A5:R1000"),"where Col1 is not Null");Query(IMPORTRANGE('Master Table Projects'!T15,"Impacted Formula!A5:R1000"),"where Col1 is not Null")}

By consequence, I have to develop a script with a loop to look apply this formula from T1 to T600. I tried this :

function myFunction() {
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var searchRange = sheet.getRange(2,2, lastRow-1, lastColumn-1);
  for (r=1; r<100;r++) {
    var sa=(Query(IMPORTRANGE('Master Table Projects'!Tr,"Impacted Formula!A5:R1000"),"where Col1 is not Null")); 
   ss.getRange(r,1).setValue(sa); 
  };

This is not working since I receive an alert message : "SyntaxError: missing ) after argument list (line 19, file "learnings.gs")"

I'm not an expert at all in programmation but : I suppose that my "var sa" is not set up properly but I have no idea how to correct it. Tr means for me "T1 to T600" but most probably it's not well written.

I'm attaching a picture of the results I would like to get : expected results

By adding the code line proposed by @ziganotschka

sheet.getRange("A" + sheet.getLastRow()).setValue(sa);

I got the results attached in the new picture that is a nice improvement because are only appearing the imported rows that have some results to display but : There is still the issue of overlapping of data imported. And the last line (that corresponds to T999) has to be removed manually to see some of the results.improved macro

If you click on the link of the spreadsheet I can give you access afterwards but I cannot share a public link due to my company restrictions (this option is blocked).

Can you please help me by correcting these lines to make it work ?

Thanks to @ziganotschka here is the working code :

  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  Logger.log(limit);
  for (var r=2; r<=limit; r++) { 
    var sa="=Query(IMPORTRANGE('Master Table Projects'!T"+r+',"Impacted Formula!A5:R1000"),"where Col1 is not Null")'; 
    var firstEmptyRow = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()+1;
      Logger.log(firstEmptyRow);
    sheet.getRange("A" + firstEmptyRow).setValue(sa);
    SpreadsheetApp.flush();
  }
}

Thanks so much Isa

Answer 1

Provide that you have a working formula, in order to assign it with a dynamic row index, you need to concatenate it correctly:

• When you build your variable sa, it can be composed of references to other variables and text
• The text must be designated as a string by wrapping in=t in single or double quotes
• If you text (formula) already contains some quotes, make sure to adapt the outer wrapping quotes to assure an alternation of single (') and double (") quotes or escape quotes - see here
• For formulas, = should be part of the text
• To concatentate the text part and the variable reference, use the + sign
• Your IMPORTRANGE formula might return you empty rows, you have to query for the next free row each time you set a new formula.
• Useful for this is the method getNextDataCell()

Sample:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  for (var r=2; r<=limit; r++) { 
    var sa="=Query(IMPORTRANGE('Master Table Projects'!T"+r+',"Impacted Formula!A5:R1000"),"where Col1 is not Null")'; 
    var firstEmptyRow = sheet.getRange("A2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()+1;
    sheet.getRange("A" + firstEmptyRow).setValue(sa);
    SpreadsheetApp.flush();
  }
}

Notes:

• It is not good practice to use setValue() in each single line, for the future consider storing the requests in an array and assgin after exiting the for loop all the values at one to the spreadsheet with setValues()
• Double-check if your formula is correct (IMPORTRANGE expects a URL to a spreadhsheet)

Update

• To make sure that the formulas don't overwrite each other, you can implement: sheet.getRange("A" + sheet.getLastRow()+1).setValue(sa);
• However, in your case the importrange creates empty rows that are not being script inspite of the query "where Col1 is not Null".
• Thus, it's best to concatenate all the queries with ; and set them into the same cell
• If you want to retrieve the last row og column T in sheet 'Master Table Projects' dynamically, you can implement

var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

and then loop until limit

Sample:

function myFunction2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var sa="={";
  var limit = ss.getSheetByName('Master Table Projects').getRange("T2").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  for (var r=2; r<limit; r++) { 
    var sa1="Query(IMPORTRANGE('Master Table Projects'!T"+r+',"Impacted Formula!A5:R1000"),"where Col1 is not Null")';
    sa=sa+sa1+";";
  }
  sa = sa.slice(0,-1) +"}";
  sheet.getRange("A2").setValue(sa);
}