Office Script开发求助：Table1数据处理插入Table2报错排查

问题背景 #

• 工作簿包含Sheet1和Sheet2两个工作表：
  • Sheet1的Table1列：Server、Health、Error、PrevHealth、PrevError，分别存储服务器名称、当日健康状态、当日错误信息、前日健康状态、前日错误信息
  • Sheet2的Table2为空白表格，列：Server、LastHealth、LastError
• 需求逻辑：
  1. 若Health列值为Critical，将当前行Server、Health、Error插入Table2对应列
  2. 若Health不为Critical但PrevHealth为Critical，将当前行Server、PrevHealth、PrevError插入Table2对应列
  3. 其他情况跳过
• 报错信息：Line 25: Table addRow: The argument is invalid or missing or has an incorrect format.

原代码 #

function main(workbook: ExcelScript.Workbook) { 
// Get the worksheets 
const sheet1 = workbook.getWorksheet("Sheet1"); 
const sheet2 = workbook.getWorksheet("Sheet2");
// Get the tables
const table1 = sheet1.getTable("Table1");
const table2 = sheet2.getTable("Table2");

// Get the data range from Table1
const dataRange = table1.getRangeBetweenHeaderAndTotal();

// Loop through each row in Table1
for (const row of dataRange.getValues()) {
    const server = row[0]; // Server column
    const health = row[1]; // Health column
    const error = row[2]; // Error column
    const prevHealth = row[3]; // PrevHealth column
    const prevError = row[4]; // PrevError column

    // Check conditions
    if (health === "Critical") {
        // Insert a new row in Table2 with Server, Health, Error
        table2.addRow([server, health, error]);
    } else if (prevHealth === "Critical") {
        // Insert a new row in Table2 with Server, PrevHealth, PrevError
        table2.addRow([server, prevHealth, prevError]);
    }
    // Otherwise, skip the row
}

问题原因 #

1. 空值/类型不匹配：dataRange.getValues()返回的数组中，未填充的单元格会以undefined或空值存在，addRow方法要求传入的数组元素类型必须与Table2列的格式完全匹配，空值会触发格式错误
2. 列顺序依赖：原代码使用数组索引对应列，若Table2的列顺序发生变化，会导致数据插入错误，也可能触发格式校验失败

解决方法 #

推荐使用对象形式指定列名和对应值添加行，这种方式不依赖列顺序，且能自动处理空值适配，鲁棒性更强；也可以提前将空值替换为兼容类型（如空字符串）。

修正后代码（对象形式，推荐） #

function main(workbook: ExcelScript.Workbook) { 
  // 获取工作表
  const sheet1 = workbook.getWorksheet("Sheet1"); 
  const sheet2 = workbook.getWorksheet("Sheet2");
  // 获取表格对象
  const table1 = sheet1.getTable("Table1");
  const table2 = sheet2.getTable("Table2");

  // 遍历Table1的每一行
  for (const row of table1.getRows()) {
    const rowValues = row.getValues()[0];
    const server = rowValues[0];
    const health = rowValues[1];
    const error = rowValues[2];
    const prevHealth = rowValues[3];
    const prevError = rowValues[4];

    // 按条件插入行到Table2
    if (health === "Critical") {
      table2.addRow({
        Server: server,
        LastHealth: health,
        LastError: error
      });
    } else if (prevHealth === "Critical") {
      table2.addRow({
        Server: server,
        LastHealth: prevHealth,
        LastError: prevError
      });
    }
  }
}

备选修正方案（处理空值） #

如果坚持使用数组形式，可提前将空值替换为兼容类型：

function main(workbook: ExcelScript.Workbook) { 
  const sheet1 = workbook.getWorksheet("Sheet1"); 
  const sheet2 = workbook.getWorksheet("Sheet2");
  const table1 = sheet1.getTable("Table1");
  const table2 = sheet2.getTable("Table2");

  const dataRange = table1.getRangeBetweenHeaderAndTotal().getValues();

  for (const row of dataRange) {
    // 将空值替换为空字符串，确保类型匹配
    const server = row[0] ?? "";
    const health = row[1] ?? "";
    const error = row[2] ?? "";
    const prevHealth = row[3] ?? "";
    const prevError = row[4] ?? "";

    if (health === "Critical") {
      table2.addRow([server, health, error]);
    } else if (prevHealth === "Critical") {
      table2.addRow([server, prevHealth, prevError]);
    }
  }
}

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