通过Apps Script写入Google Sheets的性能问题及优化咨询

你的核心问题是循环调用appendRow()——每写一行就发起一次Sheets API请求，12000次请求会导致严重的性能瓶颈和超时。以下是针对性的优化方法：

核心优化：批量写入数据 #

把所有数据（表头+查询结果）先存入一个二维数组，最后一次性调用setValues()写入，这能把API请求次数从12000+次降到1次，性能提升非常明显。

修改后的完整代码：

var server = 'x.x.x.x';
var port = 3306;
var dbName = 'xxx';
var username = 'xxx';
var password = 'xxx';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;
 
function readData() {
  var conn = Jdbc.getConnection(url, username, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('insertQueryHere');
  var metaData = results.getMetaData();
  var numCols = metaData.getColumnCount();
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('sheet123');
  sheet.clearContents();
  
  // 初始化存储所有数据的二维数组，先加入表头
  var allData = [];
  var headerArr = [];
  for (var col = 0; col < numCols; col++) {
    headerArr.push(metaData.getColumnName(col + 1));
  }
  allData.push(headerArr);
  
  // 把查询结果批量存入数组
  while (results.next()) {
    var rowArr = [];
    for (var col = 0; col < numCols; col++) {
      rowArr.push(results.getString(col + 1));
    }
    allData.push(rowArr);
  }
  
  // 一次性写入所有数据
  if (allData.length > 0) {
    var range = sheet.getRange(1, 1, allData.length, numCols);
    range.setValues(allData);
  }
  
  // 清理资源
  results.close();
  stmt.close();
  conn.close(); // 别忘了关闭数据库连接
  sheet.autoResizeColumns(1, numCols);
}

额外优化建议 #

• 临时关闭自动计算：如果表格包含复杂公式，写入前关闭自动重算可以避免不必要的性能消耗，写完再恢复：

  // 写入前关闭自动重算
  spreadsheet.setRecalculation(SpreadsheetApp.Recalculation.MANUAL);
  // 写入完成后恢复自动重算
  spreadsheet.setRecalculation(SpreadsheetApp.Recalculation.AUTOMATIC);
  

• 分块写入（超大数据场景）：如果数据量超过5万行，可分块写入（比如每5000行写一次），避免内存溢出：

  // 替换一次性写入的代码
  var batchSize = 5000;
  for (var i = 0; i < allData.length; i += batchSize) {
    var batch = allData.slice(i, i + batchSize);
    sheet.getRange(i+1, 1, batch.length, numCols).setValues(batch);
  }
  

• 减少重复API调用：提前获取Sheet、Range等对象，避免在循环中重复调用getSheetByName或getRange。

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