我能让这个脚本快多少?

Question

我注意到，当我注释掉调用setValues()的行时，脚本明显更快。这并不是什么大问题，但我对加快这个过程的不同方法(如果有的话)很感兴趣。如果有人有什么见解，我将不胜感激!它还可以帮助我了解在脚本的其他部分可以在哪里进行类似的优化。

下面是问题代码:

// import new value(s)
if ((numRaw - numDk) > 0) {
var iDate;
var lastRow = getLastDataRow(earSheet, columnToLetter(earSheet.getRange("EAR_DATES_RNG").getColumn()));
var distName = impSheet.getRange("A1").getValue().toString();
var defaultDept = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All Lists").getRange("A2").getValue().toString();
impData.forEach(function (entry) {
iDate = new Date(entry[0]);
var newEar = earSheet.getRange("A" + lastRow + ":G" + lastRow).getValues(); // init copy
if (currData.get(iDate.getTime()) == null) {
newEar[0][0] = entry[0];
newEar[0][1] = distName;
newEar[0][6] = entry[1];
newEar[0][3] = "Royalties";
newEar[0][4] = defaultDept;
newEar[0][5] = "NOT SPECIFIED";

earSheet.getRange("A" + lastRow + ":G" + lastRow).setValues(newEar);
Logger.log("ADDED: " + entry[1] + " to row: " + lastRow);
addedNewData++;
lastRow++;
}
else {
Logger.log("EXISTING DATA FOUND: " + currData.get(iDate.getTime()));
}
});
}

Answer 1

根据Matt和Cooper的建议，我修改了代码，速度明显更快:

if ((numRaw - numDk) > 0) {
var iDate;
var prevLastRow = getLastDataRow(earSheet, columnToLetter(earSheet.getRange("EAR_DATES_RNG").getColumn()));
var currLastRow = getLastDataRow(earSheet, columnToLetter(earSheet.getRange("EAR_DATES_RNG").getColumn()));
var rangeSize = numRaw;
var distName = impSheet.getRange("A1").getValue().toString();
var defaultDept = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("All Lists").getRange("A2").getValue().toString();
var newEar = earSheet.getRange("A" + prevLastRow + ":G" + (prevLastRow + rangeSize - 1)).getValues(); // init copy
impData.forEach(function (entry) {
iDate = new Date(entry[0]);

if (currData.get(iDate.getTime()) == null) {
newEar[currLastRow-2][0] = entry[0];
newEar[currLastRow-2][1] = distName;
newEar[currLastRow-2][6] = entry[1];
newEar[currLastRow-2][3] = "Royalties";
newEar[currLastRow-2][4] = defaultDept;
newEar[currLastRow-2][5] = "NOT SPECIFIED";
Logger.log("ADDED: " + entry[1] + " to row: " + currLastRow);
addedNewData++;
currLastRow++;
}
else {
Logger.log("EXISTING DATA FOUND: " + currData.get(iDate.getTime()));
}
});
earSheet.getRange("A" + prevLastRow + ":G" + (prevLastRow + rangeSize - 1)).setValues(newEar);
}```