谷歌应用程序脚本中超过运行时间的复杂转置

Question

我在一列中接收数据，必须将其转换为单独的记录。一些记录的长度为12个字符，其他记录为10个字符，其余记录为9个字符。此外，10和9个字符长的记录中的后2个值必须分别向右移动1和2个字段。给定记录中的第一个值始终是日期。我创建了以下代码，它运行良好，只是在大约6分钟和77条记录后超时。我需要能够处理15倍甚至更多的问题。

我将日期对象的计算嵌入到每个if语句的else部分，并嵌套后续的if语句，以减少不必要的计算。这使我的记录从48张增加到77张。

非常感谢您的真知灼见🙂

---

function transposeNew(){
let ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
let lr = ss.getRange("A13").getDataRegion().getLastRow();
let sr = 13
// get the data column
let data = ss.getRange(sr,1,lr-sr,1).getValues();
// set up the rows loop
let pasteRow = 2;
let arrayField = 0;
while (arrayField < data.length){
//use the new Date() constructor to create a date object with the date value passed
let isDate12 = new Date(data[arrayField+12]).getFullYear(); //processed; size input;record should include 12 rows & 13th should be a date to begin the next row
if (isDate12 === 2020) {
let record = data.slice(arrayField, arrayField+12);
let recordTr = transposeSub(record);
ss.getRange(pasteRow, 5, 1, 12).setValues(recordTr);
arrayField = arrayField + 12;
}
else {
let isDate10 = new Date(data[arrayField+10]).getFullYear(); //unprocessed;size input
if (isDate10 === 2020) {
let record = data.slice(arrayField, arrayField+10);
let record1 = record.slice(0,8);
let record1Tr = transposeSub(record1);
let record2 = record.slice(8,10);
let record2Tr = transposeSub(record2);
ss.getRange(pasteRow, 5, 1, 8).setValues(record1Tr);
ss.getRange(pasteRow, 14, 1, 2).setValues(record2Tr);
arrayField = arrayField + 10;
}
else {
let isDate9 = new Date(data[arrayField+9]).getFullYear(); //unprocessed;no size 
input
if (isDate9 === 2020) {
let record = data.slice(arrayField, arrayField+10);
let record1 = record.slice(0,7);
let record1Tr = transposeSub(record1);
let record2 = record.slice(7,9);
let record2Tr = transposeSub(record2);
ss.getRange(pasteRow, 5, 1, 7).setValues(record1Tr);
ss.getRange(pasteRow, 14, 1, 2).setValues(record2Tr);
arrayField = arrayField + 9;
}
}
}
pasteRow ++;
}
}
function transposeSub(a)
{
return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Answer 1

我看到您创建了一个循环，在这个while语句中，您多次调用SpreadsheetApp函数。这会创建与电子表格的连接，读取/更改其数据，并多次关闭连接，这就是为什么代码运行时间过长的原因。请查看GAS最佳实践批处理操作部分。

您应该考虑在while中消除任何get/setValue()，而是调用getValues()以在while之前保存javascript数组中的所有值，然后在while之后使用setValues()一次写入所有输出。在这个答案中探讨了所描述的概念。

Answer 2

因此，问题是循环标准中的一个缺陷；新手失误。存在一个数据异常，导致一条记录不符合任何if语句中的标准，因此循环将永久继续。我通过在工作表上的每条记录旁边插入pasteRow和arrayField的值来发现这一点，这样我就可以看到它在哪里被破坏了。有趣的是，记录停止了，但在应用程序退出之前，pasteRow和arrayField的值一直持续到20000。

我注意到@Bruno Polo和@Cooper提供的反馈是正确的。在发布这篇文章后不久，我对它进行了修改，将记录推送到一个新的数组中，并在完成后粘贴数组。由于上述同样的原因，该计划失败了。我想，既然我理解了这个问题，我会回到那个版本。

谢谢你和我一起看这个。这是一个非凡的专家群体，我从他们那里学到了很多！😁