Google Docs script for pulling character information from the wow API

This is a Google Apps Script I created to pull data about my toons from the wow/armory API.

I use this to populate my google apps spreadsheet with my character lists and such with level/class/race/iLvl information.

This is the second iteration and I am happy with it now. I might consider adding more, but it gets a bit convoluted and I almost prefer to just show anyone who wants more info how to modify it to show what they want...

This gives output that looks like this now:

Attachment 590
This is what the second looks like, with per-item iLvl information. It gets very long horizontally, so I flipped it to vertical.

Attachment 589

The script's attached to this post and can also be seen here: (Installation instructions here)

Code:

---

/*

Version 0.2 April 26 2012

Changelog:

0.2 - Added profession information

--

wow()function for google docs spreadsheet to gather information from the wow armory for a given toon-realm.

Usage: =wow(toonName,realmName)

This outputs Name, Class, Race, Level and iLvl for the given character.

Example Output: (one in each cell in a row)

Name: Destähd    Race: Blood Elf    Class: Paladin    Level: 85    iLvl: 368

The Blizzard API is documented here:

http://blizzard.github.com/api-wow-docs/

Excerpts:

Profile

The Character Profile API is the primary way to access character information. This Character Profile API can be used to fetch a single character at a time through an HTTP GET request to a URL describing the character profile resource. By default, a basic dataset will be returned and with each request and zero or more additional fields can be retrieved. To access this API, craft a resource URL pointing to the character whos information is to be retrieved.
URL = Host + "/api/wow/character/" + Realm + "/" + CharacterName

Character Races
The character races data API provides a list of character races.

URL = Host + "/api/wow/data/character/races"

Character Classes
The character classes data API provides a list of character classes.

URL = Host + "/api/wow/data/character/classes"

The regions where this API is available are:

    us.battle.net

    eu.battle.net

    kr.battle.net

    tw.battle.net

    www.battlenet.com.cn

*/

function wow(toonName,realmName) {
//function wow(toonName,realmName) {
 
//  var toonName = "Déstáhd"; //comment this out for actual usage... Tailor
//  var toonName = "Destähd"; //comment this out for actual usage... no professions
 
//  var realmName = "Magtheridon"; //comment this out for actual usage...
 
// Character information
  var toonJSON = UrlFetchApp.fetch("us.battle.net/api/wow/character/"+realmName+"/"+toonName+"?fields=items,professions");
  var toon = Utilities.jsonParse(toonJSON.getContentText());

// populate Race database
  var racesJSON = UrlFetchApp.fetch("us.battle.net/api/wow/data/character/races")
  var races = Utilities.jsonParse(racesJSON.getContentText());
     
// populate Classes database
  var classesJSON = UrlFetchApp.fetch("us.battle.net/api/wow/data/character/classes")
  var classes = Utilities.jsonParse(classesJSON.getContentText());

// Walk through races to find race name from the ID provided from the character dump.
  for (var r = 0; r < races.races.length; r++) {
    if (races.races[r].id == toon.race) {
      var toonRace = races.races[r].name;
    }
  }

// Walk through classes to find class name from the ID provided from the character dump.
  for (var c = 0; c < classes.classes.length; c++) {
    if (classes.classes[c].id == toon.class) {
      var toonClass = classes.classes[c].name;
    }
  }

// Professions information
 
  var ProfessionA = "none";
  var ProfessionB = "none";
 
  if (toon.professions.primary.length == 1) {
    ProfessionA = toon.professions.primary[0].name+" "+toon.professions.primary[0].rank;
  }
 
  if (toon.professions.primary.length == 2) {
    ProfessionA = toon.professions.primary[0].name+" "+toon.professions.primary[0].rank;
    ProfessionA = toon.professions.primary[1].name+" "+toon.professions.primary[1].rank;
  }
 
// Collated information we're going to output... 
  var toonInfo = new Array(
    "Name: "+toon.name,
    "Race: "+toonRace,
    "Class: "+toonClass,
    "Level: "+toon.level,
    "iLvl: "+toon.items.averageItemLevel,
    "Profession: "+ProfessionA,
    "Profession: "+ProfessionB
  )
 
  Logger.log(toonInfo);
  return toonInfo;
}

---

Neat! Thanks for sharing

Very nice, as I'm always loosing track of what toon is on what.

Can Professions be added to it, or does that even show up via the API?

Stephen

Nice. Thanks for sharing. :)

