使用Apps Script将CSV导入Google Sheets时遇到超时问题

一、使用Advanced Sheets Service替代setValues #

首先需要在脚本编辑器中启用Advanced Sheets Service：

• 打开脚本编辑器，点击「资源」→「高级Google服务」
• 找到「Google Sheets API」，开启开关后点击「确定」

修改后的代码如下：

function importcsvsales () {
  var threads = GmailApp.search("subject:Sales");
  var messages = threads[0].getMessages();
  var message = messages[messages.length - 1];
  var attachment = message.getAttachments()[0];

  attachment.setContentType('text/csv');
  if (attachment.getContentType() === "text/csv") {
    var spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
    var sheetName = "Sheet1";
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");

    // 精准清空原有数据范围，减少不必要的重算触发
    var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var lastRow = sh0.getLastRow();
    var lastCol = sh0.getLastColumn();
    if (lastRow > 0 && lastCol > 0) {
      sh0.getRange(1, 1, lastRow, lastCol).clearContent();
    }

    // 使用Advanced Sheets Service写入数据
    if (csvData.length > 0) {
      var range = `${sheetName}!A1:${String.fromCharCode(64 + csvData[0].length)}${csvData.length}`;
      Sheets.Spreadsheets.Values.update(
        { values: csvData },
        spreadsheetId,
        range,
        { valueInputOption: "RAW" }
      );
    }

    GmailApp.markMessageRead(message);
    GmailApp.moveMessageToTrash(message);
  }
}

优化说明：

• 用RAW模式写入数据，跳过格式转换环节，提升写入速度
• 仅清空表格中实际有数据的范围，而非固定的超大区域，减少触发公式重算的单元格数量

二、其他优化方法 #

1. 临时关闭自动重算 #

在写入数据前后暂停自动计算，避免公式频繁重算拖慢速度：

function importcsvsales () {
  var threads = GmailApp.search("subject:Sales");
  var messages = threads[0].getMessages();
  var message = messages[messages.length - 1];
  var attachment = message.getAttachments()[0];

  attachment.setContentType('text/csv');
  if (attachment.getContentType() === "text/csv") {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var sh0 = sheet.getSheetByName("Sheet1");
    var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");

    // 关闭自动重算
    var settings = Sheets.Spreadsheets.get(sheet.getId(), {fields: "calculationSettings"});
    var originalRecalcMode = settings.calculationSettings.recalculationInterval;
    Sheets.Spreadsheets.batchUpdate({
      requests: [{
        updateSpreadsheetProperties: {
          properties: {calculationSettings: {recalculationInterval: "MANUAL"}},
          fields: "calculationSettings.recalculationInterval"
        }
      }]
    }, sheet.getId());

    // 清空并写入数据
    var lastRow = sh0.getLastRow();
    var lastCol = sh0.getLastColumn();
    if (lastRow > 0 && lastCol > 0) {
      sh0.getRange(1, 1, lastRow, lastCol).clearContent();
    }
    sh0.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);

    // 恢复自动重算
    Sheets.Spreadsheets.batchUpdate({
      requests: [{
        updateSpreadsheetProperties: {
          properties: {calculationSettings: {recalculationInterval: originalRecalcMode}},
          fields: "calculationSettings.recalculationInterval"
        }
      }]
    }, sheet.getId());

    GmailApp.markMessageRead(message);
    GmailApp.moveMessageToTrash(message);
  }
}

2. 优化SUMIF公式 #

如果当前SUMIF公式引用的是整列（例如SUMIF(A:A, ...)），改成引用实际数据范围（例如SUMIF(A1:A35000, ...)），从根源减少公式需要计算的单元格数量，降低重算开销。

内容的提问来源于stack exchange，提问作者Matt