时间戳

首页 > 解决方案 > 从 URL 列表更新 IMPORTRANGE 函数

google-apps-script - 从 URL 列表更新 IMPORTRANGE 函数

问题描述

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

我已经尝试为每个电子表格 URL 包含 importrange 文本（不使其成为公式），然后做，={'URL List'!A2:A},但所做的只是引入公式的文字文本。

我确定我需要使用 Google App Script 来实现这一点，可能有一个循环，但我不知道如何让它工作。

标签： google-apps-scriptgoogle-sheets

解决方案

这是有效的：

  1. 工具 -> 脚本编辑器
  2. 清除您看到的小 myFunction 代码并粘贴下面的代码
  3. 更改一些 id's/sheetnames/ranges
  4. 保存并关闭
  5. 工具 -> 宏 -> 导入 -> 选择：'setImportRange'
  6. 工具 -> 宏的 -> 管理 -> 设置快捷方式

现在，每当您通过菜单-> 宏或通过快捷方式运行时，公式将使用该驱动器文件夹中的所有工作表进行更新。

编辑：如果您的工作表名称有空格，那么您必须用单引号括起来：“Data！A2:B”--->“'Data sheet'！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)`)

}

推荐阅读