Good afternoon! I am working on api integration with google sheets using the API to Sheets extension https://workspace.google.com/marketplace/app/api_to_sheets/245778206812. Can't solve JMESPath Query problem, need your help
Initial code
{
"result": {
"postings": [
{
"posting_number": "49382959-0024-1",
"order_id": 633398070,
"order_number": "49382959-0024",
"status": "delivered",
"delivery_method": {
"id": 22038534499000,
"name": "Ozon Логистика самостоятельно, Москва",
"warehouse_id": 22038534499000,
"warehouse": "Московская обл., г. Видное, ул. Завидная 19",
"tpl_provider_id": 24,
"tpl_provider": "Ozon Логистика"
},
"tracking_number": "",
"tpl_integration_type": "ozon",
"in_process_at": "2022-04-08T04:56:02Z",
"shipment_date": "2022-04-09T10:00:00Z",
"delivering_date": "2022-04-09T07:14:26Z",
"cancellation": {
"cancel_reason_id": 0,
"cancel_reason": "",
"cancellation_type": "",
"cancelled_after_ship": false,
"affect_cancellation_rating": false,
"cancellation_initiator": ""
},
"customer": null,
"products": [
{
"price": "7970.0000",
"offer_id": "R22301Е",
"name": "Смеситель для ванны и душа Pursh R22301Е черный, нержавеющая сталь",
"sku": 300454648,
"quantity": 1,
"mandatory_mark": [],
"currency_code": ""
}
],
"addressee": null,
"barcodes": null,
"analytics_data": {
"region": "Ульяновская Область",
"city": "Ульяновск",
"delivery_type": "PVZ",
"is_premium": false,
"payment_type_group_name": "Карты оплаты",
"warehouse_id": 22038534499000,
"warehouse": "Московская обл., г. Видное, ул. Завидная 19",
"tpl_provider_id": 24,
"tpl_provider": "Ozon Логистика",
"delivery_date_begin": "2022-04-16T13:00:00Z",
"delivery_date_end": "2022-04-16T17:00:00Z",
"is_legal": false
},
"financial_data": {
"products": [
{
"commission_amount": 637.6,
"commission_percent": 8,
"payout": 7332.4,
"product_id": 300454648,
"old_price": 15900,
"price": 7970,
"total_discount_value": 7930,
"total_discount_percent": 49.87,
"actions": [
"Системная виртуальная скидка селлера",
"Маркетплейс промо №4"
],
"picking": null,
"quantity": 1,
"client_price": "",
"item_services": {
"marketplace_service_item_fulfillment": 0,
"marketplace_service_item_pickup": 0,
"marketplace_service_item_dropoff_pvz": 0,
"marketplace_service_item_dropoff_sc": 0,
"marketplace_service_item_dropoff_ff": 0,
"marketplace_service_item_direct_flow_trans": -54,
"marketplace_service_item_return_flow_trans": 0,
"marketplace_service_item_deliv_to_customer": 0,
"marketplace_service_item_return_not_deliv_to_customer": 0,
"marketplace_service_item_return_part_goods_customer": 0,
"marketplace_service_item_return_after_deliv_to_customer": 0
}
}
],
"posting_services": {
"marketplace_service_item_fulfillment": 0,
"marketplace_service_item_pickup": 0,
"marketplace_service_item_dropoff_pvz": -45,
"marketplace_service_item_dropoff_sc": 0,
"marketplace_service_item_dropoff_ff": 0,
"marketplace_service_item_direct_flow_trans": 0,
"marketplace_service_item_return_flow_trans": 0,
"marketplace_service_item_deliv_to_customer": -350,
"marketplace_service_item_return_not_deliv_to_customer": 0,
"marketplace_service_item_return_part_goods_customer": 0,
"marketplace_service_item_return_after_deliv_to_customer": 0
}
},
"is_express": false,
"requirements": {
"products_requiring_gtd": [],
"products_requiring_country": [],
"products_requiring_mandatory_mark": []
}
},
{
"posting_number": "07227925-0084-1",
"order_id": 633125045,
"order_number": "07227925-0084",
"status": "delivered",
"delivery_method": {
"id": 22038534499000,
"name": "Ozon Логистика самостоятельно, Москва",
"warehouse_id": 22038534499000,
"warehouse": "Московская обл., г. Видное, ул. Завидная 19",
"tpl_provider_id": 24,
"tpl_provider": "Ozon Логистика"
},
"tracking_number": "",
"tpl_integration_type": "ozon",
"in_process_at": "2022-04-07T21:20:13Z",
"shipment_date": "2022-04-09T10:00:00Z",
"delivering_date": "2022-04-09T07:14:34Z",
"cancellation": {
"cancel_reason_id": 0,
"cancel_reason": "",
"cancellation_type": "",
"cancelled_after_ship": false,
"affect_cancellation_rating": false,
"cancellation_initiator": ""
},
"customer": null,
"products": [
{
"price": "2763.0000",
"offer_id": "LА1061W",
"name": "Белый однорычажный смеситель для раковины (умывальника) из нержавеющей стали, Lemen",
"sku": 519731707,
"quantity": 1,
"mandatory_mark": [],
"currency_code": ""
},
{
"price": "2677.0000",
"offer_id": "R933O3W",
"name": "Высокий однорычажный смеситель для кухни, Pursh, белый, нержавеющая сталь",
"sku": 545484671,
"quantity": 1,
"mandatory_mark": [],
"currency_code": ""
}
],
"addressee": null,
"barcodes": null,
"analytics_data": {
"region": "Московская Область",
"city": "Красногорск",
"delivery_type": "Courier",
"is_premium": false,
"payment_type_group_name": "Карты оплаты",
"warehouse_id": 22038534499000,
"warehouse": "Московская обл., г. Видное, ул. Завидная 19",
"tpl_provider_id": 24,
"tpl_provider": "Ozon Логистика",
"delivery_date_begin": "2022-04-11T09:00:00Z",
"delivery_date_end": "2022-04-11T19:00:00Z",
"is_legal": false
},
"financial_data": {
"products": [
{
"commission_amount": 221.04,
"commission_percent": 8,
"payout": 2541.96,
"product_id": 519731707,
"old_price": 9900,
"price": 2763,
"total_discount_value": 7137,
"total_discount_percent": 72.09,
"actions": [
"Системная виртуальная скидка селлера",
"Маркетплейс промо №4"
],
"picking": null,
"quantity": 1,
"client_price": "",
"item_services": {
"marketplace_service_item_fulfillment": 0,
"marketplace_service_item_pickup": 0,
"marketplace_service_item_dropoff_pvz": 0,
"marketplace_service_item_dropoff_sc": 0,
"marketplace_service_item_dropoff_ff": 0,
"marketplace_service_item_direct_flow_trans": -37.8,
"marketplace_service_item_return_flow_trans": 0,
"marketplace_service_item_deliv_to_customer": 0,
"marketplace_service_item_return_not_deliv_to_customer": 0,
"marketplace_service_item_return_part_goods_customer": 0,
"marketplace_service_item_return_after_deliv_to_customer": 0
}
},
{
"commission_amount": 214.16,
"commission_percent": 8,
"payout": 2462.84,
"product_id": 545484671,
"old_price": 6750,
"price": 2677,
"total_discount_value": 4073,
"total_discount_percent": 60.34,
"actions": [
"Системная виртуальная скидка селлера",
"Маркетплейс промо №4"
],
"picking": null,
"quantity": 1,
"client_price": "",
"item_services": {
"marketplace_service_item_fulfillment": 0,
"marketplace_service_item_pickup": 0,
"marketplace_service_item_dropoff_pvz": 0,
"marketplace_service_item_dropoff_sc": 0,
"marketplace_service_item_dropoff_ff": 0,
"marketplace_service_item_direct_flow_trans": -43.2,
"marketplace_service_item_return_flow_trans": 0,
"marketplace_service_item_deliv_to_customer": 0,
"marketplace_service_item_return_not_deliv_to_customer": 0,
"marketplace_service_item_return_part_goods_customer": 0,
"marketplace_service_item_return_after_deliv_to_customer": 0
}
}
],
"posting_services": {
"marketplace_service_item_fulfillment": 0,
"marketplace_service_item_pickup": 0,
"marketplace_service_item_dropoff_pvz": -45,
"marketplace_service_item_dropoff_sc": 0,
"marketplace_service_item_dropoff_ff": 0,
"marketplace_service_item_direct_flow_trans": 0,
"marketplace_service_item_return_flow_trans": 0,
"marketplace_service_item_deliv_to_customer": -272,
"marketplace_service_item_return_not_deliv_to_customer": 0,
"marketplace_service_item_return_part_goods_customer": 0,
"marketplace_service_item_return_after_deliv_to_customer": 0
}
},
"is_express": false,
"requirements": {
"products_requiring_gtd": [],
"products_requiring_country": [],
"products_requiring_mandatory_mark": []
}
}
],
"has_next": false
}
}
JMESPath
result.postings[].{posting_number:posting_number, products:products[].offer_id}
And it was like in the last picture
In other words how to turn a column into a row via JMESPath
Please help!
