Office Script向Power Automate传值时遇两类错误求助

错误1：第21行类型不匹配问题 #

• 问题原因：range2.getVisibleView().getValues()返回的是二维数组（每个元素对应一行的单元格值数组），你直接用value => !isNaN(value)判断，相当于把整个行数组传给isNaN，导致类型不匹配。
• 修复方案：先提取单列的单元格值（取每行第一个元素），再判断是否为有效数字。修改代码如下：

var numericValues = visibleValues
  .filter(row => typeof row[0] === 'number' && !isNaN(row[0])) // 明确判断是数字类型且非NaN
  .map(row => row[0]); // 提取单列的数值

错误2：返回超过16个值导致的类型错误 #

• 问题原因：Office Script对返回给Power Automate的对象有顶层属性数量限制（最多16个），超过后会触发类型识别错误。
• 修复方案：将同类值包装成数组或嵌套对象，减少顶层属性数量。以下两种方式任选其一：
  方式1：用数组批量存储

  let outputObj = {
      CountA1: countA,
      CountB2: countB,
      CountCList: [countC, countC, countC, countC, countC, countC, countC, countC, countC, countC, countC, countC, countC, countC, countC] // 对应CountC3到CountC17
  };
  

  方式2：用嵌套对象分组

  let outputObj = {
      CountA1: countA,
      CountB2: countB,
      CountCGroup: {
          CountC3: countC,
          CountC4: countC,
          CountC5: countC,
          CountC6: countC,
          CountC7: countC,
          CountC8: countC,
          CountC9: countC,
          CountC10: countC,
          CountC11: countC,
          CountC12: countC,
          CountC13: countC,
          CountC14: countC,
          CountC15: countC,
          CountC16: countC,
          CountC17: countC,
      }
  };
  

修改后的完整代码示例 #

function main(workbook: ExcelScript.Workbook) {
    // Get the active cell and worksheet.
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();

    let countA = 0;
    let countB = 0;
    let countC = 0;

    var i = 0;
    for (i = 0; i < 5; i++) {
        countA = countA + 1
    }

    var sourceSheet_new = workbook.getWorksheet("Sheet1")
    var range2 = sourceSheet_new.getUsedRange().getColumn(12);
    var visibleValues = range2.getVisibleView().getValues();
    // 修复类型不匹配问题
    var numericValues = visibleValues
        .filter(row => typeof row[0] === 'number' && !isNaN(row[0]))
        .map(row => row[0]);

    console.log("countA: " + countA);
    console.log("countB: " + countB);
    console.log("countC: " + countC);

    // 修复返回值数量限制问题：使用嵌套对象
    let outputObj = {
        CountA1: countA,
        CountB2: countB,
        CountCGroup: {
            CountC3: countC,
            CountC4: countC,
            CountC5: countC,
            CountC6: countC,
            CountC7: countC,
            CountC8: countC,
            CountC9: countC,
            CountC10: countC,
            CountC11: countC,
            CountC12: countC,
            CountC13: countC,
            CountC14: countC,
            CountC15: countC,
            CountC16: countC,
            CountC17: countC,
        }
    };

    return outputObj;
}

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