How do I group multiple tab ranges in array literal dynamically(by using wildcard)?

user_3726101 0
Viewed 252

I have one spreadsheet with multiple tabs, e.g.

"Summary"
"Week 1"
"Week 2"
"Backlog"

The number of Weeks is not fixed. In the future, I may add more tabs as time goes on, for example
"Week 3"
"Week 4" ...

In the "summary" tab, I want to list all the data from Column A (remove title) from all "Week" tabs. Currently, I have to add all the tab names of "Week" manually in the query range parameter, e.g.

=query({'Week 1'!A2:A; 'Week 2'!A2:A}, "select *")

But I want to make it automatically if I add more tabs in the future by doing something like this:

=query({"Week *"}!A2:A, "select *")

How do I do that?

array-formulas google-sheets-formula google-apps-script google-sheets javascript
2 Answers

An option would be to get rid of the QUERY formula altogether and use an Apps Script Custom Function instead.

First, open a bound script by selecting Tools > Script editor, and copy the following functions to the script:

function SUMMARIZE_WEEKTABS() {
  const ss = SpreadsheetApp.getActive();
  const weekSheets = ss.getSheets().filter(sheet => sheet.getName().startsWith("Week "));
  const summarySheet = ss.getSheetByName("Summary");
  let weekData = weekSheets.map(weekSheet => {
    return weekSheet.getRange(2, 1, weekSheet.getLastRow()).getValues().flat();    
  });
  weekData = weekData[0].map((_, colIndex) => weekData.map(row => row[colIndex]));
  return weekData;
}

The function SUMMARIZE_TABS returns the data from column A from all sheets whose name starts with "Week ". Once it is defined in your script, you can use it the same way you would use any sheets built-in function. See, for example, this:

enter image description here

Update:

If you want all data to be written on the same column, use this instead:

function SUMMARIZE_WEEKTABS() {
  const ss = SpreadsheetApp.getActive();
  const weekSheets = ss.getSheets().filter(sheet => sheet.getName().startsWith("Week "));
  const summarySheet = ss.getSheetByName("Summary");
  let weekData = weekSheets.map(weekSheet => {
    return weekSheet.getRange(2, 1, weekSheet.getLastRow() - 1).getValues();    
  }).flat();
  return weekData;
}

Reference:

user_106120110

Explanation:

You can use Google Apps Script to accomplish this task.

The following script:

  • gets all the sheets of your spreadsheet file that contain the name Week,
  • iterates over these sheets and construct a string object which can be used inside the query formula,
  • sets the resulting formula to cell A1 of the Summary sheet. Feel free to modify A1 in the code below.

I also added a function that creates a macro button on top of your sheet, so you can use this functionality from the sheet itself (see instructions below).

Solution:

Please follow the instructions (gif) on how to use the following code:

function getQuery() {
  const ss = SpreadsheetApp.getActive();
  const summary_sheet = ss.getSheetByName('Summary');
  const sheet_names = ss.getSheets().filter(fsh=>fsh.getName().includes("Week")).map(sh=>sh.getName())
  var weeks = [];
  sheet_names.forEach(wk => 
                      weeks.push(`'${wk}'!A2:A`)
                     )
  summary_sheet.getRange('A1').setFormula(`=query({${weeks.join(";")}}, "select *")`)

}

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Macros')
  .addItem('Get Query', 'getQuery')
  .addToUi();
}

Instructions:

After you save the code snippet in the script editor, refresh your sheet/browser.

steps

References:

Google Apps Script libraries:

JavaScript libraries:

user_112252910
Related