Search code examples
ms-officeoffice-js

Office Script return multidimensional array

Can you help me, how can i return a function in office script that returns an array with: a mail, and 2 array of row ranges:

enter image description here

the return i need is (if possible )

[["anymail@mail.com"],["Estac","22Jan","23Jan","24Jan","25Jan"],["448 - RRHH","","R","R",""]]
Solution

  • Assuming this is Office Scripts and not an Office Add-in, you can return a 2D array from the script by adding the appropriate type to the signature. This script returns the data in the used range of Sheet1:

    function main(workbook: ExcelScript.Workbook): (string | number | boolean)[][] {
    let selectedSheet = workbook.getWorksheet("Sheet1");
    let range = selectedSheet.getUsedRange();
    let rangeData = range.getValues();
    return rangeData;
}

    You could also wrap the array in an object to make dealing with it in Power Automate easier. This sample shows how to merge worksheets and returns 2D arrays as part of that: https://learn.microsoft.com/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook