从URL列表更新IMPORTRANGE函数

Question

我有一个电子表格，列出了其他电子表格的几个URL，所有这些都包含客户端数据。随着新客户工作表的创建，此列表不断被添加到。我需要能够将所有客户工作表数据自动聚合到一个工作表中。

我尝试过为每个电子表格URL包含importrange文本(而不是将其作为公式(，然后执行={'URL List'!A2:A},，但所做的只是引入公式的文本。

我确信我需要使用谷歌应用程序脚本来实现这一点，可能需要一个循环，但我不知道如何使其工作。

Answer 1

这是有效的：

1. 工具->脚本编辑器
2. 清除您看到的myFunction小代码，然后从下面粘贴代码
3. 更改一些id/表单名称/范围
4. 保存并关闭
5. 工具->宏的->导入->选择："setImportRange">
6. 工具->宏的->管理->设置快捷方式

现在，无论何时通过菜单运行->宏或通过快捷方式，公式将与该驱动器文件夹中的所有图纸一起更新。

编辑：如果你的sheetname有空格，那么你必须用单引号括起来："数据A2:B"--->quot；'数据表'！A2:B"；

代码：

function setImportRange(){
// Change id of drive folder
const spreadsheets = DriveApp.getFolderById('1AxxxxxxxxxwxzZv825s-4m8LcnM3u3hm').getFiles();
const importranges = [];
while (spreadsheets.hasNext()){
const ss = spreadsheets.next();
//Change sheetname and range
importranges.push(`IMPORTRANGE("${ss.getId()}", "Data!A2:B")`)
}
//Change master sheetname and the cell where you want the formula.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Master').getRange('A1').setFormula(`=QUERY({${importranges.join(';')}},"SELECT * WHERE Col1 IS NOT NULL",0)`)
}

编辑：来自列表：

function setImportRangeFromList(){
const sss = SpreadsheetApp.getActiveSpreadsheet();
const inputSheet = sss.getSheetByName('List');
// 2 = start row | 1 = Start column
const spreadsheets = [].concat(...inputSheet.getRange(2,1,inputSheet.getLastRow()).getValues());
const importranges = [];
spreadsheets.forEach(ss => {
//Change sheetname and range
importranges.push(`IMPORTRANGE("${ss}", "Data!A2:B")`)
});
sss.getSheetByName('Master').getRange('A1').setFormula(`=QUERY({${importranges.join(';')}},"SELECT * WHERE Col1 IS NOT N",0)`)
}