arrays - Google Sheet Query - 动态构建参考数组

Question

我在一个文件中有多个选项卡，并希望将相同的范围合并到一个主选项卡。

我做了一个查询

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

但最后我会有越来越多的标签（大约 30 个），我不想每次都手动更改我的查询。我能怎么做？

我在某处看到我可以使用宏来创建一个函数，你知道代码吗？我只是想要一些简单的东西，我在另一个标签中添加了我所有的标签名称

标签： arraysgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulas

Answer 1

尝试：

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)");
}

这会将公式设置为A1工作表query，随时通过更改A1和更改设置公式的位置query。

您还可以添加要排除的工作表。将其附加到excludedTabs阵列上。

样本输出：

添加了样品表以检查新的表盒。

添加了预期的公式。（不包括query,random和other床单）