These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Need API Skill Help

First post
Author
Previous Topic Next Topic
Jeremy Kamira
#1 - 2014-11-29 11:56:19 UTC
I am a bit new to this sort of thing, so sorry if im friggen up somewhere.

This is all through Google Excel by the way

I am trying to collect the list of skills and the skill id's from the eve api, this is what i have so far, the two URL's are side by side

=ImportXML("https://api.eveonline.com/char/charactersheet.xml.aspx?vcode="&B5&"&keyid="&B4&"&characterid="&J4&"","/eveapi/result/rowset/row/@level")

=ImportXML("https://api.eveonline.com/char/charactersheet.xml.aspx?vcode="&B5&"&keyid="&B4&"&characterid="&J4&"","/eveapi/result/rowset/row/@typeid")

So then what i can do is something like

=INDEX (C8:C281, MATCH ([INSERT THE TYPE ID HERE],D8:D281,0) )

and it brings back the skills level. I am basing these equations off of another discontinued spreadsheet i found

However, the skill levels and the skill id's dont match up like i thought they would.

http://i.gyazo.com/8693b44b0fc90e1a66909076de9c793f.png

Half of those type id's dont even exist, and if they do, the skill level is wrong. Somebody know where i went wrong here?


I can link you the actual spreadsheet if you think you can help, i am really confused.
Jeremy Kamira
#2 - 2014-11-29 18:40:44 UTC
Figured out what was wrong, i had to move the left column down by a bit and everything lined up. I don't really understand why this is, would somebody mind explaining it to me?

http://i.gyazo.com/d9d62a7016b5246f1509ab253e2e579a.png
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#3 - 2014-11-29 19:21:10 UTC  |  Edited by: Steve Ronuken
In general, I'd recommend not using import xml with google sheets. It's fine for pulling a single value out, but when you want to pull multiple, it gets a bit iffy. It's also become a little flaky recently.

However, Google does provide a different way to load xml data. It's a touch more complicated, but not much more.
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/sheetLoader.gs is the beginnings of a sheet loader.

Right now, it just pulls the skills into list. But it'd be simple enough to expand so it also pulls other things from the sheet.




As for why you had a problem, it'll be because there's more than one rowset in there. So they can conflict with each other. Probably.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
663
#4 - 2014-11-29 20:57:13 UTC  |  Edited by: 663
I found Chribba has a nice list of typeIDs..

Does not seem to be comma delimited though, so copy and paste into a spreadsheet will need some string parsing before you do.

Edit: I think I know why the "typeid" and "level" don't match up. importXML() does not pull the rows specified by the xpath in order, like you think it might. It's quite out of order each time you do it, so the "typeid" and "level" fetches are not in the same order at all.

Either you need to do a fetch per cell in the column, like:
//rowset/row[@queueposition=0]/@typeid"


.. and then base the queueposition off row().. or try to use the code that Steve has posted. I am still trying to understand how it works so I can't comment much on that yet.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2014-11-29 21:00:12 UTC
http://www.fuzzwork.co.uk/resources/typeids.csv is one I keep up to date with each release.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jeremy Kamira
#6 - 2014-11-29 22:04:28 UTC
"As for why you had a problem, it'll be because there's more than one rowset in there. So they can conflict with each other. Probably."

I learned a bit more about xpaths and i realized this, so then what i tried to get the exact value of my skill is this


=ImportXML("https://api.eveonline.com/char/charactersheet.xml.aspx?vcode="&B3&"&keyid="&B2&"&characterid="&B4&"","eveapi/result/rowset/row[@typeID="3300"]//@level")


I went onto http://www.xpathtester.com/xpath and tested it, and when i tested rowset/row[@typeID="3300"]//@level against the rowset of my skills, i got 5 (Which is correct).

However, when i insert the formula into the spreadsheet, it returns a parse error, any ideas?
663
#7 - 2014-11-29 22:12:16 UTC  |  Edited by: 663
-Change the double quotes to single quotes, or no quotes at all since it's a number
-Change typeID to all lowercase
-You can just use //row[@typeid=3300]/@level

Edit: grr, my phone!
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#8 - 2014-11-29 22:14:08 UTC
Jeremy Kamira wrote:
"As for why you had a problem, it'll be because there's more than one rowset in there. So they can conflict with each other. Probably."

I learned a bit more about xpaths and i realized this, so then what i tried to get the exact value of my skill is this


=ImportXML("https://api.eveonline.com/char/charactersheet.xml.aspx?vcode="&B3&"&keyid="&B2&"&characterid="&B4&"","eveapi/result/rowset/row[@typeID="3300"]//@level")


I went onto http://www.xpathtester.com/xpath and tested it, and when i tested rowset/row[@typeID="3300"]//@level against the rowset of my skills, i got 5 (Which is correct).

However, when i insert the formula into the spreadsheet, it returns a parse error, any ideas?



the problem is the double quotes round 3300. They're closing the quote which started at eveapi. change them to single quotes, or get rid of them, and it'll probably work.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Jeremy Kamira
#9 - 2014-11-29 22:29:33 UTC
663 wrote:
-Change the double quotes to single quotes, or no quotes at all since it's a number
-Change typeID to all lowercase
-You can just use //row[@typeid=3300]/@level

Edit: grr, my phone!



eveapi/result/rowset//row[@typeid=3300]/@level")

Works! Thank you so much
Hel O'Ween
Men On A Mission
#10 - 2014-11-30 16:32:36 UTC
Jeremy Kamira wrote:

I went onto http://www.xpathtester.com/xpath and tested it, and when i tested rowset/row[@typeID="3300"]//@level against the rowset of my skills, i got 5 (Which is correct).

However, when i insert the formula into the spreadsheet, it returns a parse error, any ideas?


That might be because although XPath is case sensitive, Google's implementation is not and Google docs requires all node & attribute names to be lowercase.

EVEWalletAware - an offline wallet manager.