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

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / How to filter data fro...
Power Automate
Unanswered

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

(0) ShareShare
ReportReport
Posted on by icecarbon 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": []
}
}

Categories:
General topics
  • DamoBird365 Profile Picture
    DamoBird365 8,942 Microsoft Employee on at

    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

  • icecarbon Profile Picture
    icecarbon 2 on at

    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?

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

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Mohsin Ali Profile Picture

Mohsin Ali 389

#2
11manish Profile Picture

11manish 345 Super User 2026 Season 2

#3
M Bilal Khan Profile Picture

M Bilal Khan 308

Last 30 days Overall leaderboard

Featured topics

Known issue with browser automation in Power Automate for Desktop on Google Chrome 146 and 147
Share your ideas and struggles with building Power Automate flows!
Survey: Power Automate mobile app
How to Ask for Help with Your Power Automate Workflow
Start and wait for an approval action
Restore a deleted flow
List of actions cheat sheet for Power Automate Desktop
Welcome to the Power Automate forum!

Product updates

Microsoft Power Platform Community release plans