this looks sweet! Thanks.

can iLevel for all item slots be added?

I'm glad it's useful for someone(s) else. :)

Quote:

---

Originally Posted by F9thRet

Very nice, as I'm always loosing track of what toon is on what.

Can Professions be added to it, or does that even show up via the API?

Stephen

---

It can be added and it does show in the API. This is my next goal.

Quote:

---

Originally Posted by shadewalker

this looks sweet! Thanks.

can iLevel for all item slots be added?

---

In theory, yes, that can be added, but it would really make the script 'loud' (it also adds a whole layer of complexity for me to work out, as the API just gives item ID and name, so I'd have to figure out how to add an additional lookup to work out individual iLvl.)

Per-slot iLvl is a bit of a stretch, but I'll try to figure out how to work this out, maybe with a toggle, so it can be shown, or not, selectively.

for people with some Dev in them, the whole dataset can be extracted using the Armory Api. Send a simple request and you get an JSON response with all the data.
For example:
http://us.battle.net/api/wow/charact...,titles,quests

returns

Quote:

---

{"lastModified":1332676694000,"name":"Soap","realm ":"Shadow Council","class":2,"race":10,"gender":1,"level":83 ,"achievementPoints":5490,"thumbnail":"shadow-council/61/64745789-avatar.jpg","guild":{"name":"League of Oceania","realm":"Shadow Council","level":19,"members":122,"achievementPoin ts":635,"emblem":{"icon":114,"iconColor":"ff004867 ","border":4,"borderColor":"fff9cc30","backgroundC olor":"ffb1002e"}},"items":{"averageItemLevel":289 ,"averageItemLevelEquipped":286,"head":{"id":61466 ,"name":"Bell-Ringer's Skullcap","icon":"inv_helmet_189","quality":2,"too ltipParams":{}},"neck":{"id":61472,"name":"Amulet of Reconstruction","icon":"inv_misc_necklacea7","qual ity":3,"tooltipParams":{}},"shoulder":{"id":61418, "name":"Terrath's Rocky Spaulders","icon":"inv_shoulder_149","quality":2," tooltipParams":{}},"back":{"id":61415,"name":"Cloa k of Fungal Growth","icon":"inv_misc_cape_cataclysm_tank_b_01" ,"quality":2,"tooltipParams":{}},"chest":{"id":614 36,"name":"Gateshattering Hauberk","icon":"inv_chest_plate_31","quality":2," tooltipParams":{}},"tabard":{"id":65906,"name":"Ta bard of the Guardians of Hyjal","icon":"inv_misc_tabard_guardiansofhyjal"," quality":1,"tooltipParams":{}},"wrist":{"id":61421 ,"name":"Petrified Stone Bracers","icon":"inv_bracer_79","quality":2,"toolt ipParams":{}},"hands":{"id":61454,"name":"Catapult Loader's Gloves","icon":"inv_gauntlets_122","quality":2,"to oltipParams":{}},"waist":{"id":61427,"name":"Gyrew orm Waistguard","icon":"inv_belt_100","quality":2,"too ltipParams":{}},"legs":{"id":61412,"name":"Fungus-Stained Legplates","icon":"inv_pants_plate_dungeonplate_c_ 03","quality":3,"tooltipParams":{}},"feet":{"id":6 1502,"name":"Deepstone Treads","icon":"inv_boots_plate_23","quality":2,"t ooltipParams":{}},"finger1":{"id":57331,"name":"Ri ng of the Quenched Inferno","icon":"inv_misc_starring1","quality":2," tooltipParams":{}},"finger2":{"id":57383,"name":"S harptooth Signet","icon":"inv_misc_starring1","quality":2,"t ooltipParams":{}},"trinket1":{"id":49118,"name":"B ubbling Brightbrew Charm","icon":"inv_drink_08","quality":4,"tooltipP arams":{}},"trinket2":{"id":49116,"name":"Bitter Balebrew Charm","icon":"inv_drink_04","quality":4,"tooltipP arams":{}},"mainHand":{"id":61493,"name":"Question ing Axe","icon":"inv_axe_1h_cataclysm_b_01","quality": 2,"tooltipParams":{}},"offHand":{"id":61460,"name" :"Catapult Loading Scoop","icon":"inv_shield_cataclysm_b_02","quality ":2,"tooltipParams":{}},"ranged":{"id":57297,"name ":"Azralon's Twisted Rune","icon":"inv_wand_02","quality":2,"tooltipPar ams":{}}},"stats":{"health":74933,"powerType":"man a","power":13288,"str":2065,"agi":181,"sta":4274," int":105,"spr":108,"attackPower":4359,"rangedAttac kPower":0,"mastery":16.25114,"masteryRating":858," crit":6.930126,"critRating":493,"hitPercent":2.181 862,"hitRating":152,"hasteRating":264,"expertiseRa ting":271,"spellPower":1334,"spellPen":0,"spellCri t":8.355514,"spellCritRating":493,"spellHitPercent ":10.558044,"spellHitRating":152,"mana5":618.0,"ma na5Combat":598.0,"armor":27898,"dodge":7.484168,"d odgeRating":253,"parry":12.470005,"parryRating":82 6,"block":41.5625,"blockRating":0,"resil":0,"mainH andDmgMin":1255.0,"mainHandDmgMax":1638.0,"mainHan dSpeed":2.511,"mainHandDps":576.0032,"mainHandExpe rtise":15,"offHandDmgMin":0.0,"offHandDmgMax":0.0, "offHandSpeed":1.931,"offHandDps":0.0,"offHandExpe rtise":15,"rangedDmgMin":-1.0,"rangedDmgMax":-1.0,"rangedSpeed":-1.0,"rangedDps":-1.0,"rangedCrit":6.930126,"rangedCritRating":493," rangedHitPercent":2.181862,"rangedHitRating":152}, "professions":{"primary":[{"id":186,"name":"Mining","icon":"inv_pick_02","ra nk":495,"max":525,"recipes":[2657,2658,2659,3304,3307,3308,3569,10097,10098,161 53,29356,29358,29359,29360,29361,29686,35750,35751 ,49252,49258,55208,55211,70524,74530,84038]},{"id":773,"name":"Inscription","icon":"inv_inscr iption_tradeskill01","rank":450,"max":525,"recipes ":[45382,48114,48116,48247,48248,50600,50601,50602,50 603,50604,50608,50609,50611,50617,50618,50619,5062 0,52738,52739,52843,53462,56943,56945,56946,56949, 56953,56954,56955,56958,56963,56973,56974,56975,56 976,56987,56988,56999,57007,57008,57020,57021,5702 2,57027,57028,57029,57030,57031,57033,57035,57036, 57113,57117,57127,57128,57129,57130,57133,57156,57 158,57165,57167,57168,57172,57181,57186,57189,5719 0,57194,57195,57196,57197,57199,57200,57201,57214, 57215,57217,57222,57224,57228,57230,57237,57239,57 240,57241,57245,57246,57249,57251,57252,57258,5726 1,57263,57274,57275,57277,57703,57704,57706,57707, 57708,57709,57710,57711,57712,57713,57714,57715,57 716,58286,58287,58289,58296,58297,58298,58299,5830 6,58307,58308,58310,58311,58312,58313,58314,58315, 58317,58318,58320,58321,58323,58324,58326,58327,58 328,58329,58330,58331,58332,58333,58340,58341,5834 2,58344,58345,58472,58478,58480,58482,58483,58486, 58487,58488,58489,58490,58491,59326,59387,59480,59 497,59498,59504,59559,59561,60336,60337,61117,6111 8,61119,61120,61177,61288,64251,64259,64271,64276, 64314,92026]}],"secondary":
<truncated>

---

the API is described here: http://us.battle.net/wow/en/forum/topic/2743690922
with examples here: http://blizzard.github.com/api-wow-docs/

Quote:

---

Originally Posted by Zub

the API is described here: http://us.battle.net/wow/en/forum/topic/2743690922
with examples here: http://blizzard.github.com/api-wow-docs/

---

Thanks for the first link, didn't have that one. The second is in the OP... :)

Quote:

---

Originally Posted by argentina

Thanks for the first link, didn't have that one. The second is in the OP... :)

---

doh /selffacepalm
that's what I get from quickly reading topics while at work lol

Quote:

---

Originally Posted by argentina

In theory, yes, that can be added, but it would really make the script 'loud' (it also adds a whole layer of complexity for me to work out, as the API just gives item ID and name, so I'd have to figure out how to add an additional lookup to work out individual iLvl.)

Per-slot iLvl is a bit of a stretch, but I'll try to figure out how to work this out, maybe with a toggle, so it can be shown, or not, selectively.

---

I tinkered around with the end of the script a bit to have it look up the item id and then the item iLevel. As you mentioned, it requires an extra look up for each item. I'm not sure if there is a cleaner way to do it or not.

Code:

---

  //get item ids and item information
  var headId = toon.items.head.id;
  var headItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+headId)
  var headItem = Utilities.jsonParse(headItemJSON.getContentText());
 
  var neckId = toon.items.neck.id;
  var neckItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+neckId)
  var neckItem = Utilities.jsonParse(neckItemJSON.getContentText());
 
  var shoulderId = toon.items.shoulder.id;
  var shoulderItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+shoulderId)
  var shoulderItem = Utilities.jsonParse(shoulderItemJSON.getContentText());
 
  var backId = toon.items.back.id;
  var backItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+backId)
  var backItem = Utilities.jsonParse(backItemJSON.getContentText()); 
 
  var chestId = toon.items.chest.id;
  var chestItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+chestId)
  var chestItem = Utilities.jsonParse(chestItemJSON.getContentText());
 
  var wristId = toon.items.wrist.id;
  var wristItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+wristId)
  var wristItem = Utilities.jsonParse(wristItemJSON.getContentText()); 
 
  var handsId = toon.items.hands.id;
  var handsItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+handsId)
  var handsItem = Utilities.jsonParse(handsItemJSON.getContentText()); 

  var waistId = toon.items.waist.id;
  var waistItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+waistId)
  var waistItem = Utilities.jsonParse(waistItemJSON.getContentText());

  var legsId = toon.items.legs.id;
  var legsItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+legsId)
  var legsItem = Utilities.jsonParse(legsItemJSON.getContentText());
 
  var feetId = toon.items.feet.id;
  var feetItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+feetId)
  var feetItem = Utilities.jsonParse(feetItemJSON.getContentText()); 

  var finger1Id = toon.items.finger1.id;
  var finger1ItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+finger1Id)
  var finger1Item = Utilities.jsonParse(finger1ItemJSON.getContentText());   

  var finger2Id = toon.items.finger2.id;
  var finger2ItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+finger2Id)
  var finger2Item = Utilities.jsonParse(finger2ItemJSON.getContentText()); 
 
  var trinket1Id = toon.items.trinket1.id;
  var trinket1ItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+trinket1Id)
  var trinket1Item = Utilities.jsonParse(trinket1ItemJSON.getContentText()); 

  var trinket2Id = toon.items.trinket2.id;
  var trinket2ItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+trinket2Id)
  var trinket2Item = Utilities.jsonParse(trinket2ItemJSON.getContentText()); 
 
  var mainHandId = toon.items.mainHand.id;
  var mainHandItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+mainHandId)
  var mainHandItem = Utilities.jsonParse(mainHandItemJSON.getContentText()); 
 
  var offHandId = toon.items.offHand.id;
  var offHandItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+offHandId)
  var offHandItem = Utilities.jsonParse(offHandItemJSON.getContentText()); 

  var rangedId = toon.items.ranged.id;
  var rangedItemJSON = UrlFetchApp.fetch("us.battle.net/api/wow/item/"+rangedId)
  var rangedItem = Utilities.jsonParse(rangedItemJSON.getContentText()); 
 
  var toonInfo = new Array(
    toon.name,
//    "Race: "+toonRace,
//    "Class: "+toonClass,
    toon.level,
    toon.items.averageItemLevel,
    toon.items.averageItemLevelEquipped,
    headItem.itemLevel,
    neckItem.itemLevel,
    shoulderItem.itemLevel,
    backItem.itemLevel,
    chestItem.itemLevel,
    wristItem.itemLevel,
    handsItem.itemLevel,
    waistItem.itemLevel,
    legsItem.itemLevel,
    feetItem.itemLevel,
    finger1Item.itemLevel,
    finger2Item.itemLevel,
    trinket1Item.itemLevel,
    trinket2Item.itemLevel,
    mainHandItem.itemLevel,
    offHandItem.itemLevel,
    rangedItem.itemLevel
  )

     
