I'm in the final step of converting JSON output from an API call into an Excel table. Power Automate is using Office Scripts to transfer the data into an Excel table but it is doing so in an incorrect format. The output that is being fed into the script looks like this:
"Id","ClientName" & "QBOName" = my table's headers.
When I run my script, my table ends up looking like this:
When it's supposed to look like this (including all the other info in the rows below):
I'm using a script from a blog of my friend @DamoBird365 https://www.damobird365.com/split-a-workbook-into-multiple-worksheets/. The script looks like this:
function main(workbook: ExcelScript.Workbook,
MainTable: string = "Table1", //new table name
worksheetInformation: WorksheetData) {
// Get default worksheet Sheet1
let sheet = workbook.getWorksheet(`Sheet1`);
// Create range based on the size of data
let range = sheet.getRangeByIndexes(0, 0, worksheetInformation.data.length, worksheetInformation.data[0].length);
//Populate sheet with data
range.setValues(worksheetInformation.data)
//Autofit column width
range.getFormat().autofitColumns();
//Create New Table
let newTable = workbook.addTable(range, true);
newTable.setName(MainTable);
}
// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
data: string[][];
}
@DamoBird365, or does anyone know the adjustment I would need to make to the Script? I noticed that the only difference between the output from my ComposeArray action (output being used in the script) and the input the script is showing after the flow runs, is an additional square bracket as per the pic below:
I'm including here a link to a zip folder with my current flow in case it helps https://drive.google.com/file/d/1W9ALSiNtQT4ABJ3KCa1T-bspYtvVyo7H/view?usp=sharing.
Any help will be greatly appreciated! 🙂
Hi
I would go a different way. Does your API call always return the data in the same structure (column names)? If so I would create an excel file as template, copy and create the file where you want it, restructure the API call data e.g. with a Select action and make a add row to table action to fill the excel file.
Maybe this helps you...
stampcoin
45
Michael E. Gernaey
25
Super User 2025 Season 1
Chriddle
21
Super User 2025 Season 1