Google Docs script for pulling character information from the wow API

[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.

[Destahd]

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.

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.

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...

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...)

[F9thRet]

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

Stephen

[Cptan]

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.

wow armory.jpg

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

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

[HydraCoder]

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

[JRKy]

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

Scratch that, the txt files work.

[JRKy]

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.