//  Logger.log("Name: "+toon.name);
//  Logger.log("Race: "+toonRace);
//  Logger.log("Class: "+toonClass);
//  Logger.log("Level: "+toon.level);
//  Logger.log("iLvl: "+toon.items.averageItemLevel);
//  Logger.log("iLvlE: "+toon.items.averageItemLevelEquipped);
//  Logger.log("Head: "+headItem.itemLevel);

  return toonInfo;

---

How much work would it take me to port these scripts to VBA for excel? Assuming an intermediate level of VBA expertise but no experience at all with the actual pulling of data from a web source.

OP Updated with new version of the basic script, now shows primary professions also.

Any desire for secondary ones? First Aid, Archeology, Cooking and whatever the fourth one is?

I created a second, separate script for per-slot iLvl information, the reasons why it is a second script are below.

Quote:

---

Originally Posted by shadewalker

this looks sweet! Thanks.

can iLevel for all item slots be added?

---

I added a second script with a second function wowi() for this, it is attached to the OP.

Quote:

---

Originally Posted by shadewalker

I tinkered around with the end of the script a bit to have it look up the item id and then the item iLevel. As you mentioned, it requires an extra look up for each item. I'm not sure if there is a cleaner way to do it or not.

---

See the items script for the cleaner way, it's done now.

