Office Script pass Values to Powerautomate

Question

In Office Script from an Excel online Macro I receive 2 errors. My Code:

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
   // console.log("countA: " + countA);
  }

  var sourceSheet_new = workbook.getWorksheet("Sheet1")
  var range2 = sourceSheet_new.getUsedRange().getColumn(12);
  var visibleValues = range2.getVisibleView().getValues();
  var numericValues = visibleValues.filter(value => !isNaN(value));

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

    // Create object with named variables
    let outputObj = {
        CountA1: countA,
        CountB2: countB,
        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,
    };

    // Set output to object
    return outputObj;
}

Problems:

[21, 60] Argument of type '(string | number | boolean)[]' is not assignable to parameter of type 'number'.

var value =0; before does not eliminate the problem.

And my Error: After uncommenting Count17 I receive

[2, 10] [Parameter Error] Return type error: Type literal contains invalid type 'Unrecognized'.

So I can't pass more than 16 values to Powerautomate

Answer 1

• visibleValues is a nested array. !isNaN(value) doesn't work. Use typeof value[0] === "number" to filter numeric values.

• function main(...): {} { a pair of curly bracket is needed.

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
        // console.log("countA: " + countA);
    }

    var sourceSheet_new = workbook.getWorksheet("Sheet1")
    var range2 = sourceSheet_new.getUsedRange().getColumn(12);
    var visibleValues = range2.getVisibleView().getValues();
    var numericValues = visibleValues.filter(value => typeof value[0] === "number");

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

    // Create object with named variables
    let outputObj = {
        CountA1: countA,
        CountB2: countB,
        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,
    };

    // Set output to object
    return outputObj;
}

---

Update: get the max value

  const convertedData: number[][] = visibleValues.map(row =>
    row.map(cell => typeof cell === 'number' ? cell : Number.MIN_VALUE)
  );

  let highestNumber = convertedData.reduce((max, subArray) => 
    Math.max(max, ...subArray), Number.MIN_VALUE
      );
  
  console.log(highestNumber)