Google Sheet Query - Building Reference Array Dynamically

Question

I have multiple tabs in a file and want to merge the same range to a master tab.

I did a Query

=QUERY({source1!AR5:AU;source1!AR5:AU}, "select Col1,Col2,Col3,Col4 where Col1 is not null order by Col1", 0)

But at the end I will have more and more tabs (around 30), and I don't want to change my query manually each time. how can i do?

I saw somewhere that I can use macros to create a function, do you have an idea of the code? I just want to have something easy where I have added in another tab all my tab names

@NoemieRousseau Why do you keep track of the data on different tabs? — MattKing
– MattKing, Commented Jan 26, 2021 at 15:11

NightEye · Accepted Answer · 2021-01-26 16:09:18Z

1

Try:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var allSheets = [];
  // Append more names if you want to exclude more
  // Since 'query' is master sheet, excluded it as well
  // Remove 'query' below if you want to include it in the formula
  var excludedTabs = ['other', 'random', 'query'];
  
  sheets.forEach(function (sheet){
    var sheetName = sheet.getSheetName();
    if (!excludedTabs.includes(sheetName)){
      allSheets.push(sheetName);
    }
  });

  // Set formula in A1 cell in "query" sheet
  // Modify A1 to change the cell
  var cell = ss.getSheetByName('query').getRange("A1"); 
  cell.setFormula("=QUERY({" + allSheets.join('!AR5:AU;') + "!AR5:AU}, \"select Col1,Col2,Col3,Col4 where Col1 is not null order by Col1\", 0)");
}

This will set the formula to A1 of the sheet query, feel free to change where to set the formula by changing A1 and query.

You can also add sheets to be excluded. Append it on the excludedTabs array.

Sample Output:

Sample sheets were added to check the new sheet cases.

Expected formula was added. (excluding query, random and other sheets)

edited Jan 26, 2021 at 16:09

answered Jan 26, 2021 at 15:15

NightEye

11.3k2 gold badges8 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Noemie Rousseau Over a year ago

thanks a lot! it is almost something like this that I need, but I would like to select the tabs I want. (3 of them should not be considered in the macro and all the other ones yes). can you help?

NightEye Over a year ago

Yes for sure @NoemieRousseau . If you can specify these tabs, it would be easy to adjust

Noemie Rousseau Over a year ago

so if I want my query in sheet "query", then i want to exclude other sheets called "other" & "random" and I want to consider all the rest in my query

Noemie Rousseau Over a year ago

awesome ! let me check !

Noemie Rousseau Over a year ago

so great ! it is working ! thanks a lot :)

|

Collectives™ on Stack Overflow

Google Sheet Query - Building Reference Array Dynamically

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related