提高多张纸上单元复制操作的速度

您需要最小化对应用程序脚本api的调用,特别是在循环内。在嵌套循环中更是如此。

这包括在 Using batch operations section of the Best Practices documentation .

下面是一个带注释的替换,它最小化了apps脚本api调用:

注意:我试图重新创建您的电子表格,因为 you didn't provide a minimal and complete example . 如果您想尝试使用这个,请用您的数据副本进行测试。

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行数据。