CODEWITHSUNDEEP
arraysgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulas
Noemie Rousseau
Noemie Rousseau

Reputation: 13

Google Sheet Query - Building Reference Array Dynamically

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

Upvotes: 1

Views: 82

Answers (1)

NightEye
NightEye

Reputation: 11184

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:

enter image description here

Sample sheets were added to check the new sheet cases.

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

Upvotes: 1

Related Questions