修复Google Apps Script中TypeError: Cannot read property 'setValues' of undefined错误

问题描述 #

我制作了多个包含表格的文件，各文件表格数据不同，需要汇总到一个文件中。双文件汇总代码可正常运行，但扩展到3个文件时出现TypeError: Cannot read property 'setValues' of undefined错误，错误发生在destinationrange.getCell[[i+1,j+1,k+1]].setValues(sum);行，需要解决该错误并实现26个文件的汇总。

正常运行的双文件汇总代码 #

function juneFunction() {
  var sheet_origin1=SpreadsheetApp
                      .openById("ID1")
                      .getSheetByName("Calcul/Machine");
  var sheet_origin2=SpreadsheetApp
                      .openById("ID2")
                      .getSheetByName("Calcul/Machine"); 
  var sheet_destination=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //define range as a string in A1 notation
  var range1="BU4:CF71"  
  var values1=sheet_origin1.getRange(range1).getValues();
  var values2=sheet_origin2.getRange(range1).getValues(); 
  var destinationrange=sheet_destination.getRange("C3:N70");
  //Here you sum the values of equivalent cells from different sheets
  for(var i=0; i<values1.length;i++)
  {
    for(var j=0; j<values1[0].length;j++)
    {
      sum=values1[i][j]+values2[i][j];
      destinationrange.getCell(i+1,j+1).setValue(sum);
    }
  } 
}

报错的三文件汇总代码 #

function juneFunction() {

  var sheet_origin1=SpreadsheetApp
                      .openById("ID1")
                      .getSheetByName("Calcul/Machine");
  var sheet_origin2=SpreadsheetApp
                      .openById("ID2")
                      .getSheetByName("Calcul/Machine");
  var sheet_origin3=SpreadsheetApp
                      .openById("ID3")
                      .getSheetByName("Calcul/Machine");
  var sheet_destination=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  
  //define range as a string in A1 notation
  var range1="BU4:CF71"  
  
  var values1=sheet_origin1.getRange(range1).getValues();
  var values2=sheet_origin2.getRange(range1).getValues();
  var values3=sheet_origin3.getRange(range1).getValues();
  
  var destinationrange=sheet_destination.getRange("C3:N70");
  
  //Here you sum the values of equivalent cells from different sheets
  for(var i=0; i<values1.length;i++)
  {
    for(var j=0; j<values1[0].length;j++)
    {
      for(var k=0; k<values1[0].length;k++){
        sum=values1[i][j][k]+values2[i][j][k]+values3[i][j][k];
        destinationrange.getCell[[i+1,j+1,k+1]].setValues(sum);
      }
    }
  }
}

错误原因分析 #

1. getCell调用错误：getCell是方法，必须用圆括号()传参，而非方括号[]；且该方法仅接受行号、列号两个参数，不存在第三个维度的参数。
2. 数组维度错误：getValues()返回的是二维数组（行×列），每个单元格值对应values[i][j]，不存在第三维values[i][j][k]。
3. 冗余循环：新增的k循环完全多余，每个单元格仅需对应行i和列j的位置，将多个文件的对应单元格值相加即可。
4. 方法使用错误：单个单元格赋值应使用setValue()，setValues()用于给多行多列范围批量设置二维数组。

修正后的三文件汇总代码 #

function juneFunction() {
  // 源文件ID列表
  var sourceIds = ["ID1", "ID2", "ID3"];
  var sheetName = "Calcul/Machine";
  var sheet_destination = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rangeStr = "BU4:CF71";
  var destRangeStr = "C3:N70";

  // 初始化结果数组，先读取第一个文件的数值作为基础
  var firstSheet = SpreadsheetApp.openById(sourceIds[0]).getSheetByName(sheetName);
  var resultValues = firstSheet.getRange(rangeStr).getValues();

  // 遍历剩余文件，累加对应单元格数值
  for (var idx = 1; idx < sourceIds.length; idx++) {
    var currentSheet = SpreadsheetApp.openById(sourceIds[idx]).getSheetByName(sheetName);
    var currentValues = currentSheet.getRange(rangeStr).getValues();
    for (var i = 0; i < resultValues.length; i++) {
      for (var j = 0; j < resultValues[0].length; j++) {
        // 处理非数值情况，避免NaN
        resultValues[i][j] = (resultValues[i][j] || 0) + (currentValues[i][j] || 0);
      }
    }
  }

  // 批量写入结果，比逐个单元格赋值效率更高
  sheet_destination.getRange(destRangeStr).setValues(resultValues);
}

扩展到26个文件的实现 #

只需在sourceIds数组中添加所有26个文件的ID即可，无需修改核心逻辑：

function juneFunction() {
  // 替换为你的26个文件ID
  var sourceIds = ["ID1", "ID2", "ID3", /* ... 这里添加剩余23个ID */ "ID26"];
  var sheetName = "Calcul/Machine";
  var sheet_destination = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rangeStr = "BU4:CF71";
  var destRangeStr = "C3:N70";

  if (sourceIds.length === 0) return;

  // 初始化结果数组
  var firstSheet = SpreadsheetApp.openById(sourceIds[0]).getSheetByName(sheetName);
  var resultValues = firstSheet.getRange(rangeStr).getValues();

  // 累加所有文件的数值
  for (var idx = 1; idx < sourceIds.length; idx++) {
    var currentSheet = SpreadsheetApp.openById(sourceIds[idx]).getSheetByName(sheetName);
    var currentValues = currentSheet.getRange(rangeStr).getValues();
    for (var i = 0; i < resultValues.length; i++) {
      for (var j = 0; j < resultValues[0].length; j++) {
        resultValues[i][j] = (resultValues[i][j] || 0) + (currentValues[i][j] || 0);
      }
    }
  }

  // 批量写入汇总结果
  sheet_destination.getRange(destRangeStr).setValues(resultValues);
}

优化说明 #

• 采用数组批量赋值替代逐个单元格写入，大幅提升运行效率（Google Apps Script对读写操作有配额限制，批量操作更友好）。
• 处理了非数值情况（如空单元格），避免出现NaN错误。
• 所有源文件ID集中管理，扩展时只需添加ID，简化代码维护。

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