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.
copyraw
/* 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
Category: Zoho :: Article: 838
Add comment