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