1. #1

    Export guild roster from armory (API) to Google Sheets

    Hi all, not 100% sure this is the best spot for this post but thought I'd give it a shot. I'm looking for a way to export the guilds roster from the wow armory and populate it in to a google sheet. Info I'd be trying to export would be name, class, specs, ilvl, max level of ring, no. of boss kills from current raid tier.

    I've had a look around but not much luck at this stage. Anyone able to do this and share what they may have?

  2. #2
    You can sign up for API access at https://dev.battle.net and then take a look at the information you can request at https://dev.battle.net/io-docs. You're looking for the guild and character items requests. I don't know how to cleanly export, but at least you'll have easy to copy data instead of having to manually go through the guild page by page.
    Originally Posted by Zarhym (Blue Tracker)
    this thread is a waste of internet

  3. #3
    Here's a Google App Engine script that you can use with Sheets, written by some anonymous author I imported the sheet from. It's not the best, but it works and I didn't want to spend time re-inventing the wheel. Hopefully it will do what you want to do, or give you enough pointers for your own implementation.

    The old API that allowed anonymous access was revoked fairly recently. You will need to register for the Battle.net Community API using the links @Kanegasi provided to get our own key, then substitute it where it says YOUR_API_KEY_HERE.

    To use the returned data as a row, remember to transpose() it first.

    Code:
    //Some very cleaned up, but poorly executing code
    
    function getAPI(toonName,toonRealm) {
      var cache = CacheService.getDocumentCache();
      var value = cache.get(toonName+"-"+toonRealm);
      if (value == null) {
        for (var n = 0; n < 6; n++) {
          try {
            var toonJSON = UrlFetchApp.fetch("https://us.api.battle.net/wow/character/"+toonRealm+"/"+toonName+"?fields=items,statistics,progression,audit&apikey=YOUR_API_KEY_HERE");
            if (toonJSON.getResponseCode() === 200) {
              value = JSON.parse(toonJSON.getContentText());
              cache.put(toonName+"-"+toonRealm, value, 10800);
            }
          } catch (err) {
            if (n == 5) {
              throw err;
            }
            Utilities.sleep((Math.pow(2,n)*1000) + (Math.round(Math.random() * 1000)));
          }
        }
      }
      return value;
    }
    
    function pull(toonName,toonRealm) {
     
      if(!toonName || !toonRealm) {
       return ""
      }
     
      var toon;
      for (var n = 0; n < 6; n++) {
        try {
          var toonJSON = UrlFetchApp.fetch("https://us.api.battle.net/wow/character/"+toonRealm+"/"+toonName+"?fields=items,statistics,progression,audit&apikey=YOUR_API_KEY_HERE");
          if (toonJSON.getResponseCode() === 200) {
            toon = JSON.parse(toonJSON.getContentText());
          }
        } catch (err) {
          if (n == 5) {
            throw err;
          }
          Utilities.sleep((Math.pow(2,n)*1000) + (Math.round(Math.random() * 1000)));
        }
      }
     
      var wodstats = toon.statistics.subCategories[5].subCategories[5].statistics
     
      var bosskills = 0 
      for (var i = 1; i <= 15; i+=2){
        bosskills += wodstats[i].quantity
      }
      
      var grgemindex = [115809, 115811, 115812, 115813, 115814, 115815], 
          gemindex = [115803, 115804, 115805, 115806, 115807, 115808], 
          gem75index = [127760, 127761, 127762, 127763, 127764, 127765], 
          gems75 = 0,
          grgems = 0, 
          gems = 0
      
      var getItem = function(item){
        var itemlevel = 0,
            itemlevelcm = 0
        if (item) { 
          itemlevel = item.itemLevel,
          itemlevelcm = itemlevel 
          if (itemlevel > 630) { var itemlevelcm = 630 } 
            if (gem75index.indexOf(item.tooltipParams["gem0"]) > -1) { 
            gems75++ 
          } else if (grgemindex.indexOf(item.tooltipParams["gem0"]) > -1) { 
            grgems++ 
          } else if (gemindex.indexOf(item.tooltipParams["gem0"]) > -1) { 
            gems++ 
          }
        }
        return [itemlevel, itemlevelcm] 
      }
      
      var head = getItem(toon.items.head),  
          neck = getItem(toon.items.neck), 
          shoulders = getItem(toon.items.shoulder), 
          back = getItem(toon.items.back), 
          chest = getItem(toon.items.chest), 
          wrist = getItem(toon.items.wrist), 
          hands = getItem(toon.items.hands), 
          waist = getItem(toon.items.waist), 
          legs = getItem(toon.items.legs), 
          feet = getItem(toon.items.feet), 
          finger1 = getItem(toon.items.finger1), 
          finger2 = getItem(toon.items.finger2), 
          trinket1 = getItem(toon.items.trinket1), 
          trinket2 = getItem(toon.items.trinket2), 
          mainHand = getItem(toon.items.mainHand), 
          offHand = getItem(toon.items.offHand)
     
      var CM_iL_Total = head[1]+neck[1]+shoulders[1]+back[1]+chest[1]+wrist[1]+hands[1]+waist[1]+legs[1]+feet[1]+finger1[1]+finger2[1]+trinket1[1]+trinket2[1]+mainHand[1]+offHand[1] 
      var CM_iL = CM_iL_Total / 16 //Kept this portion of code for future expansions
      if (offHand[1] == 0) { CM_iL = CM_iL_Total / 15 }
     
      var killsforRaidId = function(raid_id){
        var normal = 0,
            heroic = 0,
            mythic = 0;
     
        toon.progression.raids[raid_id].bosses.forEach(function(boss){
          normal += boss.normalKills 
          heroic += boss.heroicKills 
          mythic += boss.mythicKills 
        }) 
        return [normal, heroic, mythic] 
      } 
      
      var highmaul = killsforRaidId(32), 
          highmaulNormal = highmaul[0], 
          highmaulHeroic = highmaul[1], 
          highmaulMythic = highmaul[2] 
     
      var brf = killsforRaidId(33), 
          brfNormal = brf[0],  
          brfHeroic = brf[1],  
          brfMythic = brf[2] 
      
      var hfc = killsforRaidId(34), 
          hfcNormal = hfc[0], 
          hfcHeroic = hfc[1], 
          hfcMythic = hfc[2]
      
      var giftindex = [5310, 5317, 5311, 5324, 5318, 5325, 5312, 5319, 5326, 5313, 5320, 5327, 5314, 5321, 5328], 
          breathindex = [5281, 5285, 5284, 5298, 5292, 5297, 5300, 5293, 5299, 5302, 5294, 5301, 5304, 5295, 5303] 
      
      var enchantsByItem = function(item) {
        if (item) {
          var enchant
          if (giftindex.indexOf(item.tooltipParams["enchant"]) > -1) {
            enchant = "Gift"
          } else if (breathindex.indexOf(item.tooltipParams["enchant"]) > -1) {
            enchant = "Breath"
          } else {
            enchant = "None"
          } 
        }
        else { enchant = "N/A" }
        return enchant
      }
    
      var neckchant = enchantsByItem(toon.items.neck), 
          backchant = enchantsByItem(toon.items.back), 
          finger1chant = enchantsByItem(toon.items.finger1), 
          finger2chant = enchantsByItem(toon.items.finger2) 
      
      var getWeaponEnchant = function(enchantid) {
        if (toon.class == 6 && enchantid) {return "DK"} 
        switch (enchantid) {
          case 5336:
            return "Blackrock"
            break;
          case 5335:
            return "Shadowmoon"
            break;
          case 5334:
            return "Frostwolf"
            break;
          case 5331:
            return "Shattered Hand"
            break;
          case 5276:
            return "Megawatt"
            break;
          case 5275:
            return "Oglethorp's"
            break;
          case 5383:
            return "Hemet's"
            break;
          case 5330:
            return "Thunderlord"
            break;
          case 5337: 
            return "Warsong"
            break;
          case 5384:
            return "Bleeding Hollow"
            break;
          default:
            return "None"
        } 
      }
      
      var wepchant, 
          wepchantoh 
      
      if (toon.items.mainHand) {
        wepchant = getWeaponEnchant(toon.items.mainHand.tooltipParams["enchant"])
      } else { wepchant = "N/A" } 
      
      if (toon.items.offHand && toon.items.offHand.weaponInfo) {
        wepchantoh = getWeaponEnchant(toon.items.offHand.tooltipParams["enchant"])
      } 
      
      var currentdate = new Date() 
      var lastupdate =   (currentdate.getMonth()+1) + "/" 
      + currentdate.getDate() + "/" 
      + currentdate.getFullYear() + " @ "  
      + currentdate.getHours() + ":"  
      + currentdate.getMinutes() + ":" 
      + currentdate.getSeconds() + " GMT" 
      
      var T18Sets = ["Arcanic Conclave", 
                     "Oathclaw", 
                     "Pious", 
                     "Reathrattle", 
                     "Demongaze", 
                     "Ceaseless Vigil", 
                     "Iron Wrath", 
                     "Felblade", 
                     "Hurricane's Eye", 
                     "Living Mountain", // TECTUS
                     "Savage Hunt"] 
                     
      var setPiece = function(slot) {
        var setsT18 = 0,
            setsT17 = 0
        if (slot.tooltipParams.set && (slot.itemLevel == 695 || slot.itemLevel == 710 || slot.itemLevel == 725 || slot.itemLevel == 701 || slot.itemLevel == 716 || slot.itemLevel == 731 || slot.itemLevel == 705 || slot.itemLevel == 720 || slot.itemLevel == 735 || slot.itemLevel == 711 || slot.itemLevel == 726 || slot.itemLevel == 741)) { //T18
          setsT18 = slot.tooltipParams.set.length 
        }
        else if (slot.tooltipParams.set && (slot.itemLevel == 670 || slot.itemLevel == 685 || slot.itemLevel == 700 || slot.itemLevel == 676 || slot.itemLevel == 691 || slot.itemLevel == 706)) { //T17
          setsT17 = slot.tooltipParams.set.length 
        }
        return [setsT18, setsT17] 
      }
      
      var tierHead = setPiece(toon.items.head), 
          tierShoulder = setPiece(toon.items.shoulder), 
          tierChest = setPiece(toon.items.chest), 
          tierHands = setPiece(toon.items.hands), 
          tierLegs = setPiece(toon.items.legs) 
      
      var tier18pieces = Math.max(tierHead[0], tierShoulder[0], tierChest[0], tierHands[0], tierLegs[0]), 
          tier17pieces = Math.max(tierHead[1], tierShoulder[1], tierChest[1], tierHands[1], tierLegs[1])
     
      var toonInfo = new Array(
        toon.items.averageItemLevelEquipped, tier18pieces+"/"+tier17pieces,
        head[0], neck[0], 
        shoulders[0], back[0],
        chest[0], wrist[0],
        hands[0], waist[0],
        legs[0], feet[0],
        finger1[0], finger2[0],
        trinket1[0],trinket2[0],
        mainHand[0], offHand[0],
        wepchant, wepchantoh, neckchant, finger1chant, finger2chant, backchant, 
        toon.audit.emptySockets, gems, grgems, gems75, 
        hfcNormal, hfcHeroic, hfcMythic, 
        lastupdate
      )
     return toonInfo;
    }
    Last edited by spikeh; 2016-05-25 at 08:57 AM.

  4. #4
    Thanks guys. It's looking like it may be a tad outside my skill level. Went to the google app engine (here https://appengine.google.com/start) and got : "New Application Creation is disabled." Think I need a basic training course

  5. #5
    I should have posted better instructions, I thought you knew where to put code snippet.

    First, go to https://dev.battle.net/member/register and sign up for a Mashery account.

    Once you've created the account and logged in, go to https://dev.battle.net/apps/myapps and click on "Create a new application". Fill in with whatever information, it doesn't matter. Make sure "Issue a new key for game APIs" is checked.

    After some time you will receive an email saying you have been approved. Go to https://dev.battle.net/apps/mykeys and you will see your API key, which looks like a long string of random alphanumeric characters. Insert this where it says "YOUR_API_KEY_HERE" in the code snippet when you paste it, without any quotes.

    Go to your Google Sheets spreadsheet, click on Tools -> Script Editor..., which will open a new Google Scripts tab that should be empty for you.

    Click on File -> New -> Script file, name it whatever you want, then paste the code snippet in. Save the script file.

    Now go back to your sheet. You need two cells containing the name and realm of a character which will be used as input to the script function, for example A1 = "Reinholder", B1 = "Frostmourne". Then in C1, type "=transpose(pull(A1,B1,ROW(C1)))". This will populate C1 to AH1 with data, with the following headings:

    E-iLvl
    T18/17
    Head
    Neck
    Shoulder
    Back
    Chest
    Wrist
    Hands
    Waist
    Legs
    Feet
    Ring
    Ring
    Trinket
    Trinket
    MH
    OH
    Weapon
    Offhand
    Neck
    Ring
    Ring
    Cloak
    Gems
    N
    H
    M
    Last Update

  6. #6
    Deleted
    I'd recommend following this guide, it's pretty good
    http://bruk.org/api/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •