An article to resolve my frustration in being able to ONLY retrieve the first 100 products using GraphQL, so page 1 of Shopify products.
Why?
Our use-case is that we retrieve the 100 most recently modified products at the end of each day and run some Deluge code against it to ensure that the data in Shopify is the same as in Zoho. In this case, Shopify is considered the source of truth and Zoho is the data to be overwritten, ensuring that Product IDs, Variant IDs, Inventory IDs, Current Selling Price, Inventory Level, and Barcode all match.
Our problem is that sometimes more than 100 products are modified in a day... We need to be able to do more than 100 or at least resume from a record... We need pagination.
How?
To resolve this, I'm building on top of my initial GraphQL query which will now retrieve 10 products per page and loop through 3 pages for use in Zoho Deluge. If this works, we can increase the number of pages as well as the number of products per page:
The GraphQL
The key value to retrieve is "cursor"; we're also going to use "hasNextPage" to determine if we need to keep looping:
copyraw
{ productVariants(first: 10, query: "updated_at:>'2023-02-20T00:00:00+0000'") { edges { node { id price sku barcode inventoryQuantity inventoryItem { id } product { id title handle status createdAt publishedAt updatedAt } updatedAt } cursor } pageInfo { hasNextPage } } }
- {
- productVariants(first: 10, query: "updated_at:>'2023-02-20T00:00:00+0000'") {
- edges {
- node {
- id
- price
- sku
- barcode
- inventoryQuantity
- inventoryItem {
- id
- }
- product {
- id
- title
- handle
- status
- createdAt
- publishedAt
- updatedAt
- }
- updatedAt
- }
- cursor
- }
- pageInfo {
- hasNextPage
- }
- }
- }
The Deluge Code
So this is a Zoho Creator function that I'm going to call API.fn_ShopifyQuery_UpdateAllProducts() and it will be added to a nightly schedule. It's aim is to recover all the modified records of a day and ensure that the data in Zoho is the same as on the Shopify website. Included in this is the feature to export all the data as a CSV and to email it to myself:
copyraw
void API.fn_ShopifyQuery_UpdateAllProducts() { // init v_CountTotal = 0; v_CountUpdated = 0; v_PerPage = 10; b_HasNextPage = true; v_Cursor = ""; l_CsvFileRows = List(); // // start preparing CSV file to email for debugging purposes v_ReportCSV = "Ref,Shopify SKU,Shopify Title,Shopify Product ID,Shopify Variant ID,Shopify Inventory ID,Shopify Price,Shopify Quantity,Shopify Status,Shopify Barcode,Created on Shopify,Modified on Shopify,Published on Shopify"; l_CsvFileRows = List(); l_CsvFileRows.add(v_ReportCSV); // // app specific v_ShopID ="joels-awesome-shop"; v_ShopifyApiVersion = "2022-01"; v_AccessToken = "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2222"; v_Endpoint = "https://" + v_ShopID + "/admin/api/" + v_ShopifyApiVersion.toString() + "/graphql.json"; // // set header parameters m_Header = Map(); m_Header.put("Content-Type","application/json"); m_Header.put("X-Shopify-Access-Token",v_AccessToken); // // create a list of pages (specifies the number of pages to try) l_Pages = {1,2,3}; for each v_Page in l_Pages { if(b_HasNextPage) { // // query using GraphQL v_UpdatedSince = zoho.currentdate.subDay(1).toString("yyyy-MM-dd'T'00:00:00+0000"); if(v_Page == 1) { v_GraphQl = "{ productVariants(first: " + v_PerPage + ", query: \"updated_at:>'" + v_UpdatedSince + "'\") { edges { node { id price sku barcode inventoryQuantity inventoryItem { id } product { id title handle status createdAt publishedAt updatedAt } updatedAt } cursor} pageInfo { hasNextPage } } }"; } else { v_GraphQl = "{ productVariants(first: " + v_PerPage + ", after: \"" + v_Cursor + "\", query: \"updated_at:>'" + v_UpdatedSince + "'\") { edges { node { id price sku barcode inventoryQuantity inventoryItem { id } product { id title handle status createdAt publishedAt updatedAt } updatedAt } cursor} pageInfo { hasNextPage } } }"; } //info v_GraphQl; // // query using GraphQL m_GraphQl = Map(); m_GraphQl.put("query",v_GraphQl); // // Let's test with this GraphQL query r_GetProductVariants = invokeurl [ url :v_Endpoint type :POST parameters:m_GraphQl.toString() headers:m_Header ]; //info r_GetProductVariants; // // parse the response if(r_GetProductVariants.get("data") != null) { if(r_GetProductVariants.get("data").get("productVariants") != null) { if(r_GetProductVariants.get("data").get("productVariants").get("edges") != null) { l_Edges = r_GetProductVariants.get("data").get("productVariants").get("edges"); for each r_Node in l_Edges { v_CountTotal = v_CountTotal + 1; // // parse node m_Node = r_Node.get("node"); // // evaluate v_VariantID = m_Node.get("id").getSuffix("ProductVariant/"); v_VariantSKU = m_Node.get("sku"); v_VariantBarcode = m_Node.get("barcode"); v_VariantPrice = m_Node.get("price"); v_VariantQty = m_Node.get("inventoryQuantity"); v_InventoryID = m_Node.get("inventoryItem").get("id").getSuffix("InventoryItem/"); v_ProductID = m_Node.get("product").get("id").getSuffix("Product/"); v_ProductTitle = m_Node.get("product").get("title"); v_ProductHandle = m_Node.get("product").get("handle"); v_ProductStatus = m_Node.get("product").get("status"); v_ProductCreated = m_Node.get("product").get("createdAt"); v_ProductPublished = m_Node.get("product").get("publishedAt"); v_ProductModified = m_Node.get("product").get("updatedAt"); v_VariantModified = m_Node.get("updatedAt"); // // transforms if(v_ProductTitle.contains(",")) { v_ProductTitle = "\"" + v_ProductTitle + "\""; } if(v_ProductHandle.contains(",")) { v_ProductHandle = "\"" + v_ProductHandle + "\""; } v_Shopify_Created = v_ProductCreated.getPrefix("Z").replaceAll("T"," "); v_Shopify_PublishedStr = if(!isNull(v_ProductPublished),v_ProductPublished,v_Shopify_Created); v_Shopify_Published = v_Shopify_PublishedStr.getPrefix("Z").replaceAll("T"," "); v_Shopify_UpdatedStr = if(!isNull(v_ProductModified),v_ProductModified,v_Shopify_Created); v_Shopify_Updated = v_Shopify_UpdatedStr.getPrefix("Z").replaceAll("T"," "); // adding an apostrophe/single-quote to ID values so MS Excel doesn't round them up v_ProductIDStr = "\"'" + v_ProductID + "\""; v_VariantIDStr = "\"'" + v_VariantID + "\""; v_InventoryIDStr = "\"'" + v_InventoryID + "\""; // // generate CSV row for monitoring purposes l_CsvFileRow = List(); l_CsvFileRow.add(v_CountTotal); l_CsvFileRow.add(v_VariantSKU); l_CsvFileRow.add(v_ProductTitle); l_CsvFileRow.add(v_ProductIDStr); l_CsvFileRow.add(v_VariantIDStr); l_CsvFileRow.add(v_InventoryIDStr); l_CsvFileRow.add(v_VariantPrice); l_CsvFileRow.add(v_VariantQty); l_CsvFileRow.add(v_ProductStatus); l_CsvFileRow.add(v_VariantBarcode); l_CsvFileRow.add(v_Shopify_Created); l_CsvFileRow.add(v_Shopify_Updated); l_CsvFileRow.add(v_Shopify_Published); v_CsvRow = l_CsvFileRow.toString(); l_CsvFileRows.add(v_CsvRow); // // note the cursor so that we resume from the last cursor v_Cursor = r_Node.get("cursor"); // // output to console for debugging purposes info "Page #" + v_Page + "::" + v_CountTotal + "::" + v_VariantSKU + "::" + v_Cursor; } // // capture whether to continue the loop or not b_HasNextPage = r_GetProductVariants.get("data").get("productVariants").get("pageInfo").get("hasNextPage"); } } } } } // // generate a CSV list for monitoring purposes v_CSVFilename = "replay-golf-export-active-page-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm-ss") + ".csv"; l_CsvFileRows.add("-----------------------------" + v_CSVFilename); v_CSVFile = l_CsvFileRows.toString("\n").toFile(v_CSVFilename); // // send via Email v_CountRows = l_CsvFileRows.size() - 2; v_Subject = "Shopify Products Export & Comparison"; v_Message = "Please find attached a log of <b>" + v_CountRows + "</b> Product(s) from Shopify that had been updated on " + zoho.currenttime.subDay(1).toString("EEEE, dd-MMM-yyyy") + "<br /><br />"; v_Message = v_Message + "There were " + v_CountUpdated + " record(s) updated.<br /><br />Kind Regards,<br /><br />The Team"; sendmail [ from :zoho.loginuserid to:"Joel Lipman <This email address is being protected from spambots. You need JavaScript enabled to view it.>" subject :v_Subject message :v_Message Attachments :file:v_CSVFile ] }
- void API.fn_ShopifyQuery_UpdateAllProducts()
- {
- // init
- v_CountTotal = 0;
- v_CountUpdated = 0;
- v_PerPage = 10;
- b_HasNextPage = true;
- v_Cursor = "";
- l_CsvFileRows = List();
- //
- // start preparing CSV file to email for debugging purposes
- v_ReportCSV = "Ref,Shopify SKU,Shopify Title,Shopify Product ID,Shopify Variant ID,Shopify Inventory ID,Shopify Price,Shopify Quantity,Shopify Status,Shopify Barcode,Created on Shopify,Modified on Shopify,Published on Shopify";
- l_CsvFileRows = List();
- l_CsvFileRows.add(v_ReportCSV);
- //
- // app specific
- v_ShopID ="joels-awesome-shop";
- v_ShopifyApiVersion = "2022-01";
- v_AccessToken = "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2222";
- v_Endpoint = "https://" + v_ShopID + "/admin/api/" + v_ShopifyApiVersion.toString() + "/graphql.json";
- //
- // set header parameters
- m_Header = Map();
- m_Header.put("Content-Type","application/json");
- m_Header.put("X-Shopify-Access-Token",v_AccessToken);
- //
- // create a list of pages (specifies the number of pages to try)
- l_Pages = {1,2,3};
- for each v_Page in l_Pages
- {
- if(b_HasNextPage)
- {
- //
- // query using GraphQL
- v_UpdatedSince = zoho.currentdate.subDay(1).toString("yyyy-MM-dd'T'00:00:00+0000");
- if(v_Page == 1)
- {
- v_GraphQl = "{ productVariants(first: " + v_PerPage + ", query: \"updated_at:>'" + v_UpdatedSince + "'\") { edges { node { id price sku barcode inventoryQuantity inventoryItem { id } product { id title handle status createdAt publishedAt updatedAt } updatedAt } cursor} pageInfo { hasNextPage } } }";
- }
- else
- {
- v_GraphQl = "{ productVariants(first: " + v_PerPage + ", after: \"" + v_Cursor + "\", query: \"updated_at:>'" + v_UpdatedSince + "'\") { edges { node { id price sku barcode inventoryQuantity inventoryItem { id } product { id title handle status createdAt publishedAt updatedAt } updatedAt } cursor} pageInfo { hasNextPage } } }";
- }
- //info v_GraphQl;
- //
- // query using GraphQL
- m_GraphQl = Map();
- m_GraphQl.put("query",v_GraphQl);
- //
- // Let's test with this GraphQL query
- r_GetProductVariants = invokeUrl
- [
- url :v_Endpoint
- type :POST
- parameters:m_GraphQl.toString()
- headers:m_Header
- ];
- //info r_GetProductVariants;
- //
- // parse the response
- if(r_GetProductVariants.get("data") != null)
- {
- if(r_GetProductVariants.get("data").get("productVariants") != null)
- {
- if(r_GetProductVariants.get("data").get("productVariants").get("edges") != null)
- {
- l_Edges = r_GetProductVariants.get("data").get("productVariants").get("edges");
- for each r_Node in l_Edges
- {
- v_CountTotal = v_CountTotal + 1;
- //
- // parse node
- m_Node = r_Node.get("node");
- //
- // evaluate
- v_VariantID = m_Node.get("id").getSuffix("ProductVariant/");
- v_VariantSKU = m_Node.get("sku");
- v_VariantBarcode = m_Node.get("barcode");
- v_VariantPrice = m_Node.get("price");
- v_VariantQty = m_Node.get("inventoryQuantity");
- v_InventoryID = m_Node.get("inventoryItem").get("id").getSuffix("InventoryItem/");
- v_ProductID = m_Node.get("product").get("id").getSuffix("Product/");
- v_ProductTitle = m_Node.get("product").get("title");
- v_ProductHandle = m_Node.get("product").get("handle");
- v_ProductStatus = m_Node.get("product").get("status");
- v_ProductCreated = m_Node.get("product").get("createdAt");
- v_ProductPublished = m_Node.get("product").get("publishedAt");
- v_ProductModified = m_Node.get("product").get("updatedAt");
- v_VariantModified = m_Node.get("updatedAt");
- //
- // transforms
- if(v_ProductTitle.contains(","))
- {
- v_ProductTitle = "\"" + v_ProductTitle + "\"";
- }
- if(v_ProductHandle.contains(","))
- {
- v_ProductHandle = "\"" + v_ProductHandle + "\"";
- }
- v_Shopify_Created = v_ProductCreated.getPrefix("Z").replaceAll("T"," ");
- v_Shopify_PublishedStr = if(!isNull(v_ProductPublished),v_ProductPublished,v_Shopify_Created);
- v_Shopify_Published = v_Shopify_PublishedStr.getPrefix("Z").replaceAll("T"," ");
- v_Shopify_UpdatedStr = if(!isNull(v_ProductModified),v_ProductModified,v_Shopify_Created);
- v_Shopify_Updated = v_Shopify_UpdatedStr.getPrefix("Z").replaceAll("T"," ");
- // adding an apostrophe/single-quote to ID values so MS Excel doesn't round them up
- v_ProductIDStr = "\"'" + v_ProductID + "\"";
- v_VariantIDStr = "\"'" + v_VariantID + "\"";
- v_InventoryIDStr = "\"'" + v_InventoryID + "\"";
- //
- // generate CSV row for monitoring purposes
- l_CsvFileRow = List();
- l_CsvFileRow.add(v_CountTotal);
- l_CsvFileRow.add(v_VariantSKU);
- l_CsvFileRow.add(v_ProductTitle);
- l_CsvFileRow.add(v_ProductIDStr);
- l_CsvFileRow.add(v_VariantIDStr);
- l_CsvFileRow.add(v_InventoryIDStr);
- l_CsvFileRow.add(v_VariantPrice);
- l_CsvFileRow.add(v_VariantQty);
- l_CsvFileRow.add(v_ProductStatus);
- l_CsvFileRow.add(v_VariantBarcode);
- l_CsvFileRow.add(v_Shopify_Created);
- l_CsvFileRow.add(v_Shopify_Updated);
- l_CsvFileRow.add(v_Shopify_Published);
- v_CsvRow = l_CsvFileRow.toString();
- l_CsvFileRows.add(v_CsvRow);
- //
- // note the cursor so that we resume from the last cursor
- v_Cursor = r_Node.get("cursor");
- //
- // output to console for debugging purposes
- info "Page #" + v_Page + "::" + v_CountTotal + "::" + v_VariantSKU + "::" + v_Cursor;
- }
- //
- // capture whether to continue the loop or not
- b_HasNextPage = r_GetProductVariants.get("data").get("productVariants").get("pageInfo").get("hasNextPage");
- }
- }
- }
- }
- }
- //
- // generate a CSV list for monitoring purposes
- v_CSVFilename = "replay-golf-export-active-page-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm-ss") + ".csv";
- l_CsvFileRows.add("-----------------------------" + v_CSVFilename);
- v_CSVFile = l_CsvFileRows.toString("\n").toFile(v_CSVFilename);
- //
- // send via Email
- v_CountRows = l_CsvFileRows.size() - 2;
- v_Subject = "Shopify Products Export & Comparison";
- v_Message = "Please find attached a log of <b>" + v_CountRows + "</b> Product(s) from Shopify that had been updated on " + zoho.currenttime.subDay(1).toString("EEEE, dd-MMM-yyyy") + "<br /><br />";
- v_Message = v_Message + "There were " + v_CountUpdated + " record(s) updated.<br /><br />Kind Regards,<br /><br />The Team";
- sendmail
- [
- from :zoho.loginuserid
- to:"Joel Lipman <This email address is being protected from spambots. You need JavaScript enabled to view it.>"
- subject :v_Subject
- message :v_Message
- Attachments :file:v_CSVFile
- ]
- }
Error(s):
{"errors":[{"message":"Field 'endCursor' doesn't exist on type 'PageInfo'","locations":[{"line":1,"column":254}],"path":["query","productVariants","pageInfo","endCursor"],"extensions":{"code":"undefinedField","typeName":"PageInfo","fieldName":"endCursor"}}]}
I only resolved this by not using "endCursor" in my GraphQL and instead looped through the nodes to get the last cursor. Not sure if this meant any data was missing. Perhaps by forcing the sort criteria to be by order of cursor alphabetically; but we were happy with the results the above code did anyway so I haven't tried this.
Source(s):
Category: Zoho :: Article: 833
Add comment