An Ebay skimmer written using Google Script for use with a Google Spreadsheet. It records the top 5 highest prices and top 5 lowest prices an item has been sold for and saves it in another sheet. Then it calculates the mean price and the average price the item has sold for.
var apiKey="PUT EBAY API CODE HERE";
var blacklist = "-(lot,bundle,sets,part,parts,spare,rack,mount,rackmount,broken,warranty)";
function onOpen() {
var ui = SpreadsheetApp.getUi();
// Or DocumentApp or FormApp.
ui.createMenu('Price Fetchers')
.addItem('Fill Ebay Prices For Inventory', 'fetchInventoryPrices')
.addToUi();
//var ss = SpreadsheetApp.getActiveSpreadsheet();
// ss.addMenu('JW Prototype Tools', 'Fill Ebay Prices')
}
function fetchInventoryPrices()
{
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var modelSheetID = -1;
for (var i = 0; i < sheets.length; i++)
{
if (sheets[i].getName() === "Saved Models")
{
Logger.log("Found Sheet named Saved Models :" +sheets[i].getName());
modelSheetID = i;
}
}
var lowestPrice = 0;
var highestPrice = 0;
if(modelSheetID != -1)
{
//var i = 0;
var ManufacturerNames = sheets[modelSheetID].getRange("A:A").getValues();
var modelNames = sheets[modelSheetID].getRange("B:B").getValues();
var modelNumbers = sheets[modelSheetID].getRange("C:C").getValues();
var listingsSheetNumber = -1;
var listingsSheet;
var existingItemIds;
//Look to see if we have a sheet saving old listings
for (var i = 0; i < sheets.length; i++)
{
//Found sheet saving listings
if (sheets[i].getName() === "Saved Listings")
{
Logger.log("Found Sheet named Saved Listings :" +sheets[i].getName());
listingsSheetNumber = i;
listingsSheet = sheets[i];
//Get item ID list
existingItemIds = listingsSheet.getRange(2, 2, listingsSheet.getLastRow()).getValues();
}
}
// If sheet not found create new sheet with predetermined name
if(listingsSheetNumber == -1)
{
listingsSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
listingsSheet.setName("Saved Listings")
listingsSheet.getRange(1,1).setValue("Search");
listingsSheet.getRange(1,2).setValue("Item ID");
listingsSheet.getRange(1,3).setValue("Title");
listingsSheet.getRange(1,4).setValue("Price");
listingsSheet.getRange(1,5).setValue("Location");
listingsSheet.getRange(1,6).setValue("URL");
listingsSheet.setFrozenRows(1);
//Get item ID list
existingItemIds = listingsSheet.getRange(2, 2, listingsSheet.getLastRow()).getValues();
}
//In case of [Exceeded maximum execution time] set rowNum to the last filled entry
//Default rowNum = 1
//var rowNum = 1;
var rowNum = sheets[modelSheetID].getRange(1,23).getValue();
if(rowNum == "")
{
rowNum = 1;
}
Logger.log(rowNum);
while (modelNumbers[rowNum][0] != "" || modelNames[rowNum][0] != "")
//while (rowNum < 10)
{
if(modelNumbers[rowNum][0].length > 1 && modelNumbers[rowNum][0].indexOf("nit") < 0 && modelNumbers[rowNum][0].indexOf("eet") < 0 && modelNumbers[rowNum][0].indexOf("ulk") < 0 && modelNumbers[rowNum][0].indexOf("ULK") < 0 && modelNumbers[rowNum][0].indexOf("N/A") < 0&& modelNumbers[rowNum][0].indexOf("n/a") < 0 && modelNumbers[rowNum][0].indexOf("na") != 0)
{
var search = modelNumbers[rowNum][0].toString().replace(/#/,"%23");
search = search.replace(/\\/, "%5C");
search = search.replace(/&/, "%28");
}
else{
var search = modelNames[rowNum][0].toString().replace(/#/,"%23");
search = search.replace(/\\/, "%5C");
search = search.replace(/&/, "%28");
}
Logger.log(search);
var oldValues = sheets[modelSheetID].getRange(rowNum+1,1,1,sheets[modelSheetID].getLastColumn()).getValues();
/*for (var row in existingItemIds) {
for (var col in existingItemIds[row]) {
Logger.log(existingItemIds[row][col]);
}
}
*/
//Search for lowest prices
//Logger.log("Lowest Price:"+oldValues[0][4]);
var lowValues = getLowestPrices(search,listingsSheet,existingItemIds,listingsSheet.getLastRow());
//Logger.log("Lowest Price:"+lowValues.lowestPrice);
//Set lowest price
if(oldValues[0][4] === 0 || oldValues[0][4] > lowValues.lowestPrice)
{
if(lowValues.lowestPrice != 0.00)
{
sheets[modelSheetID].getRange(rowNum+1,5).setValue('='+lowValues.lowestPrice);
}
}
//Set highest price
if( oldValues[0][6] < lowValues.highestPrice)
{
sheets[modelSheetID].getRange(rowNum+1,7).setValue('='+lowValues.highestPrice);
}
//Refresh item ID list
//Logger.log("Refreshing Id list");
//Logger.log("Old Length:"+existingItemIds.length);
existingItemIds = listingsSheet.getRange(2, 2, listingsSheet.getLastRow()).getValues();
//Logger.log("New Length:"+existingItemIds.length);
//Search for highest prices
//Logger.log("Highest Price:"+oldValues[0][6]);
var highValues = getHighestPrices(search,listingsSheet,existingItemIds,listingsSheet.getLastRow());
//Logger.log("Highest Price:"+highValues.highestPrice);
//Set highest price again
if( oldValues[0][6] < highValues.highestPrice && highValues.highestPrice > lowValues.highestPrice)
{
sheets[modelSheetID].getRange(rowNum+1,7).setValue('='+highValues.highestPrice);
}
//Set total value recorded
var total = oldValues[0][7];
if(lowValues.numItems > 0)
{
total +=lowValues.total;
}
if(highValues.numItems > 0)
{
total +=highValues.total;
}
Logger.log("Old total:"+oldValues[0][7].valueOf()+"Total in Entry:"+total);
if(total != oldValues[0][7])
{
sheets[modelSheetID].getRange(rowNum+1,8).setValue('='+total);
}
//Set Sample size
sheets[modelSheetID].getRange(rowNum+1,10).setValue(oldValues[0][9]+lowValues.numItems+highValues.numItems);
//Refresh item ID list
Logger.log("Refreshing Id list");
Logger.log("Old Length:"+existingItemIds.length);
existingItemIds = listingsSheet.getRange(2, 2, listingsSheet.getLastRow()).getValues();
Logger.log("New Length:"+existingItemIds.length);
rowNum++;
sheets[modelSheetID].getRange(1,23).setValue(rowNum);
}
}
Logger.log("Script Finished Running");
sheets[modelSheetID].getRange(1,23).setValue(1);
}
function getLowestPrices(search, sheet,existingItemIds, idLength)
{
Logger.log("Lowest Prices for "+search);
var url = "https://svcs.ebay.com/services/search/FindingService/v1";
url += "?SECURITY-APPNAME="+apiKey;
url += "&OPERATION-NAME=findCompletedItems";
url += "&SERVICE-VERSION=1.12.0";
url += "&keywords=%22"+search+"%22";
url += "%20"+blacklist
url += "&RESPONSE-DATA-FORMAT=JSON";
url += "&REST-PAYLOAD";
url += "&paginationInput.entriesPerPage=5";
url += "&itemFilter.name=SoldItemsOnly&itemFilter.value=true";
url += "&GLOBAL-ID=EBAY-US";
url += "&siteid=0";
url += "&sortOrder=PricePlusShippingLowest";
var response = UrlFetchApp.fetch(url);
Logger.log(url);
var dataAll = JSON.parse(response.getContentText());
var items = dataAll.findCompletedItemsResponse[0].searchResult[0].item;
var total = 0;
var lowestPrice = 0.00;
var highestPrice = 0.00;
var numItems = 0;
var originalSearch = search.replace("%23","#");
originalSearch = originalSearch.replace("%5C","\\");
originalSearch = originalSearch.replace("%28","&");
Logger.log("Lowest Prices for "+originalSearch);
//Logger.log("Item List Length:"+items.length());
//Make sure results are not empty
if(items!= null)
{
Logger.log("List Not Empty");
Logger.log("Items Found:" +items.length);
for(var i=0; i<items.length; i++)
{
//Logger.log("Entered 1st For loop "+i+" Times");
var newItem = true;
for( var j = 0; j < idLength ; j++)
{
//Logger.log("Entered 2nd For loop "+j+" Times");
//Logger.log("Current ID:"+existingItemIds[j][0]);
//See if we have the item id recorded before
if(existingItemIds[j][0] != null)
{
if(items[i].itemId == existingItemIds[j][0])
{
Logger.log("Same ItemID found");
newItem = false;
}
}
}
//Add to sheet if new item
if(newItem == true)
{
Logger.log("New Item, adding to records")
var rowCell = i + sheet.getLastRow() + 1;
sheet.getRange(rowCell,1,1,1).setValue(originalSearch);
sheet.getRange(rowCell,2,1,1).setValue(items[i].itemId);
sheet.getRange(rowCell,3,1,1).setValue(items[i].title);
sheet.getRange(rowCell,4,1,1).setValue(items[i].sellingStatus[0].convertedCurrentPrice[0].__value__);
sheet.getRange(rowCell,5,1,1).setValue(items[i].location);
sheet.getRange(rowCell,6,1,1).setValue(items[i].viewItemURL);
sheet.sort(1);
/*var value = parseFloat(items[i].sellingStatus[0].convertedCurrentPrice[0].__value__);
Logger.log("Total:"+total);
total += value;
if(value < lowestPrice || lowestPrice == 0)
{
lowestPrice = value;
}
if(value > highestPrice)
{
highestPrice = value;
}
*/
numItems++;
}
var value = parseFloat(items[i].sellingStatus[0].convertedCurrentPrice[0].__value__);
Logger.log("Total:"+total);
total += value;
if(value < lowestPrice || lowestPrice == 0.00)
{
lowestPrice = value;
}
if(value > highestPrice)
{
highestPrice = value;
}
}
}
else{
Logger.log("List Empty");
lowestPrice = 0;
}
url = "";
Logger.log("Returning:lowestPrice:"+lowestPrice+", numItems:"+numItems +", total:"+total+" For search:"+search);
Logger.log("Total:"+total);
return ({"highestPrice":highestPrice,"lowestPrice":lowestPrice, "numItems":numItems, "total":total});
}
function getHighestPrices(search, sheet,existingItemIds, idLength)
{
Logger.log("Highest Prices for "+search);
var url = "https://svcs.ebay.com/services/search/FindingService/v1";
url += "?SECURITY-APPNAME="+apiKey;
url += "&OPERATION-NAME=findCompletedItems";
url += "&SERVICE-VERSION=1.12.0";
url += "&keywords=%22"+search+"%22";
url += "%20"+blacklist
url += "&RESPONSE-DATA-FORMAT=JSON";
url += "&REST-PAYLOAD";
url += "&paginationInput.entriesPerPage=5";
url += "&itemFilter.name=SoldItemsOnly&itemFilter.value=true";
url += "&GLOBAL-ID=EBAY-US";
url += "&siteid=0";
url += "&sortOrder=PricePlusShippingHighest";
var response = UrlFetchApp.fetch(url);
Logger.log(url);
var dataAll = JSON.parse(response.getContentText());
var items = dataAll.findCompletedItemsResponse[0].searchResult[0].item;
var total = 0;
var highestPrice = 0.00;
var numItems = 0;
var originalSearch = search.replace("%23","#");
originalSearch = originalSearch.replace("%5C","\\");
originalSearch = originalSearch.replace("%28","&");
Logger.log("Lowest Prices for "+originalSearch);
//Logger.log("Item List Length:"+items.length());
//Make sure results are not empty
if(items!= null)
{
Logger.log("List Not Empty");
Logger.log("Items Found:" +items.length);
for(var i=0; i<items.length; i++)
{
//Logger.log("Entered 1st For loop "+i+" Times");
var newItem = true;
for( var j = 0; j < idLength ; j++)
{
//Logger.log("Entered 2nd For loop "+j+" Times");
//Logger.log("Current ID:"+existingItemIds[j][0]);
//See if we have the item id recorded before
if(existingItemIds[j][0] != null)
{
if(items[i].itemId == existingItemIds[j][0])
{
Logger.log("Same ItemID found");
newItem = false;
}
}
}
//Add to sheet if new item
if(newItem == true)
{
Logger.log("New Item, adding to records")
var rowCell = i + sheet.getLastRow() + 1;
sheet.getRange(rowCell,1,1,1).setValue(originalSearch);
sheet.getRange(rowCell,2,1,1).setValue(items[i].itemId);
sheet.getRange(rowCell,3,1,1).setValue(items[i].title);
sheet.getRange(rowCell,4,1,1).setValue(items[i].sellingStatus[0].convertedCurrentPrice[0].__value__);
sheet.getRange(rowCell,5,1,1).setValue(items[i].location);
sheet.getRange(rowCell,6,1,1).setValue(items[i].viewItemURL);
sheet.sort(1);
numItems++;
}
var value = parseFloat(items[i].sellingStatus[0].convertedCurrentPrice[0].__value__);
Logger.log("Total:"+total);
total += value;
if(value > highestPrice)
{
highestPrice = value;
}
}
}
else{
Logger.log("List Empty");
highestPrice = 0;
}
url = "";
Logger.log("Returning:highestPrice:"+highestPrice+", numItems:"+numItems +", total:"+total+" For search:"+search);
Logger.log("Total:"+total);
return ({"highestPrice":highestPrice, "numItems":numItems, "total":total});
}