Forum Discussion
Pagination code returns first page forever instead of properly paginating
Thanks ams1 -
I'm not able to share the API documentation directly, but here's a sample response object they provided:{ "responses": [ { "id": "5e22085d5379215be800002b", "survey_id": "6e22085d5379215be800002b", "created_at": "2020-09-24T00:20:10", "conversation_id": "7e22085d5379215be800002b", "answers": [ { "question": { "id": "5e22085d5379215be800002b", "value": "Choose one option", "type": "multichoice | multiselect | range | nps | singleline" }, "answer": { "value": "'yes' OR ['Red', 'Blue']" } } ], "snapshot_data": { "device": { "id": "55fbe2e775bf3c760b1000bc", "carrier": "Verizon", "custom_data": { "key": "CustomKey", "value": "CustomValue" }, "manufacturer": "Samsung", "model": "S22", "os_api_level": "3.5.9", "os_name": "Android", "os_version": "5.2.0" }, "person": { "id": "95fbe2e775bf3c760b1000bc", "custom_data": { "key": "CustomKey", "value": "CustomValue" }, "email": "[email protected]", "facebook_id": "1184928249824", "mparticle_id": "-29482958295729572", "name": "Jack Daniels" }, "app_release": { "cf_bundle_version": "78", "cf_bundle_short_version_string": "4.5.11", "sdk_version": "4.5.11", "sdk_platform": "iOS", "sdk_distribution": "source", "sdk_distribution_version": "4.5.11" } } } ], "ends_with": "6e22085d5379215be800002b", "page_size": 250, "has_more": true }And these are the sample params:
I've tried your sample code and I think I just need to make sure the result is the `"responses": [` portion and the `ends_with`, `page_size`, and `has_more` just used for cursor/paging.
Hey Everyone ! I am currently delving into the world of connecting Power BI to Shopify using its API. As I'm relatively new to APIs, I have been experimenting with pagination in my Power Query. However, I am encountering a bit of a snag – I can only seem to retrieve 250 records at a time.
According to the Shopify Docs, it appears that I can only fetch a maximum of 250 records per request. However, I need to retrieve all records, and I'm uncertain about the total number of records or pages available.
Could you lend me a hand in resolving this issue?
I am currently using the following query to retrieve all records, but I am stuck at retrieving only 250 records:.
let
GetOrders = (url as text) =>
let
response = Web.Contents(url),
json = Json.Document(response),
orders = json[orders]
in
orders,
GetAllOrders = (previousPageUrl as text, nextPageUrl as text) =>
let
allOrders = {},
GetPageOrders = (pageUrl as text) =>
let
orders = GetOrders(pageUrl),
nextPageHeader = try Record.Field(Web.Contents(pageUrl, [Headers=[#"Accept"="application/json"]]), "Link") otherwise null,
nextPageUrl = if nextPageHeader <> null then Text.BetweenDelimiters(nextPageHeader, "<", ">; rel=""next""") else null
in
if nextPageUrl <> null then
let
nextOrders = GetPageOrders(nextPageUrl)
in
List.Combine({orders, nextOrders})
else
orders
in
GetPageOrders(previousPageUrl),
previousPageUrl = "https://prostandard.myshopify.com/admin/api/2024-01/orders.json?limit=250&page_info=eyJkaXJlY3Rpb24iOiJwcmV2IiwibGFzdF9pZCI6NTU3OTAwNTc4ODI1OSwibGFzdF92YWx1ZSI6IjIwMjQtMDMtMDcgMTE6NDQ6NDAuMjg5NDE0In0",
nextPageUrl = "https://prostandard.myshopify.com/admin/api/2024-01/orders.json?limit=250&page_info=eyJkaXJlY3Rpb24iOiJuZXh0IiwibGFzdF9pZCI6NTU3MDQ0NTA4MjcyMywibGFzdF92YWx1ZSI6IjIwMjQtMDItMjkgMTk6NDc6MzkuNTQ1Njg0In0",
allOrders = GetAllOrders(previousPageUrl, nextPageUrl),
#"Converted to Table" = Table.FromList(allOrders, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "admin_graphql_api_id", "app_id", "browser_ip", "buyer_accepts_marketing", "cancel_reason", "cancelled_at", "cart_token", "checkout_id", "checkout_token", "client_details", "closed_at", "company", "confirmation_number", "confirmed", "contact_email", "created_at", "currency", "current_subtotal_price", "current_subtotal_price_set", "current_total_additional_fees_set", "current_total_discounts", "current_total_discounts_set", "current_total_duties_set", "current_total_price", "current_total_price_set", "current_total_tax", "current_total_tax_set", "customer_locale", "device_id", "discount_codes", "email", "estimated_taxes", "financial_status", "fulfillment_status", "landing_site", "landing_site_ref", "location_id", "merchant_of_record_app_id", "name", "note", "note_attributes", "number", "order_number", "order_status_url", "original_total_additional_fees_set", "original_total_duties_set", "payment_gateway_names", "phone", "po_number", "presentment_currency", "processed_at", "reference", "referring_site", "source_identifier", "source_name", "source_url", "subtotal_price", "subtotal_price_set", "tags", "tax_exempt", "tax_lines", "taxes_included", "test", "token", "total_discounts", "total_discounts_set", "total_line_items_price", "total_line_items_price_set", "total_outstanding", "total_price", "total_price_set", "total_shipping_price_set", "total_tax", "total_tax_set", "total_tip_received", "total_weight", "updated_at", "user_id", "billing_address", "customer", "discount_applications", "fulfillments", "line_items", "payment_terms", "refunds", "shipping_address", "shipping_lines"}, {"id", "admin_graphql_api_id", "app_id", "browser_ip", "buyer_accepts_marketing", "cancel_reason", "cancelled_at", "cart_token", "checkout_id", "checkout_token", "client_details", "closed_at", "company", "confirmation_number", "confirmed", "contact_email", "created_at", "currency", "current_subtotal_price", "current_subtotal_price_set", "current_total_additional_fees_set", "current_total_discounts", "current_total_discounts_set", "current_total_duties_set", "current_total_price", "current_total_price_set", "current_total_tax", "current_total_tax_set", "customer_locale", "device_id", "discount_codes", "email", "estimated_taxes", "financial_status", "fulfillment_status", "landing_site", "landing_site_ref", "location_id", "merchant_of_record_app_id", "name", "note", "note_attributes", "number", "order_number", "order_status_url", "original_total_additional_fees_set", "original_total_duties_set", "payment_gateway_names", "phone", "po_number", "presentment_currency", "processed_at", "reference", "referring_site", "source_identifier", "source_name", "source_url", "subtotal_price", "subtotal_price_set", "tags", "tax_exempt", "tax_lines", "taxes_included", "test", "token", "total_discounts", "total_discounts_set", "total_line_items_price", "total_line_items_price_set", "total_outstanding", "total_price", "total_price_set", "total_shipping_price_set", "total_tax", "total_tax_set", "total_tip_received", "total_weight", "updated_at", "user_id", "billing_address", "customer", "discount_applications", "fulfillments", "line_items", "payment_terms", "refunds", "shipping_address", "shipping_lines"})
in
#"Expanded Column1"
Hi RamTyagi were you able to find a solution? As a workaround, maybe you can try to test your connection with a 3rd party connector, which pulls data directly from the Shopify API and does not require any complex coding. I've tried windsor.ai, supemetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Shopify connector in the data sources list:
After that, you need to follow instructions and install the windsor.ai app from the Shopify App Store:
then on preview and destination page you will see a preview of your Shopify fields
There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url.