Guild Wars 2 Spidy bulk script v1.2

This is a slightly modified version of the script I use for my Guild Wars 2 spreadsheets for data collection and analysis, consider this 1.2 of the public beta.

As time progresses I will be fixing it so it is robust and easier to use.


//////////////////// //////Settings////// //////////////////// // the names of your datadump sheet and your logsheet var sheet = “Data”; var log = “Log”; // To select the sections remove the first “//” before the row you want. var filters = // “http://www.gw2spidy.com/api/v0.9/json/allitems/# // Number Type // “http://www.gw2spidy.com/api/v0.9/json/allitems/2”, // 65 Bags // “http://www.gw2spidy.com/api/v0.9/json/allitems/3”, // 2274 Misc // “http://www.gw2spidy.com/api/v0.9/json/allitems/4”, // 304 Containers/Satchels // “http://www.gw2spidy.com/api/v0.9/json/allitems/5”, // 1098 Crafting component // “http://www.gw2spidy.com/api/v0.9/json/allitems/7”, // 41 Endless Potions/tonics // “http://www.gw2spidy.com/api/v0.9/json/allitems/11”, // 173 Mini’s // “http://www.gw2spidy.com/api/v0.9/json/allitems/13”, // 3 Salvage Kits // “http://www.gw2spidy.com/api/v0.9/json/allitems/15”, // 1362 Accessory // “http://www.gw2spidy.com/api/v0.9/json/allitems/16”, // 262 Junk // “http://www.gw2spidy.com/api/v0.9/json/allitems/17”, // 531 Upgrade components. // “http://www.gw2spidy.com/api/v0.9/json/allitems/18”, // 11469 Weapons //// Here you can select a custom list in the format as follows //// // “http://www.gw2spidy.com/api/v0.9/json/items/all/?filter_ids=ID1,ID2,ID3,ID4” /////////////////////////////////////////////////////////////////////////// ///////////////////////////// Dont edit below this //////////////////////// /////////////////////////////////////////////////////////////////////////// ; // Refresh This is the main function, it automates everything function refresh(){ expireCache(); updateAll(); updategems(); } // Menu function onOpen() { var ui = SpreadsheetApp.getUi(); // Or DocumentApp or FormApp. ui.createMenu(‘Investment Tools’) .addItem(‘Refresh’, ‘refresh’) .addSeparator() .addItem(‘Clear Log’, ‘clearLog’) .addToUi(); } // Gem Price // Use the formula: =updategems() to load it function updategems(){ var gems = “http://www.gw2spidy.com/api/v0.9/json/gem-price”; var jsonData = UrlFetchApp.fetch(gems); var jsonString = jsonData.getContentText(); var jsonObject = JSON.parse(jsonString).result; // create object and remove “result” wrapper var buyValuea = (jsonObject.gold_to_gem); var sellValuea = (jsonObject.gem_to_gold); var buyValue = buyValuea/100; var sellValue = sellValuea/100; return [buyValue, sellValue]; } function refreshdata(){ $cache = CacheService.getPublicCache(); } // Updating Function function updateAll() { $url = [filters]; $sheetname = sheet; $logsheetname = log; updateRawData($url, $sheetname, $logsheetname); } // expire the cache by setting the cache status to null function expireCache() { $cache = CacheService.getPublicCache(); $cache.remove(“Cache Status”); }; // Clear the Log function clearLog() { $shee = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Log”); $shee.getRange(2, 1, $shee.getLastRow(), 6).clearContent(); }; // removes characters that could crash a JSON parse function makeJSONsafe($unsafe) { $safe = $unsafe.replace(/(u000A)+|(n)+|u000A|(u000D)+|u000D|u000Au000D|nr/g, ‘ ‘); return $safe; }; // retrives JSON from Spidy and adds item data to the cache function add2CacheFromSpidy($JSONURL) { var $options = { “muteHttpExceptions” : true }; // muting HTTP exceptions prevents script from crashing with HTTP error var $cache = CacheService.getPublicCache(); // open the cache using Google’s CacheService var $message = “”; $cache.put(“Cache Status”, “Cache update in progress when data refresh called.”); var i = 0 while (i < $JSONURL.length) { var $jsonData = UrlFetchApp.fetch($JSONURL[i], $options); // fetch the HTTPResponse Object using Google’s URLFetchApp if ($jsonData.getResponseCode() == 200) { // successful connection var $jsonString = $jsonData.getContentText(); // retrive JSON string from HTTPResponse Object var $spidyObject = JSON.parse(makeJSONsafe($jsonString)); // convert JSON string into a JSON Object var $itemsList = $spidyObject.results; // retrive array of item objects from Spidy JSON Object results parameter var $numItems = $spidyObject.count; // retrive number of items from Spidy JSON Object count paramter for (var $j = 0; $j < $numItems; $j++) { // caching loop – each item is stored as an object within the $cache.put(“” + $itemsList[$j].data_id, JSON.stringify($itemsList[$j])); // items list array which is indexed from 0 – $count } // this loop takes those objects converts them into a JSON string and // stores them in the cache indexed by the spidy item id $message = “” + $numItems + ” items retrived from GW2Spidy and cached.”; $cache.put(“Cache Status”, $message, 900); // this is used as a flag to determine if the cache data is fresh (900 seconds) $message = “”; $cache.put(“Connection Error”, $message); // reset error message as it is a flag for if there is an error } else { // unsuccessful connection $message = “HTTP Error ” + $jsonData.getResponseCode() + ” during connection attempt”; $cache.put(“Connection Error”, $message); // set error message } i++ } }; function writeToLog($message, $date, $sheet, $flag) { if ($flag) { var $row = $sheet.getLastRow()+1; $sheet.getRange($row, 2).setValue($date); $sheet.getRange($row, 3).setValue($message); } }; // work horse function, pulls everything together function updateRawData($url, $sheetName, $logsheetName) { var $cache = CacheService.getPublicCache(); // open the cache var $sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName($sheetName); // get sheet to write data too var $logFlag = 0; if ($logsheetName != “”) { var $logsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName($logsheetName); $logFlag = 1; // only log is a logsheet is specified } if ($cache.get(“Cache Status”) == null) // if Cache Status is null data is old { writeToLog(“Cache update started.”, new Date(), $logsheet, $logFlag); add2CacheFromSpidy($url); // update the cache from URL $cache = CacheService.getPublicCache(); // refresh cache variable (is this a pointer? can i get rid of this line? writeToLog($cache.get(“Cache Status”), new Date(), $logsheet, $logFlag); } if ($cache.get(“Connection Error”) == “” || $cache.get(“Connection Error”) == null) { var $IDList = $sheet.getRange(3, 1, $sheet.getLastRow() – 2, 1).getValues(); // returns 2-D array [row][column] with id stored, [[id1], [id2], etc.] $itemList = fetchCachedData($IDList, $cache); // fetches the cached data and returns 2D Array [row][column] $sheet.getRange(3, 2, $IDList.length, 5).setValues($itemList); // write data to spreadsheet writeToLog(“Detected ” + $IDList.length + ” ID(s) in sheet ” + $sheetName + “. Data refreshed successfully.”, new Date(), $logsheet, $logFlag); } else { writeToLog($cache.get(“Connection Error”), new Date(), $logsheet, $logFlag); } }; // take range contained IDs and return 2D array of data from the cache function fetchCachedData($IDList, $cache) { var $itemObject = null; var $id = 0; var $itemList = new Array($IDList.length); for (var $i = 0; $i < $IDList.length; $i++) { $id = $IDList[$i][0]; // due to the way the getRange function works, the IDs are in a 2D array [[id1], [id2], [id3], … [idn]] $itemObject = JSON.parse($cache.get($id)); // convert JSON text in cache into an object $itemList[$i] = new Array(5); if (($id > 0) && ($itemObject != null)) { // convert object into an array $itemList[$i][0] = $itemObject.name; $itemList[$i][1] = $itemObject.max_offer_unit_price; $itemList[$i][2] = $itemObject.min_sale_unit_price; $itemList[$i][3] = $itemObject.price_last_changed; $itemList[$i][4] = “=(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0)+1)”; } else { $itemList[$i][0] = “Error: ID not in cache”; $itemList[$i][4] = “=(OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())),-1,0)+1)”; } } return $itemList; };

One Reply to “Guild Wars 2 Spidy bulk script v1.2”

Leave a Reply