A quick article on retrieving all the products from a WooCommerce instance on a client's Wordpress website.
Why?
A client of ours wants the information entered against products in their WooCommerce to display on their estimates in Zoho Books; such as product image, product name, product description.
Granted that in some cases, the design should go in the other direction, as in enter the products in Zoho Inventory and this feeds the website. More than often, however, this request comes in when the customer has already been setting their Zoho environment up and the Wordpress site has the more information.
How?
So first, this article shows you how to setup an API connection, and then we'll include the code to count the total number of products, and then finallly show the code to loop through all the pages to retrieve every product from the WooCommerce system.
API connection in WooCommerce
So we need to get an API connection to ensure we get all the products. This involves getting a consumer_key and a consumer_secret.
- Login to the administrator's section of the Wordpress website (mysite.com/wp-admin)
- Expand WooCommerce in the left sidebar
- Go to Settings (in the menu/sidebar) > Advanced (tab) > REST API (link under the tabs)
- Click on the Add key button
- Give it a description, assign a user that it will run as (preferably of the admin or account that will never leave the business), and permissions can be "Read".
- Click on the "Generate API Key" button
- Note the consumer_key and consumer_secret somewhere (these aren't displayed to you in future - so this is your one chance, or else you need to generate another key)
Zoho Deluge
Here's the code to retrieve all the product categories. We need this to get the count of all products (if there's another way, I'd love to hear it). We don't have more than 100 product categories so we'll just to page 1 but specifying 100 per page as the default is only 10.
// // API keys (these are made up so enter your own in these 2 lines below v_MyWordPressSiteUrl = "https://mysite.com"; v_ConsumerKey = "ck_aaaabbbbccccddddeeeeffff1111222233334444"; v_ConsumerSecret = "cs_555566667777888899990000aaaabbbbccccdddd"; // // connection parameters l_ConnectionParams = List(); l_ConnectionParams.add("consumer_key=" + v_ConsumerKey); l_ConnectionParams.add("consumer_secret=" + v_ConsumerSecret); // // get product count by using categories v_EndpointCategories = v_MyWordPressSiteUrl + "/wp-json/wc/v3/products/categories"; l_PaginationParams = List(); l_PaginationParams.add("page=1"); l_PaginationParams.add("per_page=100"); l_PaginationParams.add("order_by=id"); // // we have no more than 100 categories so let's start counting r_Categories = invokeurl [ url: v_EndpointCategories + "?" + l_ConnectionParams.toString("&") + "&" + l_PaginationParams.toString("&") type: GET ]; v_TotalProducts = 0; v_TotalCategories = r_Categories.toJSONList().size(); for each m_Category in r_Categories.toJSONList() { if(!isNull(m_Category.get("count"))) { v_TotalProducts = v_TotalProducts + m_Category.get("count"); } } info v_TotalProducts + " in " + v_TotalCategories; // // if categories >= 100 then should loop this to get all the categories
- //
- // API keys (these are made up so enter your own in these 2 lines below
- v_MyWordPressSiteUrl = "https://mysite.com";
- v_ConsumerKey = "ck_aaaabbbbccccddddeeeeffff1111222233334444";
- v_ConsumerSecret = "cs_555566667777888899990000aaaabbbbccccdddd";
- //
- // connection parameters
- l_ConnectionParams = List();
- l_ConnectionParams.add("consumer_key=" + v_ConsumerKey);
- l_ConnectionParams.add("consumer_secret=" + v_ConsumerSecret);
- //
- // get product count by using categories
- v_EndpointCategories = v_MyWordPressSiteUrl + "/wp-json/wc/v3/products/categories";
- l_PaginationParams = List();
- l_PaginationParams.add("page=1");
- l_PaginationParams.add("per_page=100");
- l_PaginationParams.add("order_by=id");
- //
- // we have no more than 100 categories so let's start counting
- r_Categories = invokeUrl
- [
- url: v_EndpointCategories + "?" + l_ConnectionParams.toString("&") + "&" + l_PaginationParams.toString("&")
- type: GET
- ];
- v_TotalProducts = 0;
- v_TotalCategories = r_Categories.toJSONList().size();
- for each m_Category in r_Categories.toJSONList()
- {
- if(!isNull(m_Category.get("count")))
- {
- v_TotalProducts = v_TotalProducts + m_Category.get("count");
- }
- }
- info v_TotalProducts + " in " + v_TotalCategories;
- //
- // if categories >= 100 then should loop this to get all the categories
Code to get all products
Here we need to loop through all the available pages, so including the above plus some calculation to work out how many times to loop should retrieve every thing:
//
// initialize
l_AllProducts = List();
v_TotalCategories = 0;
v_TotalProducts = 0;
v_Page = 1;
// do not set this to zero!!!
v_PerPage = 100;
//
// API keys
v_MyWordPressSiteUrl = "https://mysite.com";
v_ConsumerKey = "ck_aaaabbbbccccddddeeeeffff1111222233334444";
v_ConsumerSecret = "cs_555566667777888899990000aaaabbbbccccdddd";
//
// connection parameters
l_ConnectionParams = List();
l_ConnectionParams.add("consumer_key=" + v_ConsumerKey);
l_ConnectionParams.add("consumer_secret=" + v_ConsumerSecret);
//
// get product count by using categories
v_EndpointCategories = v_MyWordPressSiteUrl + "/wp-json/wc/v3/products/categories";
l_PaginationParams = List();
l_PaginationParams.add("page=" + v_Page);
l_PaginationParams.add("per_page=" + v_PerPage);
l_PaginationParams.add("order_by=id");
//
// we have no more than 100 categories so let's start counting
r_Categories = invokeurl
[
url :v_EndpointCategories + "?" + l_ConnectionParams.toString("&") + "&" + l_PaginationParams.toString("&")
type :GET
];
if(r_Categories.toString().contains("count"))
{
v_TotalCategories = r_Categories.toJSONList().size();
}
for each m_Category in r_Categories.toJSONList()
{
if(!isNull(m_Category.get("count")))
{
v_TotalProducts = v_TotalProducts + m_Category.get("count");
}
}
//
// if categories >= 100 then should loop this to get all the categories
// let's calculate how many pages we need
v_MaxPages = ceil(v_TotalProducts / v_PerPage);
//
// generate a loop list
l_ProductPages = " ".leftpad(v_MaxPages - 1).replaceAll(" ",",").toList();
for each v_PageIndex in l_ProductPages
{
//
// products
v_EndpointProducts = v_MyWordPressSiteUrl + "/wp-json/wc/v3/products";
//
l_PaginationParams = List();
l_PaginationParams.add("page=" + v_Page);
// maximum number of products per page
l_PaginationParams.add("per_page=" + v_PerPage);
// missing products if using default order by as date
l_PaginationParams.add("order=asc");
l_PaginationParams.add("order_by=id");
//
r_AllProducts = invokeurl
[
url :v_EndpointProducts + "?" + l_ConnectionParams.toString("&") + "&" + l_PaginationParams.toString("&")
type :GET
];
l_ThisList = r_AllProducts.toJSONList();
//
// add this list to our existing list
l_AllProducts.addAll(l_ThisList);
//
// stop the loop if there weren't any more products
if(l_ThisList.size() < 1)
{
break;
}
//
// increment page
v_Page = v_Page + 1;
}
//
// generate a file
f_AllProducts = l_AllProducts.toFile("all_products-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm") + ".json");
//
// send this to us via email
sendmail
[
from :zoho.adminuserid
to :"This email address is being protected from spambots. You need JavaScript enabled to view it."
subject :"All Products from " + v_MyWordPressSiteUrl + " as of " + zoho.currenttime.toString("yyyy-MM-dd HH:mm:ssXXX")
message :"Please see the attached file"
Attachments :file:f_AllProducts
]
//
return v_TotalProducts + " product(s) in " + v_TotalCategories + " categories across " + v_Page + " page(s).";
- //
- // initialize
- l_AllProducts = List();
- v_TotalCategories = 0;
- v_TotalProducts = 0;
- v_Page = 1;
- // do not set this to zero!!!
- v_PerPage = 100;
- //
- // API keys
- v_MyWordPressSiteUrl = "https://mysite.com";
- v_ConsumerKey = "ck_aaaabbbbccccddddeeeeffff1111222233334444";
- v_ConsumerSecret = "cs_555566667777888899990000aaaabbbbccccdddd";
- //
- // connection parameters
- l_ConnectionParams = List();
- l_ConnectionParams.add("consumer_key=" + v_ConsumerKey);
- l_ConnectionParams.add("consumer_secret=" + v_ConsumerSecret);
- //
- // get product count by using categories
- v_EndpointCategories = v_MyWordPressSiteUrl + "/wp-json/wc/v3/products/categories";
- l_PaginationParams = List();
- l_PaginationParams.add("page=" + v_Page);
- l_PaginationParams.add("per_page=" + v_PerPage);
- l_PaginationParams.add("order_by=id");
- //
- // we have no more than 100 categories so let's start counting
- r_Categories = invokeUrl
- [
- url :v_EndpointCategories + "?" + l_ConnectionParams.toString("&") + "&" + l_PaginationParams.toString("&")
- type :GET
- ];
- if(r_Categories.toString().contains("count"))
- {
- v_TotalCategories = r_Categories.toJSONList().size();
- }
- for each m_Category in r_Categories.toJSONList()
- {
- if(!isNull(m_Category.get("count")))
- {
- v_TotalProducts = v_TotalProducts + m_Category.get("count");
- }
- }
- //
- // if categories >= 100 then should loop this to get all the categories
- // let's calculate how many pages we need
- v_MaxPages = ceil(v_TotalProducts / v_PerPage);
- //
- // generate a loop list
- l_ProductPages = " ".leftpad(v_MaxPages - 1).replaceAll(" ",",").toList();
- for each v_PageIndex in l_ProductPages
- {
- //
- // products
- v_EndpointProducts = v_MyWordPressSiteUrl + "/wp-json/wc/v3/products";
- //
- l_PaginationParams = List();
- l_PaginationParams.add("page=" + v_Page);
- // maximum number of products per page
- l_PaginationParams.add("per_page=" + v_PerPage);
- // missing products if using default order by as date
- l_PaginationParams.add("order=asc");
- l_PaginationParams.add("order_by=id");
- //
- r_AllProducts = invokeUrl
- [
- url :v_EndpointProducts + "?" + l_ConnectionParams.toString("&") + "&" + l_PaginationParams.toString("&")
- type :GET
- ];
- l_ThisList = r_AllProducts.toJSONList();
- //
- // add this list to our existing list
- l_AllProducts.addAll(l_ThisList);
- //
- // stop the loop if there weren't any more products
- if(l_ThisList.size() < 1)
- {
- break;
- }
- //
- // increment page
- v_Page = v_Page + 1;
- }
- //
- // generate a file
- f_AllProducts = l_AllProducts.toFile("all_products-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm") + ".json");
- //
- // send this to us via email
- sendmail
- [
- from :zoho.adminuserid
- to :"This email address is being protected from spambots. You need JavaScript enabled to view it."
- subject :"All Products from " + v_MyWordPressSiteUrl + " as of " + zoho.currenttime.toString("yyyy-MM-dd HH:mm:ssXXX")
- message :"Please see the attached file"
- Attachments :file:f_AllProducts
- ]
- //
- return v_TotalProducts + " product(s) in " + v_TotalCategories + " categories across " + v_Page + " page(s).";
Additional:
- Total product count may differ from the actual product count as products can belong to multiple categories. Consider just using the WooCommerce interface to check the total number of products (wp-admin > Products > All Products).
Source(s):