Zoho Deluge: Shopify API: Get all active products with GraphQL and Pagination

: Joel Lipman; Zoho; 20 February 2023; Hits: 528

What?
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:

copy raw

{
  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:

copy raw

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 " + v_CountRows + " Product(s) from Shopify that had been updated on " + zoho.currenttime.subDay(1).toString("EEEE, dd-MMM-yyyy") + " ";
v_Message = v_Message + "There were " + v_CountUpdated + " record(s) updated. Kind Regards, 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):