使用Google Apps Script实现Sheet1季度与累计求和同步至Sheet2

以下是整合并优化后的完整代码，可一次性完成你所需的所有操作：

function calculateAndTransferData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");

  // 获取Sheet1的最后一列和最后一行
  const lastColumn = sheet1.getLastColumn();
  const lastRow = sheet1.getLastRow();

  // 基础数据校验，避免因数据缺失报错
  if (lastRow < 2) {
    SpreadsheetApp.getUi().alert("Sheet1至少需要包含表头+1行数据");
    return;
  }
  if (lastColumn < 3) {
    SpreadsheetApp.getUi().alert("Sheet1至少需要3列数据才能计算季度总和");
    return;
  }

  // --------------------------
  // 计算季度数据：每行最后3列（最后一列+前两列）求和
  // --------------------------
  const quarterlyDataRange = sheet1.getRange(2, lastColumn - 2, lastRow - 1, 3);
  const quarterlyValues = quarterlyDataRange.getValues();
  const quarterlySumResults = quarterlyValues.map(row => {
    // 将非数字值转为0，避免空单元格导致NaN
    const processedRow = row.map(val => typeof val === "number" ? val : 0);
    return [processedRow[0] + processedRow[1] + processedRow[2]];
  });
  // 写入Sheet2的D列（第4列）
  sheet2.getRange(2, 4, quarterlySumResults.length, 1).setValues(quarterlySumResults);

  // --------------------------
  // 计算累计数据：每行从第2列到最后一列求和
  // --------------------------
  const ytdDataRange = sheet1.getRange(2, 2, lastRow - 1, lastColumn - 1);
  const ytdValues = ytdDataRange.getValues();
  const ytdSumResults = ytdValues.map(row => {
    const processedRow = row.map(val => typeof val === "number" ? val : 0);
    return [processedRow.reduce((total, current) => total + current, 0)];
  });
  // 写入Sheet2的E列（第5列）
  sheet2.getRange(2, 5, ytdSumResults.length, 1).setValues(ytdSumResults);
}

代码细节说明 #

对应需求的实现逻辑 #

1. 识别Sheet1最后有值的列：通过sheet1.getLastColumn()直接获取，同时在开头做数据合法性校验，确保表格有足够的列数和行数用于计算。
2. 季度数据求和：定位每行的最后3列（lastColumn - 2为起始列，跨度3列），遍历每行求和，处理空值避免计算错误。
3. 写入Sheet2 D列：定位Sheet2第2行开始的D列区域，用setValues批量写入计算结果，比逐行写入效率更高。
4. 累计数据求和：定位每行从B列（第2列）到最后一列的区域，用reduce方法快速求和，同样处理空值问题。
5. 写入Sheet2 E列：定位Sheet2第2行开始的E列区域，批量写入累计求和结果。

优化亮点 #

• 合并重复的表格实例获取逻辑，减少API调用次数，提升执行速度
• 增加空值处理逻辑，避免因单元格为空导致的NaN错误结果
• 使用map和reduce替代传统for循环，代码更简洁易维护
• 集中做数据校验，提前拦截非法数据场景

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