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.
 

Googledocs API ImportXML noob question

Author
Hustomte
The Scope
#1 - 2013-04-12 01:16:44 UTC
Hello Forums,
I tried searching for my issue first, with no luck.

So here it goes.
Since I am new to the Eve API, and to Googledocs spreadsheets, I am trying to make something simple, but its turning out to be more complicated than I thought.

I simply want to pull ship kills in Jita.
I have tried many different forms of:
=ImportXML("https://api.eveonline.com/map/Kills.xml.aspx?solarSystemID=30000142", "//@shipKills")

What is irritating is that I can browse to https://api.eveonline.com/map/Kills.xml.aspx and search for 30000142 and see how many shipKills it has, but to get googledocs to find it for me seems impossible. Cry

I tried reading:
http://www.w3schools.com/xpath/xpath_syntax.asp
and
http://wiki.eve-id.net/APIv2_Map_Kills_XML

So far no luck, anyone have any ideas?
Thanks in advance Smile

...Signature...

Hustomte
The Scope
#2 - 2013-04-12 06:09:58 UTC
Nevermind, I solved it after searching for clues in various forum posts.

The above solution for shipkills in a system was:

=ImportXML("https://api.eveonline.com/map/Kills.xml.aspx", "sum(//row[@solarsystemid=30000142]/@shipkills)")

This now returns the proper ship-kills value for the solar system id. Big smile

...Signature...

Hustomte
The Scope
#3 - 2013-04-12 06:36:48 UTC
Actually, how would I do multiple systems for LOLz? Big smile

=ImportXML("https://api.eveonline.com/map/Kills.xml.aspx", "sum(//row[@solarsystemid="&JOIN("@solarsystemid=",$A2:$A9)]/@shipkills)")

Do I have JOIN before the xpath or in it like above? (yes my quotes are all messed up and not working) Ugh

...Signature...

Hustomte
The Scope
#4 - 2013-04-14 07:10:50 UTC
I have come to the conclusion that Eve's API is not xpath compliant (or not useful anyway). Eve-Central (and the rest) have a much easier xpath to work with in Googledocs.

Does anyone know if Crest is going to make Eve's API not be such a pain?
I am seriously bummed that I have to ping the API for each solar system I want to look up, instead of just once. This load cannot be good on the server.

...Signature...

Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2013-04-14 15:23:03 UTC
Eve's API is entirely xpath compliant. Unfortunately, the way it's laid out means dealing with things is a bit of a pain (attributes, rather than values)

The syntax for multiple attribute checks is [@solarsystemid=1 or @solarsystemid=2] so you almost had it.
sum(//row[@solarsystemid="&JOIN(" or @solarsystemid=",$A2:$A9)]/@shipkills

should work.


As for CREST, it's not going to, at least initially, change the older api. It's just going to supplement it. The return type will be, I believe, JSON, rather than XML. And I'm not sure how easy it'll be to use with googledocs. Probably hard, due to changing result ids.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Hustomte
The Scope
#6 - 2013-04-14 18:35:45 UTC
Thank you Steve for the feedback, I get a parse error though, plus it was mad at me for not matching quotes.
I think this is really close though, is there more documentation somewhere that I haven't been able to find yet?

...Signature...

Hustomte
The Scope
#7 - 2013-04-30 02:44:17 UTC
bump for great justice

...Signature...

Hustomte
The Scope
#8 - 2013-05-21 21:25:50 UTC
I am still hoping someone in the community knows how to fix this please.

...Signature...

Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#9 - 2013-05-21 22:09:34 UTC  |  Edited by: Steve Ronuken
=ImportXML("https://api.eveonline.com/map/Kills.xml.aspx", "sum(//row[@solarsystemid="&JOIN(" or @solarsystemid=",$A26:$A28)&"]/@shipkills)")

Yes, I screwed up the syntax a little. Teachs me to not test something. Blink

https://docs.google.com/spreadsheet/ccc?key=0As88qKwn3d59dFZCeS1JblR3VlFLNkpPSnpMTTZZc2c&usp=sharing

I'd have answered earlier, but didn't see this had updated Sad

WTB forum notifications.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Hustomte
The Scope
#10 - 2013-05-29 22:21:22 UTC
That is very close to what I wanted, but it sums all the systems into one cell (instead of listing ship kills per hour for each system).

Here is what I have currently (that is inefficient but working)
http://bit.ly/117s0BC

Thank you again for your awesome feedback and help!

...Signature...

Hustomte
The Scope
#11 - 2013-06-14 03:33:24 UTC
Anyone know how to fix this?

...Signature...

Hustomte
The Scope
#12 - 2013-07-26 06:03:41 UTC
Do I need to start a new thread for this? Cry

...Signature...

Omega Flames
Caldari Provisions
Caldari State
#13 - 2013-07-26 13:08:22 UTC
try wrapping the entire import in ARRAYFORMULA()
Hustomte
The Scope
#14 - 2013-08-16 06:18:48 UTC
Omega Flames wrote:
try wrapping the entire import in ARRAYFORMULA()


I tried using:
Quote:
=ARRAYFORMULA(ImportXML("https://api.eveonline.com/map/Kills.xml.aspx", "(//row[@solarsystemid="&JOIN(" or @solarsystemid=",$A2:$A9)&"]/@shipkills)"))


While it does import information, it is not in the order that A2 to A9 would align with, so Jita for example would have its hourly kills listed somewhere else down the list instead of next to its system-id.

I am not sure where its broken, but at least its one-line that does pull information (just incorrectly).

...Signature...

Fubar
Eagle Eye Inc.
#15 - 2013-08-16 16:35:41 UTC
We have two problems with this issue

  1. The Eve API returns the data in different system order for each call.

  2. Google docs returns data in the order that if finds the solarsystemid in the XML data.

Therefore the kills per system will be in random order every time.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#16 - 2013-08-16 20:56:00 UTC  |  Edited by: Steve Ronuken
=importdata("https://www.fuzzwork.co.uk/api/kills.php")

Should do what you want.

It'll pull all the data. (I've set it up so it should match the caching of the eve api)

It'll pull all of the data, in a single line per system, comma seperated. importdata, with continues, will fill out a sheet with all of it.

You can then vlookup it.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter