Skip to main content

Notifications

Community site session details

Community site session details

Session Id : VfNfoco5AKY8Y2e3YHn/Ag
Power Automate - Building Flows
Unanswered

Getting JSON to Excel Table

Like (0) ShareShare
ReportReport
Posted on 19 Mar 2022 14:39:54 by 14

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:

 

Coffee654_0-1647699434518.png

"Id","ClientName" & "QBOName" = my table's headers.

 

When I run my script, my table ends up looking like this:

 

Coffee654_1-1647699601039.png

When it's supposed to look like this (including all the other info in the rows below):

Coffee654_2-1647699665245.png

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:

 

Coffee654_4-1647700589657.png

 

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! 🙂 

 

  • PowerAppsWizard Profile Picture
    388 on 19 Mar 2022 at 18:26:30
    Re: Getting JSON to Excel Table

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Automate - Building Flows

#1
stampcoin Profile Picture

stampcoin 45

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 25 Super User 2025 Season 1

#3
Chriddle Profile Picture

Chriddle 21 Super User 2025 Season 1

Overall leaderboard