google-apps-script - 谷歌脚本替换停止工作的 ImportRange 公式？

Question

我需要帮助。我的工作使用 Google 表单（请假申请表）来收集员工休假/病假信息。提交时，该数据进入谷歌电子表格，然后使用公式计算总休假时间。

负责更新所有员工休假的员工使用不同的谷歌电子表格进行工资单。我正在使用以下公式从缺勤申请表中导入数据，直到它突然停止工作。我的猜测是重要的数据变得太大了。电子表格中当前有 560 行和 18 列。=QUERY(IMPORTRANGE(" https://docs.google.com/spreadsheets/d/1uZL7bMIiuOEpSbC0kk3rB_nkBAGb8Pf-opwl4FGovJ0/edit#gid=971834620 ", "'Form Responses 1'!B:P"), "选择 Col1, Col4, Col5, Col11, Col14 where Col2 <> ''", 1) [注意：我不能共享公式中列出的电子表格]

Col1 是电子邮件，Col4 是第一天休假，Col5 是最后一天休假，Col11 是总休假时间，Col14 是休假的描述（个人、病假等）。

我试图用脚本替换它，但不断遇到错误。我对 Google Apps 脚本一无所知。我花了最后 3 天在这里阅读它们并尝试编写一些东西但没有任何效果。许多脚本在一两分钟后出现超时错误，或者根本没有给出错误消息并且无法正常工作。

其他公式在工资单中运行。这是包含所有元素的模拟工资单的链接： https ://docs.google.com/spreadsheets/d/1y4baWrY0799dQPffXfD7oKCMISy7uai34wTRG5dtlfQ/edit?usp=sharing

我的问题是： 1. importrange 公式可以改进以再次工作吗？如果是这样，我该怎么做才能加快速度？2. 脚本能否解决我的公式问题并可能加快计算过程（有时需要一个小时才能完成整个工作表） 3. 如果脚本可以提供帮助，它会是什么？

在这一点上，我正在把头撞在墙上，所以非常愿意尝试任何事情！

这是我尝试的最后一个脚本，我收到以下错误消息：TypeError：无法调用 null 的方法“getRange”。（第 15 行，文件“代码”）

function onOpen() {
  var submenu = [{name:"Copy", functionName:"Copy"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Menu', submenu);  
}

function Copy() {
 var sss = SpreadsheetApp.openById('1uZL7bMIiuOEpSbC0kk3rB_nkBAGb8Pf-opwl4FGovJ0'); //replace with source ID
 var ss = sss.getSheetByName('Responses'); //replace with source Sheet tab name
 var range = ss.getRange('A1:O600'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1f53bnAllY5LYgwyPY9X8oZJ5fMBbwpiB5CRglaw_iqM'); //replace with destination ID
 var ts = tss.getSheetByName('Data3forscript'); //replace with destination Sheet tab name

 ts.getRange(ts.getLastRow()+1, 1,16,38).setValues(data); //you will need to define the size of the copied data see getRange()

标签： google-apps-scriptgoogle-sheets