Guild Wars 2 Spidy bulk script v1.2

2015-01-29

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/1"
// "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 Status is null data is old
  if ($cache.get("Cache Status") == null) { 
    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;
};