Office Script set array values to row range

Question

I'm trying to set an array, to a range of Excel cells A1:D1, using an Office Script.

The incoming array is from a column range A1:A4.

The incoming array appears to be in format:

[[1],[2],[3],[4]]

The array is being passed into my main function like this:

function main(workbook: ExcelScript.Workbook, incomingData: string[][]) {

  let sheet = workbook.getWorksheet("Sheet1");
  let destinationRange = ws.getRange("A1:D1");
  
  destinationRange.setValues(incomingData);
};

However this is giving me an error:

Line 6: Range setValues: The number of rows or columns in the input array doesn't match the size or dimensions of the range.

I need some help transforming/transposing the input array in order to set the new row successfully but I can't figure it out!

I used JSON.stringify to output to a single cell, and this gave the value:

[["[[1],[2],[3],[4]]"]]

So I'm a bit confused. Any help appreciated!

Answer 1

• Convert [[1],[2],[3],[4]] to [[1,2,3,4]]

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    let srcRange = sheet.getRange("A1:A4");
    let desRange = sheet.getRange("C1:F1");

    let colVals = srcRange.getValues();
    console.log(colVals)

    let rowVals = [colVals.map(item => item[0])];
    console.log(rowVals)

    desRange.setValues(rowVals);
}