I recommend against using the items script, it takes forever (near 1 minute) to run and often times out.

It also triggers a ratelimit from google docs itself, because it is doing too many lookups too quickly...

Quote:

---

Originally Posted by luxlunae

How much work would it take me to port these scripts to VBA for excel? Assuming an intermediate level of VBA expertise but no experience at all with the actual pulling of data from a web source.

---

This I don't know how to answer, alas. I would imagine it is not difficult... For context, I never worked with js before this script and it wasn't really difficult (outside the loop for the items, the rest was quite straight forward...)

Thanks for the update. I personally don't need the secondary ones, Not sure how others feel. Once again, awesome work.

Stephen

Quote:

---

Originally Posted by Luxlunae

How much work would it take me to port these scripts to VBA for excel? Assuming an intermediate level of VBA expertise but no experience at all with the actual pulling of data from a web source.

---

Not difficult. I used WinHttp, activated from VBA, to get the armory data from Blizzard server, and the rest are just a matter of extracting the keys and values, either from external JSON parser, or in my case, used string manipulation.

Attachment 593

Timeout from us.battle.net is not uncommon, especially when getting the item data.

Code:

---

Dim WinHttpReq As WinHttp.WinHttpRequest
Dim txtURL As Variant
Dim LastCol As Long


' Create an instance of the WinHTTPRequest ActiveX object.
Set WinHttpReq = New WinHttpRequest


