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

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

Science & Industry

 
  • Topic is locked indefinitely.
 

XML gurus help me fix my spreadsheet and win EVE

First post
Author
Previous Topic Next Topic
Haffsol
#1 - 2015-05-03 22:59:00 UTC
I was trying to fix all my old spreadsheets with the new ore composition and all the other gory changes to compression and what not

Problems: I always used eve-central but I don't know how to make calls in a range of typeids. I found a workaround that is using eve-marketdata but I've seen that its db can return awkward results. As we speak now for instance, according to eve-marketdata there are 2 people selling arkonor at 7.000 in Jita!! Reality is: ark in jita is around 14k and there's plenty, as eve-central knows very well. And it's not an occasional glitch, I've found several prices not responding to the truth.

So, how do I make calls in a range of ids using eve-central? Or can I fix eve-marketdata imports?

These were the 2 strings I was trying to work with, as you see the syntax is not the same and I'm no guru in API nor xml at all:

eve-central (1 item, arkonor here):
=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=22","/evec_api/marketstat/type/sell/min")

eve-marketdata (40 items, listed by typeids from A2 to A42):
=ImportXML("http://eve-marketdata.com/api/item_prices_jita.xml?type_ids="&JOIN(",",$A2:$A42), "/eve/price")
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2015-05-04 12:19:19 UTC
Soldarius
Dreddit
Test Alliance Please Ignore
#3 - 2015-05-04 15:29:05 UTC
googlesheets XML import is very buggy. For one a few dozen items its usually fine. But once you start going for large lists it frequently breaks. Steve's code works great and is easy to implement. But if you're looking for a quick fix, eve-central's api works the same as eve-market's. Instead of explicitly declaring the typeids use a comma-separated join pointing to the range of ids. 

=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN(",",$A2:$A42),"/evec_api/marketstat/type/sell/min")


http://youtu.be/YVkUvmDQ3HY
Haffsol
#4 - 2015-05-04 16:17:23 UTC
Steve Ronuken wrote:
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs

may be of use.

I guess so. Gimme some time to decipher it and to learn how to implement it but so far so good :)

Soldarius wrote:
googlesheets XML import is very buggy. For one a few dozen items its usually fine. But once you start going for large lists it frequently breaks. Steve's code works great and is easy to implement. But if you're looking for a quick fix, eve-central's api works the same as eve-market's. Instead of explicitly declaring the typeids use a comma-separated join pointing to the range of ids.

=ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&JOIN(",",$A2:$A42),"/evec_api/marketstat/type/sell/min")

I tried to work out the same string you posted yesterday but always had ERROR in return but I might have mispelled a comma or something since now it works and that's all I need for the moment.

I still have to figure out if it's googlesheet to be buggy (for sure it's not perfect) or if it's eve-central API system to have some kind of delay or protection against remote call spamming (there must be a technical name for such a thing, don't ask me), but while after few minutes of "Loading..." I was finally able to fetch the prices from A2 to A42, all the others in the sheet went #N/A. Anyway, one step at a time.

Thanks a lot both of you
Haffsol
#5 - 2015-05-04 19:11:48 UTC  |  Edited by: Haffsol
For the record, I left the ss open for a couple of hrs, now I'm back to the pc and it's all #N/A, can't get around it in any way.

Buggy thing is buggy
Zad Murrard
Frozen Dawn Inc
Frozen Dawn Alliance
#6 - 2015-05-06 06:58:36 UTC
If you want to keep using your spreadsheets but with minimal changes
you can try importXML2. eg. http://pastebin.com/gfUn8j8z

Replace all occurances of importXML with importXML2

eg.
ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=22","/evec_api/marketstat/type/sell/min")

with

ImportXML2("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=22","/evec_api/marketstat/type/sell/min")

And so on. Should work much better though still might run into some temporary errors due to google/endpoint.
Worth noting is that google's importXML wants the final parameter to always be in small letters, importXML2 wants it to be in the format that it is in the returned xml.

eg.
With importXML you might have final parameter ""//row/@typeid", with importXML2 it should be ""//row/@typeID".
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2015-05-06 11:59:25 UTC
Zad Murrard wrote:
If you want to keep using your spreadsheets but with minimal changes
you can try importXML2. eg. http://pastebin.com/gfUn8j8z

Replace all occurances of importXML with importXML2

eg.
ImportXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=22","/evec_api/marketstat/type/sell/min")

with

ImportXML2("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=22","/evec_api/marketstat/type/sell/min")

And so on. Should work much better though still might run into some temporary errors due to google/endpoint.
Worth noting is that google's importXML wants the final parameter to always be in small letters, importXML2 wants it to be in the format that it is in the returned xml.

eg.
With importXML you might have final parameter ""//row/@typeid", with importXML2 it should be ""//row/@typeID".



Actually, they've fixed the case thing now. (breaking a bunch of sheets)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Haffsol
#8 - 2015-05-06 18:23:45 UTC
Looks neat but how can I implement a new function or even a script in my google sheet?
Zad Murrard
Frozen Dawn Inc
Frozen Dawn Alliance
#9 - 2015-05-06 19:15:14 UTC
Tools - Script editor, copy & paste