Another one of these articles talking about getting all the active product listings from eBay for a specific client.
Why?
Our use-case scenario here is that we want to get an inventory level or stock check of all the products that are currently listed in a client's eBay store; we want to get the stock level and then use this data to synchronize with data held in Zoho Inventory...
How?
So we're going to use a ZohoCRM function to get this file which we can run on demand. Why not in Zoho Inventory or any other Zoho app? Because we like to challenge ourselves...
Once again, I'm not going into how to generate an access token / connection from Zoho to eBay, that is documented in my article: Zoho Creator: Push to eBay Listings
The megafunction
So I'm going to call this function "Fn - eBay - Get Active Products" and give it the internal name as "fn_GetEbayActiveProducts" without any arguments. Something to note in the below is that it asks you to set an estimated number of products that are in your listing, it will then generate the number of pages it thinks it has to loop, so try and give a number higher than the actual number of products you have in your shop, it will stop when it's reached the end of the list as per eBay and not what you might have thought it was.
/*
Function: fn_GetEbayActiveProducts()
Purpose: Queries eBay for all active listings
Date Created: 2023-03-01 (Joel Lipman)
- Initial release
Date Modified: ????
- ???
More Info:
- API Explorer Test Tool: https://developer.ebay.com/DevZone/build-test/test-tool/default.aspx?index=0&env=production&api=trading
- GetMyeBaySelling Documentation: https://developer.ebay.com/devzone/xml/docs/reference/ebay/getmyebayselling.html
*/
//
// declare
v_Page = 1;
v_PerPage = 100;
l_Pages = List();
//
// specify page to start from
v_StartingPageIndex = 1;
//
// specify the maximum number of products you think you have on eBay
// the actual number will be less than this but this is for pagination estimates
v_MaximumProducts = 1500;
//
// now let's generate the page list to have all the available pages
v_TotalNumberOfPages = ceil(v_MaximumProducts / v_PerPage);
l_AddPages = leftpad(" ", v_TotalNumberOfPages).replaceAll(" ", ",").toList();
for each index v_Increment in l_AddPages
{
l_Pages.add(v_StartingPageIndex + v_Increment);
}
v_TotalNumberOfPages = l_Pages.size();
//
// declare variables to store order details in a JSON or ZohoDeluge Map format
m_Response = Map();
l_JsonProducts = List();
//
// start preparing CSV file to email for export
v_ReportCSV = "ItemID,ItemName,Price,CurrencyCode,QuantityAvailable,SKU,DateCreated";
l_CsvFileRows = List();
l_CsvFileRows.add(v_ReportCSV);
//
// get access token
v_AccessToken = standalone.fn_eBay_GetAccessToken();
//
v_TradingAPIVersion = 967;
v_Endpoint = "https://api.ebay.com/ws/api.dll";
//
// build header
m_Headers = Map();
m_Headers.put("X-EBAY-API-SITEID",3);
m_Headers.put("X-EBAY-API-COMPATIBILITY-LEVEL",v_TradingAPIVersion);
v_ApiCall = "GetMyeBaySelling";
m_Headers.put("X-EBAY-API-CALL-NAME",v_ApiCall);
m_Headers.put("X-EBAY-API-IAF-TOKEN",v_AccessToken);
//
// build params
m_Params = Map();
m_Params.put("WarningLevel","High");
m_Params.put("ErrorLanguage","en_GB");
m_Params.put("DetailLevel","ReturnAll");
//
// specify only the active list
m_ActiveList = Map();
m_ActiveList.put("Include",true);
m_ActiveList.put("ListingType","FixedPriceItem");
m_ActiveList.put("Sort","ItemID");
//
// exclude other lists
m_Exclude = Map();
m_Exclude.put("Include", false);
m_Params.put("DeletedFromSoldList",m_Exclude);
m_Params.put("DeletedFromUnsoldList",m_Exclude);
m_Params.put("ScheduledList",m_Exclude);
m_Params.put("SellingSummary",m_Exclude);
m_Params.put("SoldList",m_Exclude);
m_Params.put("UnsoldList",m_Exclude);
//
// select which fields to return
l_OutputFields = List();
l_OutputFields.add("ItemID");
l_OutputFields.add("Title");
l_OutputFields.add("SKU");
l_OutputFields.add("QuantityAvailable");
l_OutputFields.add("SellingStatus");
l_OutputFields.add("StartTime");
l_OutputFields.add("TotalNumberOfPages");
l_OutputFields.add("TotalNumberOfEntries");
m_Params.put("OutputSelector",l_OutputFields);
//
// now lets loop through a dynamic page list
for each v_Page in l_Pages
{
//
// specify which page
m_Pagination = Map();
m_Pagination.put("PageNumber",v_Page);
m_Pagination.put("EntriesPerPage",v_PerPage);
m_ActiveList.put("Pagination",m_Pagination);
//
m_Params.put("ActiveList",m_ActiveList);
//
// convert to xml and replace root nodes
x_Params = m_Params.toXML();
x_Params = x_Params.toString().replaceFirst("<root>","<?xml version=\"1.0\" encoding=\"utf-8\"?><" + v_ApiCall + "Request xmlns=\"urn:ebay:apis:eBLBaseComponents\">");
x_Params = x_Params.toString().replaceFirst("</root>","</" + v_ApiCall + "Request>");
// info "Request Sent to eBay:";
// info x_Params;
//
// send the request XML as a string
x_ResponseBody = invokeurl
[
url :v_Endpoint
type :POST
parameters:x_Params
headers:m_Headers
];
if(v_Page==1)
{
//
// get page results
x_PaginationResult = x_ResponseBody.subString(x_ResponseBody.indexOf("<PaginationResult"),x_ResponseBody.lastIndexOf("</PaginationResult") + 19);
v_TotalNumberOfProducts = x_PaginationResult.executeXPath("/PaginationResult/TotalNumberOfEntries/text()").toLong();
//
// determine total number of pages required
v_TotalNumberOfPages = ceil(v_TotalNumberOfProducts / v_PerPage);
info "Total Product(s): " + v_TotalNumberOfProducts;
info "Total Page(s): " + v_TotalNumberOfPages;
}
//
// loop through products/items from response
v_ItemNodePart = "Item";
v_ItemNodeName = v_ItemNodePart + "Array";
x_ItemNode = x_ResponseBody.subString(x_ResponseBody.indexOf("<" + v_ItemNodeName),x_ResponseBody.lastIndexOf(v_ItemNodeName) + v_ItemNodeName.length() + 1);
l_Items = x_ItemNode.executeXPath("/" + v_ItemNodeName + "/" + v_ItemNodePart).toXmlList();
for each x_Item in l_Items
{
//
// build JSON row
m_Item = Map();
m_Item.put("ItemID",x_Item.executeXPath("/" + v_ItemNodePart + "/ItemID/text()"));
m_Item.put("ItemName",x_Item.executeXPath("/" + v_ItemNodePart + "/Title/text()"));
m_Item.put("Price",x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/text()").toDecimal());
m_Item.put("CurrencyCode",x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/@currencyID").executeXPath("/currencyID/text()"));
m_Item.put("QuantityAvailable",x_Item.executeXPath("/" + v_ItemNodePart + "/QuantityAvailable/text()").toDecimal());
m_Item.put("SKU",x_Item.executeXPath("/" + v_ItemNodePart + "/SKU/text()"));
//
v_Item_DateCreated = x_Item.executeXPath("/" + v_ItemNodePart + "/ListingDetails/StartTime/text()");
v_Item_DateCreated = if(!isnull(v_Item_DateCreated),v_Item_DateCreated.getPrefix(".").replaceFirst("T"," ",true).toTime(),zoho.currenttime).toString("yyyy-MM-dd HH:mm:ssZ");
m_Item.put("DateCreated",v_Item_DateCreated);
//
l_JsonProducts.add(m_Item);
//
// generate CSV row for monitoring purposes
l_CsvFileRow = List();
l_CsvFileRow.add("\"'" + x_Item.executeXPath("/" + v_ItemNodePart + "/ItemID/text()") + "\"");
l_CsvFileRow.add("\"" + x_Item.executeXPath("/" + v_ItemNodePart + "/Title/text()") + "\"");
l_CsvFileRow.add(x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/text()"));
l_CsvFileRow.add(x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/@currencyID").executeXPath("/currencyID/text()"));
l_CsvFileRow.add(x_Item.executeXPath("/" + v_ItemNodePart + "/QuantityAvailable/text()"));
l_CsvFileRow.add("\"" + x_Item.executeXPath("/" + v_ItemNodePart + "/SKU/text()") + "\"");
l_CsvFileRow.add(v_Item_DateCreated);
//
v_CsvRow = l_CsvFileRow.toString();
l_CsvFileRows.add(v_CsvRow);
}
if(v_Page>=v_TotalNumberOfPages)
{
break;
}
}
//
m_Response.put("items",l_JsonProducts);
//info m_Response;
//
// lets send the CSV and JSON by email
if(l_JsonProducts.size()>0)
{
//
// generate a CSV list for monitoring purposes
v_CSVFilename = "active-ebay-products-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm-ss") + ".csv";
l_CsvFileRows.add("-----------------------------" + v_CSVFilename);
//
// usually \n works on some systems but this one works on systems that \n doesn't
f_CSVFile = l_CsvFileRows.toString(zoho.encryption.urlDecode("%0A")).toFile(v_CSVFilename);
//
// just adding these in case
v_JsonFilename = "active-ebay-products-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm-ss") + ".json";
f_JsonFile = m_Response.toString().toFile(v_JsonFilename);
//
// send via Email
v_CountRows = l_CsvFileRows.size() - 2;
v_Subject = l_JsonProducts.size() + " eBay Products Export & Comparison";
v_Message = "Hi there!<br /><br />Please find attached a log of <b>" + l_JsonProducts.size() + "</b> Product(s) from eBay that was exported on <b>" + zoho.currenttime.toString("EEEE, dd-MMM-yyyy") + "</b><br /><br />This is an automated email. Please do not reply to it.<br /><br />Kind Regards,<br /><br />The Team";
sendmail
[
from :zoho.adminuserid
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:f_CSVFile,file:f_JsonFile
]
}
//
return "Exported " + l_JsonProducts.size() + " product(s)";
- /*
- Function: fn_GetEbayActiveProducts()
- Purpose: Queries eBay for all active listings
- Date Created: 2023-03-01 (Joel Lipman)
- - Initial release
- Date Modified: ????
- - ???
- More Info:
- - API Explorer Test Tool: https://developer.ebay.com/DevZone/build-test/test-tool/default.aspx?index=0&env=production&api=trading
- - GetMyeBaySelling Documentation: https://developer.ebay.com/devzone/xml/docs/reference/ebay/getmyebayselling.html
- */
- //
- // declare
- v_Page = 1;
- v_PerPage = 100;
- l_Pages = List();
- //
- // specify page to start from
- v_StartingPageIndex = 1;
- //
- // specify the maximum number of products you think you have on eBay
- // the actual number will be less than this but this is for pagination estimates
- v_MaximumProducts = 1500;
- //
- // now let's generate the page list to have all the available pages
- v_TotalNumberOfPages = ceil(v_MaximumProducts / v_PerPage);
- l_AddPages = leftpad(" ", v_TotalNumberOfPages).replaceAll(" ", ",").toList();
- for each index v_Increment in l_AddPages
- {
- l_Pages.add(v_StartingPageIndex + v_Increment);
- }
- v_TotalNumberOfPages = l_Pages.size();
- //
- // declare variables to store order details in a JSON or ZohoDeluge Map format
- m_Response = Map();
- l_JsonProducts = List();
- //
- // start preparing CSV file to email for export
- v_ReportCSV = "ItemID,ItemName,Price,CurrencyCode,QuantityAvailable,SKU,DateCreated";
- l_CsvFileRows = List();
- l_CsvFileRows.add(v_ReportCSV);
- //
- // get access token
- v_AccessToken = standalone.fn_eBay_GetAccessToken();
- //
- v_TradingAPIVersion = 967;
- v_Endpoint = "https://api.ebay.com/ws/api.dll";
- //
- // build header
- m_Headers = Map();
- m_Headers.put("X-EBAY-API-SITEID",3);
- m_Headers.put("X-EBAY-API-COMPATIBILITY-LEVEL",v_TradingAPIVersion);
- v_ApiCall = "GetMyeBaySelling";
- m_Headers.put("X-EBAY-API-CALL-NAME",v_ApiCall);
- m_Headers.put("X-EBAY-API-IAF-TOKEN",v_AccessToken);
- //
- // build params
- m_Params = Map();
- m_Params.put("WarningLevel","High");
- m_Params.put("ErrorLanguage","en_GB");
- m_Params.put("DetailLevel","ReturnAll");
- //
- // specify only the active list
- m_ActiveList = Map();
- m_ActiveList.put("Include",true);
- m_ActiveList.put("ListingType","FixedPriceItem");
- m_ActiveList.put("Sort","ItemID");
- //
- // exclude other lists
- m_Exclude = Map();
- m_Exclude.put("Include", false);
- m_Params.put("DeletedFromSoldList",m_Exclude);
- m_Params.put("DeletedFromUnsoldList",m_Exclude);
- m_Params.put("ScheduledList",m_Exclude);
- m_Params.put("SellingSummary",m_Exclude);
- m_Params.put("SoldList",m_Exclude);
- m_Params.put("UnsoldList",m_Exclude);
- //
- // select which fields to return
- l_OutputFields = List();
- l_OutputFields.add("ItemID");
- l_OutputFields.add("Title");
- l_OutputFields.add("SKU");
- l_OutputFields.add("QuantityAvailable");
- l_OutputFields.add("SellingStatus");
- l_OutputFields.add("StartTime");
- l_OutputFields.add("TotalNumberOfPages");
- l_OutputFields.add("TotalNumberOfEntries");
- m_Params.put("OutputSelector",l_OutputFields);
- //
- // now lets loop through a dynamic page list
- for each v_Page in l_Pages
- {
- //
- // specify which page
- m_Pagination = Map();
- m_Pagination.put("PageNumber",v_Page);
- m_Pagination.put("EntriesPerPage",v_PerPage);
- m_ActiveList.put("Pagination",m_Pagination);
- //
- m_Params.put("ActiveList",m_ActiveList);
- //
- // convert to xml and replace root nodes
- x_Params = m_Params.toXML();
- x_Params = x_Params.toString().replaceFirst("<root>","<?xml version=\"1.0\" encoding=\"utf-8\"?><" + v_ApiCall + "Request xmlns=\"urn:ebay:apis:eBLBaseComponents\">");
- x_Params = x_Params.toString().replaceFirst("</root>","</" + v_ApiCall + "Request>");
- // info "Request Sent to eBay:";
- // info x_Params;
- //
- // send the request XML as a string
- x_ResponseBody = invokeUrl
- [
- url :v_Endpoint
- type :POST
- parameters:x_Params
- headers:m_Headers
- ];
- if(v_Page==1)
- {
- //
- // get page results
- x_PaginationResult = x_ResponseBody.subString(x_ResponseBody.indexOf("<PaginationResult"),x_ResponseBody.lastIndexOf("</PaginationResult") + 19);
- v_TotalNumberOfProducts = x_PaginationResult.executeXPath("/PaginationResult/TotalNumberOfEntries/text()").toLong();
- //
- // determine total number of pages required
- v_TotalNumberOfPages = ceil(v_TotalNumberOfProducts / v_PerPage);
- info "Total Product(s): " + v_TotalNumberOfProducts;
- info "Total Page(s): " + v_TotalNumberOfPages;
- }
- //
- // loop through products/items from response
- v_ItemNodePart = "Item";
- v_ItemNodeName = v_ItemNodePart + "Array";
- x_ItemNode = x_ResponseBody.subString(x_ResponseBody.indexOf("<" + v_ItemNodeName),x_ResponseBody.lastIndexOf(v_ItemNodeName) + v_ItemNodeName.length() + 1);
- l_Items = x_ItemNode.executeXPath("/" + v_ItemNodeName + "/" + v_ItemNodePart).toXmlList();
- for each x_Item in l_Items
- {
- //
- // build JSON row
- m_Item = Map();
- m_Item.put("ItemID",x_Item.executeXPath("/" + v_ItemNodePart + "/ItemID/text()"));
- m_Item.put("ItemName",x_Item.executeXPath("/" + v_ItemNodePart + "/Title/text()"));
- m_Item.put("Price",x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/text()").toDecimal());
- m_Item.put("CurrencyCode",x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/@currencyID").executeXPath("/currencyID/text()"));
- m_Item.put("QuantityAvailable",x_Item.executeXPath("/" + v_ItemNodePart + "/QuantityAvailable/text()").toDecimal());
- m_Item.put("SKU",x_Item.executeXPath("/" + v_ItemNodePart + "/SKU/text()"));
- //
- v_Item_DateCreated = x_Item.executeXPath("/" + v_ItemNodePart + "/ListingDetails/StartTime/text()");
- v_Item_DateCreated = if(!isnull(v_Item_DateCreated),v_Item_DateCreated.getPrefix(".").replaceFirst("T"," ",true).toTime(),zoho.currenttime).toString("yyyy-MM-dd HH:mm:ssZ");
- m_Item.put("DateCreated",v_Item_DateCreated);
- //
- l_JsonProducts.add(m_Item);
- //
- // generate CSV row for monitoring purposes
- l_CsvFileRow = List();
- l_CsvFileRow.add("\"'" + x_Item.executeXPath("/" + v_ItemNodePart + "/ItemID/text()") + "\"");
- l_CsvFileRow.add("\"" + x_Item.executeXPath("/" + v_ItemNodePart + "/Title/text()") + "\"");
- l_CsvFileRow.add(x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/text()"));
- l_CsvFileRow.add(x_Item.executeXPath("/" + v_ItemNodePart + "/SellingStatus/CurrentPrice/@currencyID").executeXPath("/currencyID/text()"));
- l_CsvFileRow.add(x_Item.executeXPath("/" + v_ItemNodePart + "/QuantityAvailable/text()"));
- l_CsvFileRow.add("\"" + x_Item.executeXPath("/" + v_ItemNodePart + "/SKU/text()") + "\"");
- l_CsvFileRow.add(v_Item_DateCreated);
- //
- v_CsvRow = l_CsvFileRow.toString();
- l_CsvFileRows.add(v_CsvRow);
- }
- if(v_Page>=v_TotalNumberOfPages)
- {
- break;
- }
- }
- //
- m_Response.put("items",l_JsonProducts);
- //info m_Response;
- //
- // lets send the CSV and JSON by email
- if(l_JsonProducts.size()>0)
- {
- //
- // generate a CSV list for monitoring purposes
- v_CSVFilename = "active-ebay-products-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm-ss") + ".csv";
- l_CsvFileRows.add("-----------------------------" + v_CSVFilename);
- //
- // usually \n works on some systems but this one works on systems that \n doesn't
- f_CSVFile = l_CsvFileRows.toString(zoho.encryption.urlDecode("%0A")).toFile(v_CSVFilename);
- //
- // just adding these in case
- v_JsonFilename = "active-ebay-products-" + zoho.currenttime.toString("yyyy-MM-dd-HH-mm-ss") + ".json";
- f_JsonFile = m_Response.toString().toFile(v_JsonFilename);
- //
- // send via Email
- v_CountRows = l_CsvFileRows.size() - 2;
- v_Subject = l_JsonProducts.size() + " eBay Products Export & Comparison";
- v_Message = "Hi there!<br /><br />Please find attached a log of <b>" + l_JsonProducts.size() + "</b> Product(s) from eBay that was exported on <b>" + zoho.currenttime.toString("EEEE, dd-MMM-yyyy") + "</b><br /><br />This is an automated email. Please do not reply to it.<br /><br />Kind Regards,<br /><br />The Team";
- sendmail
- [
- from :zoho.adminuserid
- 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:f_CSVFile,file:f_JsonFile
- ]
- }
- //
- return "Exported " + l_JsonProducts.size() + " product(s)";
Source(s):
- eBay Developers Program - API Explorer Test Tool
- eBay Developers Program - GetMyeBaySelling Documentation
- JoelLipman.com - Zoho Creator: Push to eBay Listings