提高许多工作表上的单元格复制操作的速度

Question

我在Google Sheets文件中写了一个非常慢的for循环。有 18 张纸，我必须将每个大于 0 的单元格复制到另一张纸上。

我的代码适用于我的任务，但它非常慢。也许有人对如何提高其速度有想法？

一些解释：

• 我if(i == 2)使用了if语句，因为一张纸有 2700 行
• if(total.getRange(16+i, 5).getValue() > 1)检查工作表是否有的行 大于零的单元格。它可以防止不测试空纸。

代码的慢部分：

for (var i = 1; i < 19; i++) {
if (i == 2) {
m = 2700;
} else if (i !=  2) {
m = 500
}
for (j = 9; j<m; j++) {
if (total.getRange(16+i, 5).getValue() > 1) {
var sheet =  SpreadsheetApp.setActiveSheet(formularz.getSheets()[i]);
if (sheet.getRange(j, 8).getValue() > 0) {
var KOD = sheet.getRange(j, 3).getValue();
var il = sheet.getRange(j, 8).getValue();
wysylka.appendRow([KOD,il]);
}
}
}
}

Answer 1

需要尽量减少对应用脚本 API 的调用，尤其是在循环中。在嵌套循环中更是如此。

最佳实践文档的使用批处理操作部分对此进行了介绍。

下面是一个带注释的替换，可最大程度地减少应用脚本 API 调用：

注意：我尝试重新创建您的电子表格，因为您没有提供最小且完整的示例。如果您想尝试使用它，请使用您的数据副本进行测试。

function replacement() {
// For testing purposes, I created a spreadsheet that has data where 
// your original code expects it.  I'm not sure if this is actually all 
// in one spreadsheet or maybe spread across multiple, since you didn't
// specify.
var formularz = SpreadsheetApp.getActiveSpreadsheet();
var total = formularz.getSheetByName('total');
var wysylka = formularz.getSheetByName('wysylka');
// CONFIGURATION
// These are constants to capture the script configuration that can be
// easily tweaked without diving into the code.
// 0-based index of the first data sheet.
var START_SHEET = 1;
// 0-based index of the last data sheet.
// NOTE: I only populated 3 data sheets in my test environment.
var END_SHEET = 3;  
// 0-based index of the row with sheet data within the total sheet.
var TOTAL_STARTING_ROW = 16;
// 0-based index of the column with sheet data within the total sheet.
var TOTAL_COLUMN = 4;
// 0-based index of the starting row within data sheets.
var DATA_STARTING_ROW = 7;
// 0-based index of the 'KOD' column within data sheets.
var DATA_KOD_COLUMN = 2;
// 0-based index of the 'il' column within data sheets.
var DATA_IL_COLUMN = 7;
// END OF CONFIGURATION
// A prerequisite calculation based on the configuration.
var numSheets = END_SHEET - START_SHEET + 1;
// Use a single getValues() call to fetch all necessary total data.
totalData = total.getRange(TOTAL_STARTING_ROW + 1, TOTAL_COLUMN + 1, numSheets).getValues();
// Use an array to capture all of the rows we will append to the 'wysylka' sheet.
var wysylkaRows = [];
for (var i = 0; i < numSheets; i++) {  
// Use a single getValues() call to fetch all necessary data from the data sheet.
// Using getDataRange() to get all sheet data, instead of manually keeping track
// of number of rows per sheet.
var sheetData = formularz.getSheets()[START_SHEET + i].getDataRange().getValues();
for (var j = DATA_STARTING_ROW; j < sheetData.length; j++) {
if (totalData[i][0] > 1) {
if (sheetData[j][DATA_IL_COLUMN] > 0) {
// Push an array that represents a row we'll add to the 'wysylka' sheet.
wysylkaRows.push([
sheetData[j][DATA_KOD_COLUMN],
sheetData[j][DATA_IL_COLUMN]
]);                  
}
}
}
}
// Get a range for all of the accumulated rows.
appendRange = wysylka.getRange(wysylka.getLastRow() + 1, 1,
wysylkaRows.length, wysylkaRows[0].length);
// Set all rows with a single setValues() call.
appendRange.setValues(wysylkaRows);
}

如前所述，在我的测试中，我只填充了 3 个数据表(而不是 17 个(。我能够在 1.034 秒内处理 ~1000 行数据。