Loop through worksheets get data from table on each sheet

Question

I'm trying to loop through worksheets in an excel file and get the data table rows from each sheet, to Power Automate.

I'm trying this in my script, but I need:

function main(workbook: ExcelScript.Workbook) {
  let ws = workbook.getWorksheet("Sheet1");
  let tables = ws.getTables();
    tables.forEach((table) => {
    return(table);
  }
}

However this isn't working and I'm not sure how to do it.

There is 1 table of data on each worksheet in the excel file, and I need to loop through the worksheets and return the table rows, adding them all to a single master array, which gets returned to Power Automate.

Thanks in advance for any help on this

Answer 1

They have a method called getWorksheets:

function main(workbook: ExcelScript.Workbook) {

  let sheets = workbook.getWorksheets();

  sheets.forEach((sheet) => {
 
    let tables = sheet.getTables();
    
    if (tables.length > 0) {
      // Get the first table in the worksheet
      let firstTable = tables[0];
      let tableRange = firstTable.getRange();
      let tableData = tableRange.getValues();
      
      // Log the table data (you can modify this to do whatever you want with the data)
      console.log(`First table data of worksheet '${sheet.getName()}': `, tableData);
    } else {
      console.log(`No tables found in worksheet '${sheet.getName()}'`);
    }
  });

}

You can find more info on accessin table data here: https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.table?view=office-scripts