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

  • 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;
    }