dynamic data range from multiple tabs in a google sheet using QUERY function

Question

I'm trying to create a custom function so that I can dynamically return all the tabs within a spreadsheet to use within the QUERY function.

So that in the future if I add a new tab of data, I won't need to manually update the function.

function myFunction() {
  var out = new Array();
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i=0 ; i<sheets.length ; i++) {
    var name = sheets[i].getName();

    if (i !== sheets.length - 1) {

      out.push("'"+sheets[i].getName()+ "'!$A:$F; " ); 
    } else {
      out.push("'"+sheets[i].getName()+ "'!$A:$F " );
    }
  }

  return out;
}

Example of how I will use my custom function

=query(myFunction(),"select * where Col4 = 'Partner' order by Col1 desc",0)

Cooper · Accepted Answer · 2019-10-08T20:24:26.317

1

function getMyDataRanges() {
  var out=[];
  var sheets=SpreadsheetApp.getActive().getSheets();
  for(var i=0;i<sheets.length;i++) {
    var sh=sheets[i];
    var name=sh.getName();
    out.push("'" + name + "'!$A:$F"); 
  }
  return '{' + out.join(';') + '}';
}

I loaded the cell with something like this;

function loadFormula(sheetName,A1Range) {
  var ss=SpreadsheetApp().getActive();
  var sh=ss.getSheetByName(sheetName);
  var formula=Utilities.formatString('=QUERY(%s,"select * where Col4 = \'Partner\' order by Col1 desc")',getMyDataRanges());
  sh.getRange(A1Range).setFormula(formula);
}

edited Oct 08 '19 at 20:24

answered Oct 08 '19 at 17:13

Cooper

59,616
6
23
54

Error Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col4 – PatrickC Oct 08 '19 at 17:42
Sounds like there is a problem with the Query. If you want additional assistance please provide sample data sheets. – Cooper Oct 08 '19 at 17:47
https://docs.google.com/spreadsheets/d/1m8kefo1cwoIYoVYu2vQ_W37giY7JlxBsmiSfZrUacik/edit#gid=1278585125 – PatrickC Oct 08 '19 at 17:59
Thanks Cooper, you can compare the first 2 tabs, the formula is in the A2 cell of each tab. – PatrickC Oct 08 '19 at 18:00
Your link is not public – Cooper Oct 08 '19 at 18:26
1

Check my answer. I loaded the formula into the spreadsheet with a script. I don't think that you'll get away with putting it into a cell formula like you've done. – Cooper Oct 08 '19 at 20:25
adding the loadFormula function, do I just use it like this? `=loadFormula('with custom function in the query',A2)` – PatrickC Oct 08 '19 at 21:56
1

No I don't think it can't be used in a cell function. – Cooper Oct 08 '19 at 22:01

dynamic data range from multiple tabs in a google sheet using QUERY function

1 Answers1