web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : o47AEr4B88itgqntnKbGrn
Power Automate - General Discussion
Unanswered

How to filter data from JSON file and then output the data into an excel file.

Like (0) ShareShare
ReportReport
Posted on 2 Apr 2021 12:20:03 by 2

I am having trouble with parse JSON function and trying to filter the code below, essentially I want to filter line items and shipping address information and then dump that info. into specific cells within an excel file to make it more readable from all the data gathered without having to do it manually.

 

{
"order": {
"id": 3664907730996,
"app_id": 1354745,
"browser_ip": null,
"buyer_accepts_marketing": false,
"cancel_reason": null,
"cancelled_at": null,
"cart_token": null,
"checkout_id": null,
"checkout_token": null,
"closed_at": null,
"confirmed": true,
"contact_email": "INFO@DIRECTDOORS.COM",
"created_at": "2021-03-31T13:07:45+01:00",
"currency": "GBP",
"customer_locale": "en",
"device_id": null,
"discount_codes": [],
"email": "INFO@DIRECTDOORS.COM",
"financial_status": "paid",
"fulfillment_status": null,
"gateway": "manual",
"landing_site": null,
"landing_site_ref": null,
"location_id": null,
"name": "#32383",
"note": "",
"note_attributes": [],
"number": 31383,
"order_number": 32383,
"order_status_url": "https://www.directdoors.com/346685492/orders/4efa5743d1d0196ce1d686a8037ca5e9/authenticate?key=4e991654c0d13dac282afbc870b059c0",
"payment_gateway_names": [
"manual"
],
"phone": null,
"presentment_currency": "GBP",
"processed_at": "2021-03-31T13:07:45+01:00",
"processing_method": "manual",
"reference": null,
"referring_site": null,
"source_identifier": null,
"source_name": "shopify_draft_order",
"source_url": null,
"subtotal_price": "1137.93",
"subtotal_price_set": {
"shop_money": {
"amount": "1137.93",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "1137.93",
"currency_code": "GBP"
}
},
"tags": "CAR, DIR, LOCAL, PS2, PWTRU, SAM, TEST DO NOT ORDER, XLJ",
"tax_lines": [
{
"price": "189.65",
"rate": 0.2,
"title": "GB VAT",
"price_set": {
"shop_money": {
"amount": "189.65",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "189.65",
"currency_code": "GBP"
}
}
}
],
"taxes_included": true,
"test": false,
"token": "4efa5743d1d0196ce1d686a8037ca5e9",
"total_discounts": "0.00",
"total_discounts_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"total_line_items_price": "1137.93",
"total_line_items_price_set": {
"shop_money": {
"amount": "1137.93",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "1137.93",
"currency_code": "GBP"
}
},
"total_price": "1137.93",
"total_price_set": {
"shop_money": {
"amount": "1137.93",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "1137.93",
"currency_code": "GBP"
}
},
"total_price_usd": "1564.04",
"total_shipping_price_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"total_tax": "189.65",
"total_tax_set": {
"shop_money": {
"amount": "189.65",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "189.65",
"currency_code": "GBP"
}
},
"total_tip_received": "0.00",
"total_weight": 2468200,
"updated_at": "2021-03-31T13:07:56+01:00",
"user_id": 12353306676,
"billing_address": {
"first_name": "James",
"address1": "BAY 5 EASTFIELD INDUSTRIAL EST",
"phone": "1968671680",
"city": "EDINBURGH",
"zip": "EH26 8HA",
"province": null,
"country": "United Kingdom",
"last_name": "Szpunar",
"address2": "PENICUIK",
"company": "THE DOOR CENTRE LIMITED",
"latitude": 55.8336305,
"longitude": -3.217556,
"name": "James Szpunar",
"country_code": "GB",
"province_code": null
},
"customer": {
"id": 610030190644,
"email": "INFO@DIRECTDOORS.COM",
"accepts_marketing": false,
"created_at": "2018-09-18T08:05:46+01:00",
"updated_at": "2021-03-31T13:07:46+01:00",
"first_name": "Test",
"last_name": "Account",
"orders_count": 10,
"state": "enabled",
"total_spent": "1739.15",
"last_order_id": 3664907730996,
"note": null,
"verified_email": true,
"multipass_identifier": null,
"tax_exempt": false,
"phone": null,
"tags": "",
"last_order_name": "#32383",
"currency": "GBP",
"accepts_marketing_updated_at": "2019-01-05T13:48:00+00:00",
"marketing_opt_in_level": null,
"default_address": {
"id": 692167671860,
"customer_id": 610030190644,
"first_name": "James",
"last_name": "Szpunar",
"company": "THE DOOR CENTRE LIMITED",
"address1": "BAY 5 EASTFIELD INDUSTRIAL EST",
"address2": "PENICUIK",
"city": "EDINBURGH",
"province": null,
"country": "United Kingdom",
"zip": "EH26 8HA",
"phone": "1968671680",
"name": "James Szpunar",
"province_code": null,
"country_code": "GB",
"country_name": "United Kingdom",
"default": true
}
},
"discount_applications": [],
"fulfillments": [],
"line_items": [
{
"id": 9639747158068,
"fulfillable_quantity": 1,
"fulfillment_service": "manual",
"fulfillment_status": null,
"gift_card": false,
"grams": 200,
"name": "Steelworx 50mm Sliding Door FPH1002 Round Flush Pulls (Pair) - 2 Finishes - 50mm Round Flush Pull Set - Stainless Steel",
"pre_tax_price": "12.21",
"pre_tax_price_set": {
"shop_money": {
"amount": "12.21",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "12.21",
"currency_code": "GBP"
}
},
"price": "14.65",
"price_set": {
"shop_money": {
"amount": "14.65",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "14.65",
"currency_code": "GBP"
}
},
"product_exists": true,
"product_id": 1056404766772,
"properties": [],
"quantity": 1,
"requires_shipping": true,
"sku": "FPH1002SSS",
"taxable": true,
"title": "Steelworx 50mm Sliding Door FPH1002 Round Flush Pulls (Pair) - 2 Finishes",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"variant_id": 8744158724148,
"variant_inventory_management": null,
"variant_title": "50mm Round Flush Pull Set - Stainless Steel",
"vendor": "CAR",
"tax_lines": [
{
"price": "2.44",
"price_set": {
"shop_money": {
"amount": "2.44",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "2.44",
"currency_code": "GBP"
}
},
"rate": 0.2,
"title": "GB VAT"
}
],
"discount_allocations": []
},
{
"id": 9639747190836,
"fulfillable_quantity": 1,
"fulfillment_service": "manual",
"fulfillment_status": null,
"gift_card": false,
"grams": 1234000,
"name": "Thruframe Double Door Modern White Primed Architrave Set - 69x25mm",
"pre_tax_price": "69.35",
"pre_tax_price_set": {
"shop_money": {
"amount": "69.35",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "69.35",
"currency_code": "GBP"
}
},
"price": "83.22",
"price_set": {
"shop_money": {
"amount": "83.22",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "83.22",
"currency_code": "GBP"
}
},
"product_exists": true,
"product_id": 1189491605556,
"properties": [
{
"name": "Special Delivery Instructions: Please click info button for more details.",
"value": "Not required - I will be available"
},
{
"name": "This item is a bespoke product. Please click the info button for more details.",
"value": "I confirm I am happy to proceed"
},
{
"name": "next_day",
"value": "true"
},
{
"name": "groupedProduct",
"value": "true"
},
{
"name": "min",
"value": "0"
},
{
"name": "aog",
"value": "architraves"
},
{
"name": "GroupId",
"value": "1617052174076"
}
],
"quantity": 1,
"requires_shipping": true,
"sku": "PWTRUARCDBL2569",
"taxable": true,
"title": "Thruframe Double Door Modern White Primed Architrave Set",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"variant_id": 9096417902644,
"variant_inventory_management": null,
"variant_title": "69x25mm",
"vendor": "Direct Doors",
"tax_lines": [
{
"price": "13.87",
"price_set": {
"shop_money": {
"amount": "13.87",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "13.87",
"currency_code": "GBP"
}
},
"rate": 0.2,
"title": "GB VAT"
}
],
"discount_allocations": []
},
{
"id": 9639747223604,
"fulfillable_quantity": 1,
"fulfillment_service": "manual",
"fulfillment_status": null,
"gift_card": false,
"grams": 1234000,
"name": "Verona Double Evokit Pocket Doors - Clear Glass - Primed - 2060x2830mm Wide/1981x686mm Doors",
"pre_tax_price": "866.72",
"pre_tax_price_set": {
"shop_money": {
"amount": "866.72",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "866.72",
"currency_code": "GBP"
}
},
"price": "1040.06",
"price_set": {
"shop_money": {
"amount": "1040.06",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "1040.06",
"currency_code": "GBP"
}
},
"product_exists": true,
"product_id": 1177797623860,
"properties": [
{
"name": "Flush Handle Requirement",
"value": "Purchase 2 pairs below - sold as pairs"
},
{
"name": "Overall Wall Thickness",
"value": "100mm Frame Jamb Width"
},
{
"name": "groupedProduct",
"value": "true"
},
{
"name": "min",
"value": "1"
},
{
"name": "tlp",
"value": "true"
},
{
"name": "GroupId",
"value": "1617052174076"
}
],
"quantity": 1,
"requires_shipping": true,
"sku": "GWPVER27-PS2",
"taxable": true,
"title": "Verona Double Evokit Pocket Doors - Clear Glass - Primed",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"variant_id": 31648845168692,
"variant_inventory_management": "shopify",
"variant_title": "2060x2830mm Wide/1981x686mm Doors",
"vendor": "XLJ",
"tax_lines": [
{
"price": "173.34",
"price_set": {
"shop_money": {
"amount": "173.34",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "173.34",
"currency_code": "GBP"
}
},
"rate": 0.2,
"title": "GB VAT"
}
],
"discount_allocations": []
},
{
"id": 9639747256372,
"fulfillable_quantity": 1,
"fulfillment_service": "manual",
"fulfillment_status": null,
"gift_card": false,
"grams": 0,
"name": "THIS IS A TEST ORDER - ORDERS TEAM, PLEASE DO NOT PROCESS THIS ORDER.",
"pre_tax_price": "0.00",
"pre_tax_price_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"price": "0.00",
"price_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"product_exists": false,
"product_id": null,
"properties": [],
"quantity": 1,
"requires_shipping": false,
"sku": null,
"taxable": false,
"title": "THIS IS A TEST ORDER - ORDERS TEAM, PLEASE DO NOT PROCESS THIS ORDER.",
"total_discount": "0.00",
"total_discount_set": {
"shop_money": {
"amount": "0.00",
"currency_code": "GBP"
},
"presentment_money": {
"amount": "0.00",
"currency_code": "GBP"
}
},
"variant_id": null,
"variant_inventory_management": null,
"variant_title": null,
"vendor": null,
"tax_lines": [],
"discount_allocations": []
}
],
"refunds": [],
"shipping_address": {
"first_name": "James",
"address1": "BAY 5 EASTFIELD INDUSTRIAL EST",
"phone": "1968671680",
"city": "EDINBURGH",
"zip": "EH26 8HA",
"province": null,
"country": "United Kingdom",
"last_name": "Szpunar",
"address2": "PENICUIK",
"company": "THE DOOR CENTRE LIMITED",
"latitude": 55.8336305,
"longitude": -3.217556,
"name": "James Szpunar",
"country_code": "GB",
"province_code": null
},
"shipping_lines": []
}
}

