cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

JSON output into Excel table

I have this flow in which I make a GET HTTP call to QuickBook's API to get a General Ledger report. I have managed with the help of some knowledgeable people, to get parts of the JSON output (but not all) into an excel table. I am looking to get all the transactions that appear on the JSON output. Here is how my flow looks like:

 

 
Coffee123_7-1639165224882.png

 

Initialize variables are used later in the apply to each step to get specific JSON output.

 

Here is my first expression in one of the loops:

 

 
Coffee123_9-1639165284286.png

 

Second expression in the other loop:

 

 
Coffee123_10-1639165309049.png

 

In the set variables, I use the variables initialized before to get specific values from the JSON output:

 

 
Coffee123_11-1639165333603.png

expression used in the set variable = items('Apply_to_each_2')?['ColData']?[0]?['value']. The others set variables have the same expression but with different numbers instead of the cero.

 

This is how the excel output looks like, I am missing some transactions and accounts:

 

 
Coffee123_12-1639165383349.png

I will share the JSON output on a comment below (too large). Any help will be appreciated, thanks!

2 REPLIES 2
Anonymous
Not applicable

Here is the schema, significantly cut down in order to avoid errors messages while posting here:

 

{

  "Columns": {

    "Column": [

      {

        "ColTitle": "Date",

        "ColType": "Date",

        "MetaData": [

          {

            "Name": "ColKey",

            "Value": "tx_date"

          }

        ]

      },

      {

        "ColTitle": "Num",

        "ColType": "String",

        "MetaData": [

          {

            "Name": "ColKey",

            "Value": "doc_num"

          }

        ]

      },

      {

        "ColTitle": "Account",

        "ColType": "String",

        "MetaData": [

          {

            "Name": "ColKey",

            "Value": "account_name"

          }

        ]

      },

      {

        "ColTitle": "Amount",

        "ColType": "Money",

        "MetaData": [

          {

            "Name": "ColKey",

            "Value": "subt_nat_amount"

          }

        ]

      },

      {

        "ColTitle": "Balance",

        "ColType": "Money",

        "MetaData": [

          {

            "Name": "ColKey",

            "Value": "rbal_nat_amount"

          }

        ]

      }

    ]

  },

  "Rows": {

    "Row": [

      {

        "Header": {

          "ColData": [

            {

              "value": "Checking",

              "id": "35"

            },

            {

              "value": ""

            },

            {

              "value": ""

            },

            {

              "value": ""

            },

            {

              "value": ""

            }

          ]

        },

        "Rows": {

          "Row": [

            {

              "ColData": [

                {

                  "value": "Beginning Balance"

                },

                {

                  "value": ""

                },

                {

                  "value": ""

                },

                {

                  "value": ""

                },

                {

                  "value": "2124.50"

                }

              ],

              "type": "Data"

            },

            {

              "ColData": [

                {

                  "value": "2021-10-30"

                },

                {

                  "value": ""

                },

                {

                  "value": "Checking",

                  "id": "35"

                },

                {

                  "value": "-23.50"

                },

                {

                  "value": "2101.00"

                }

              ],

              "type": "Data"

            }

          ]

        },

        "Summary": {

          "ColData": [

            {

              "value": "Total for Checking"

            },

            {

              "value": "-23.50"

            },

            {

              "value": ""

            },

            {

              "value": ""

            },

            {

              "value": ""

            }

          ]

        },

        "type": "Section"

      }

    ]

  }

}

 

 

Anonymous
Not applicable

This is my desired output for all transactions:

Coffee123_0-1639167028620.png

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,112)