Silveress's Gw2 Script

// Varibles
    $sheetname    = "Data";
    $logsheetname = "Log";
    $startRow     = 3;

// refresh
    function refresh(){
      expireCache();
      updateAll();
      updategems();
    }
    function refreshdata(){
      $cache = CacheService.getPublicCache();
    }
// Updating Function
   function updateAll() {

    $url = [];
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName($sheetname);
    var noIDs = 100;
    var IDListLength = sheet.getLastRow();
    var repeatRough = IDListLength / noIDs;
    var repeat = Math.ceil(repeatRough);

     for (var i = 0; i < repeat;){
        var selection = i * noIDs;
        var row = $startRow + selection;
        var idlist0 = sheet.getRange(row, 1, noIDs, 1).getValues();
        var idlist1 = "http://www.gw2spidy.com/api/v0.9/json/items/all/?filter_ids="  // Base
        var idlist2 = idlist0.join(",");
        var idlist3 = idlist1 +" "+ idlist2;
        $url.push(idlist3)
        i++
        }
    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();
  };

// Menu
                function onOpen() {
        var ui = SpreadsheetApp.getUi();
        // Or DocumentApp or FormApp.
        ui.createMenu('Investment Tools')
        .addItem('Refresh', 'refresh')
        .addSeparator()
        .addItem('Clear Log', 'clearLog')
        .addToUi();
}
// removes characters that could crash a JSON parse

  function makeJSONsafe($unsafe) {
    $safe = $unsafe.replace(/(\u000A)+|(\n)+|\u000A|(\u000D)+|\u000D|\u000A\u000D|\n\r/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;
  };


// Gems

  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];
}