' get last column
With ActiveSheet
    LastCol = .Cells(6, .Columns.Count).End(xlToLeft).Column
End With


curRow = 7
Do Until Len(Cells(curRow, 2)) = 0      ' blank realm cell indicates end of last row


    strRealm = ActiveSheet.Cells(curRow, 2)
    strToonName = ActiveSheet.Cells(curRow, 3)
    txtURL = "http://us.battle.net/api/wow/character/" & strRealm & "/" & strToonName & "?fields=guild,items"


    ' Assemble an HTTP Request.
    WinHttpReq.Open "GET", txtURL, False
    ' Send the HTTP Request.
    WinHttpReq.Send


    ' assign server response to sText
    sText = WinHttpReq.ResponseText

    ........

Loop

End Sub

---

I'm here looking for ideas for projects, what sort of features would you want in a software application that incorporates a style similar to this script? What is the purpose that you use this spreadsheet, could it be made easier with a tailor-made piece of software?

I've been working on an API browser, but with limited actual functionality on what to -DO- with the data, so, if I could in any way gain some ideas on some features that'd make things easier for people like you, throw them at me.

I'd appreciate the feedback. :3

Hey folks. Any chance OP could update attachment links? I get invalid for both.

Scratch that, the txt files work.

I know this thread is kind of old but I'm just now discovering it and thought I could get some advice.

We were interested in tracking some caps for iLVL and thus started the following spreadsheet:
https://docs.google.com/spreadsheet/...M195bWc#gid=33

So my question for the OP (or anyone else) is was this script intended to be run once for a static capture via cut and paste into another sheet? The problem is leaving the function in the sheet makes is constantly update and therefore break from excessive execution (Google's limit).

Does anyone have a suggestion of how I could maybe tie the function to a button that would run only manually? Would it be better to hardcode the player roster into the script and tie it to a button that way? I know it's a little above and beyond the intent of the script but I thought enhancing our spreadsheet could help others guilds or players in the same boat.

Thanks in advance for any help or suggestions.