使用Google Apps Script的onEdit自动填充甘特图遇问题求助

我正在编写Google Apps Script，实现编辑工作表时自动填充甘特图，但遇到了问题。移除onEdit尝试运行程序后，仍弹出"cannot read properties of undefined"错误。

预期实现功能 #

• 用户编辑"Gantt Chart"工作表时自动触发
• 根据任务的起始周、计算时长，修改对应单元格区域的背景色以标识任务执行周期
• 背景色与任务角色绑定，颜色取自"Roles"工作表中设置的对应角色颜色

当前代码 #

function ganttChart()
{
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ganttSheet = ss.getSheetByName("Gantt Chart");
  var headerRow = ss.ganttSheet.getRange('headerRow').getRow();
  var lastRow = ss.ganttSheet.getLastRow();
  var lastCol = ss.ganttSheet.getLastColumn();
  var firstTask = headerRow + 1
  var taskRoleCol = ss.ganttSheet.getRange('taskRole').getColumn();
  //I'm not sure if I need to do the below RoleCol if I already have a named range -- this will return an integer which is the column #
  var roleCol = ss.getSheetByName("Roles").getRange('Roles').getColumn();
  var taskCol = ss.ganttSheet.getRange('taskNames').getColumn();
  var startWeekRow = ss.ganttSheet.getRange('startWeek').getRow();
  var expDurationCol = ss.ganttSheet.getRange('expDuration').getColumn();

  //set the requirements for the edit trigger -- not sure what these would be
  //if (e.range)
  //{
    for (var i = firstTask; i < lastRow; i++)
    {
      var currentTask = ss.ganttSheet.getRange(i, taskCol).getValue();
      var currentStartWeek = ss.ganttSheet.getRange(i, startWeekCol).getValue();
      var currentTaskExpDuration = ss.ganttSheet.getRange(i,expDurationCol).getValue();
      var currentTaskRole = ss.ganttSheet.getRange(i,taskRoleCol).getValue();

      if (currentTask != null)
      {
        if (currentStartWeek != null)
        {
          //for loop to identify the column that matches the start week #
          for (var j = 0; j < lastCol; j++)
          {
            
            var checkWeek = ss.ganttSheet.getRange(startWeekRow, j).getValue();

            if (checkWeek == currentStartWeek)
            {
              
              //identify the range
              var taskTimeRange = ss.ganttSheet.getRange(i,j - 1,(currentTaskExpDuration*2 +1), 1);
              //for loop get the background color based on role
              for (var k = 0; k < lastRow; k++)
              {
                var checkRole = ss.ganttSheet.getRange(k, roleCol).getValue();
                //if role value matches the currentTaskRole
                if (checkRole == currentTaskRole)
                {
                  
                  var roleColor = ss.ganttSheet.getRange(k, roleCol).getBackground();
                  //reformat the range based on duration
                  taskTimeRange.setBackground(roleColor);

                }
                  
              }
              
            }
          }
        }
      }
    }
  //}
}

错误原因与修复方案 #

核心错误点 #

1. 对象调用错误：多次使用ss.ganttSheet，但ganttSheet已经是通过ss.getSheetByName获取的工作表对象，ss.ganttSheet属于未定义属性，这是触发"cannot read properties of undefined"的主要原因。
2. 变量未定义：使用了startWeekCol但未提前定义，导致变量为undefined。
3. 列索引越界：表格列索引从1开始，但循环中j从0开始，会读取无效列。
4. 角色颜色获取逻辑错误：从ganttSheet读取角色数据，实际应从"Roles"工作表读取。
5. 性能问题：多次循环调用getRange和getValue，会大幅降低脚本运行效率，甚至触发谷歌服务调用限制。

修复后的代码 #

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ganttSheet = ss.getSheetByName("Gantt Chart");
  const rolesSheet = ss.getSheetByName("Roles");
  
  // 仅当编辑Gantt Chart工作表时触发
  if (e.range.getSheet().getName() !== "Gantt Chart") return;

  // 获取命名范围对应的值和位置
  const headerRow = ganttSheet.getRange('headerRow').getRow();
  const lastRow = ganttSheet.getLastRow();
  const lastCol = ganttSheet.getLastColumn();
  const firstTaskRow = headerRow + 1;
  
  const taskRoleCol = ganttSheet.getRange('taskRole').getColumn();
  const taskCol = ganttSheet.getRange('taskNames').getColumn();
  const startWeekRow = ganttSheet.getRange('startWeek').getRow();
  const startWeekCol = ganttSheet.getRange('startWeek').getColumn(); // 补充定义缺失的变量
  const expDurationCol = ganttSheet.getRange('expDuration').getColumn();

  // 批量读取Roles工作表的角色和对应颜色，避免多次调用服务
  const roleData = rolesSheet.getRange('Roles').getValues();
  const roleColors = rolesSheet.getRange('Roles').getBackgrounds();
  const roleColorMap = {};
  for (let i = 0; i < roleData.length; i++) {
    const role = roleData[i][0];
    if (role) roleColorMap[role] = roleColors[i][0];
  }

  // 读取Gantt Chart的表头周数数据
  const weekHeaders = ganttSheet.getRange(startWeekRow, 1, 1, lastCol).getValues()[0];

  // 遍历所有任务行
  for (let i = firstTaskRow; i <= lastRow; i++) {
    const currentTask = ganttSheet.getRange(i, taskCol).getValue();
    const currentStartWeek = ganttSheet.getRange(i, startWeekCol).getValue();
    const currentDuration = ganttSheet.getRange(i, expDurationCol).getValue();
    const currentRole = ganttSheet.getRange(i, taskRoleCol).getValue();

    if (!currentTask || !currentStartWeek || !currentDuration || !currentRole) continue;

    // 找到起始周对应的列
    const startColIndex = weekHeaders.indexOf(currentStartWeek);
    if (startColIndex === -1) continue;

    // 计算任务覆盖的单元格范围（保留原逻辑的*2+1计算方式）
    const taskRange = ganttSheet.getRange(i, startColIndex + 1, 1, currentDuration * 2 + 1);
    // 设置背景色，默认白色
    taskRange.setBackground(roleColorMap[currentRole] || "#ffffff");
  }
}

关键修改说明 #

1. 恢复onEdit触发器，添加工作表判断，仅在编辑"Gantt Chart"时触发
2. 修正所有ss.ganttSheet为ganttSheet，解决未定义属性错误
3. 批量读取角色数据和颜色，构建映射表，提升性能
4. 批量读取周表头数据，用indexOf快速定位起始周列，替代循环遍历
5. 补充缺失的startWeekCol变量定义
6. 添加空值判断，避免无效数据触发错误
7. 简化任务范围的计算逻辑，修复列索引错误

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