I have the same question (0)
  • icecarbon Profile Picture
    2 on 02 Apr 2021 at 13:33:26
    Re: How to filter data from JSON file and then output the data into an excel file.

    Hi,

     

    I was looking for something that could future proof more specifically as the data that comes in wont always be what I specifically put into the select box. I was wondering if there is a way to read the data as it comes in from an order and if you could show me a step by step guide as to how you formed the initialising of the variable, used parse JSON and the a filter array to output the specific data?

  • DamoBird365 Profile Picture
    8,942 Microsoft Employee on 02 Apr 2021 at 13:06:13
    Re: How to filter data from JSON file and then output the data into an excel file.

    Hi @icecarbon 

     

    I've done something very similar here https://damobird365.birdhoose.co.uk/2021/03/24/power-automate-office-scripts-populate-an-excel-template-dynamically-using-type-script/

     

    You might want to use the Select Action to tidy up your JSON and parse something more relevant for your requirements.

     

    If you need any more explanation let me know and I will try and help.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    Cheers,
    Damien


    P.S. take a look at my new blog here

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Telen Wang – Community Spotlight

We are honored to recognize Telen Wang as our August 2025 Community…

Congratulations to the July Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Tomac Profile Picture

Tomac 986 Moderator

#2
stampcoin Profile Picture

stampcoin 699 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 577 Super User 2025 Season 2

Loading complete