Search code examples
ms-officeoffice-scriptsms-office-script

Office Script get excel table rows from each worksheet and return to Power automate


I'm trying to get Excel table data from every worksheet in the file, to be returned in to Power Automate.

 function main(workbook: ExcelScript.Workbook) {

  let sheets = workbook.getWorksheets();

  sheets.forEach((sheet) => {
 
    let tables = sheet.getTables();
    
    if (tables.length > 0) {
      let firstTable = tables[0];
      let tableRange = firstTable.getRange();
      let tableData = tableRange.getValues();
      
      console.log(`First table data of worksheet '${sheet.getName()}': `, tableData);
    } else {
      console.log(`No tables found in worksheet '${sheet.getName()}'`);
    }
  });

}

What I need to do is return the data so that I can access it in Power Automate.


Solution

    • Merge all rows from tables into an array and return it at the end of main()
    function main(workbook: ExcelScript.Workbook): (string | number | boolean)[][] {
        const allSheets = workbook.getWorksheets();
        let allValues: (string | number | boolean)[][] = [];
        allSheets.forEach((sheet) => {
            const tables = sheet.getTables();
            if (tables) {
                allValues.push(tables[0].getHeaderRowRange().getValues()[0]);
                tables.forEach((table) => {
                    const tabValues = table.getRangeBetweenHeaderAndTotal().getValues();
                    tabValues.forEach((row) => {
                        allValues.push(row);
                    })
                })
            }
        })
        return allValues;
    }