You’re offline. This is a read only version of the page.
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": []
}
}
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?
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
Share
Report
2
All responses (
Answers (
