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.
 

Google Spreadsheet API Import

First post
Author
Previous Topic Next Topic
Offspring Doshu
Sajuuk-Kharr
#1 - 2014-11-07 13:17:55 UTC
Hi there guys, maybe you can help me with a problem I keep running into.

The following is a formula I use to import prices from EveCentral in a google spreadsheet:

=importxml((concatenate("http://api.eve-central.com/api/marketstat?usesystem=30000142","&typeid="&Join("&typeid=",$B3:$B27))),"//buy/max")

Where B3:B27 is the column with the typeIDs

The formula works flawless, and basically allows a single pull per 25 items. Now, the problem I am running into is that totally random it stops working. It either returns the error "#N/A" or just gets stuck on "Loading" without change. It's also worth mentioning that if I make a copy of the spreadsheet while it's in this state, that one will work right away.

Does anyone have any idea what the issue might be?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#2 - 2014-11-07 13:26:46 UTC
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs

importXML is bad, and getting worse as time goes on.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Offspring Doshu
Sajuuk-Kharr
#3 - 2014-11-07 14:01:37 UTC  |  Edited by: Offspring Doshu
Steve Ronuken wrote:
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs

importXML is bad, and getting worse as time goes on.


Thanks for the prompt response I really need to start reading into scrips and vba. I don't understand the specific commands in your script but I do see where you clean the typeids to get a proper sorted list. I also see the part where you basically make a call for each typeid with all the info (buymax,min, sellmax,min etc).

Isn't this basically the same with the change that the import only happens when you turn the script on? Won't the script be problematic with thousands of items (if I remember correctly EveCentral has a limit to the number of calls you are allowed to make)? If I want multiple regions, systems stations I guess I should get a sheet for each?

Until now, I got the item volumes history (daily units sold as seen in game) from eve-merketdata. I understand that you can now get it from CREST?
Soldarius
Dreddit
Test Alliance Please Ignore
#4 - 2014-11-07 18:01:47 UTC
I agree with Steve. importXML is bad and getting worse.

=importxml((concatenate("http://api.eve-central.com/api/marketstat?usesystem=30000142","&typeid="&Join("&typeid=",$B3:$B27))),"//buy/max")


You made a couple errors. First, you have an extra open parenthesis after "importxml". Second, you mixed your commas and ampersands in the concatenate function. In concatenate, all terms must be separated by commas. Alternatively, you can simply omit the concatenate and use ampersands to force concatenation in-line. It could read:

=importxml("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&Join(",",$B3:$B27),"//buy/max")


Eve-central's API takes comma separated lists as input for typeIDs.

http://youtu.be/YVkUvmDQ3HY
Offspring Doshu
Sajuuk-Kharr
#5 - 2014-11-07 19:23:26 UTC
Soldarius wrote:
I agree with Steve. importXML is bad and getting worse.

=importxml((concatenate("http://api.eve-central.com/api/marketstat?usesystem=30000142","&typeid="&Join("&typeid=",$B3:$B27))),"//buy/max")


You made a couple errors. First, you have an extra open parenthesis after "importxml". Second, you mixed your commas and ampersands in the concatenate function. In concatenate, all terms must be separated by commas. Alternatively, you can simply omit the concatenate and use ampersands to force concatenation in-line. It could read:

=importxml("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid="&Join(",",$B3:$B27),"//buy/max")


Eve-central's API takes comma separated lists as input for typeIDs.



Oh,thanks for the input but the formula in the thread is just an example since the real one uses cell reference and I had to make it understandable for my point.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#6 - 2014-11-07 22:18:52 UTC
Offspring Doshu wrote:
Steve Ronuken wrote:
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs

importXML is bad, and getting worse as time goes on.


Thanks for the prompt response I really need to start reading into scrips and vba. I don't understand the specific commands in your script but I do see where you clean the typeids to get a proper sorted list. I also see the part where you basically make a call for each typeid with all the info (buymax,min, sellmax,min etc).

Isn't this basically the same with the change that the import only happens when you turn the script on? Won't the script be problematic with thousands of items (if I remember correctly EveCentral has a limit to the number of calls you are allowed to make)? If I want multiple regions, systems stations I guess I should get a sheet for each?

Until now, I got the item volumes history (daily units sold as seen in game) from eve-merketdata. I understand that you can now get it from CREST?



It would be problematic with thousands of items. you could, if you wanted, extend it so it goes through the list in chunks below the limit of evecentral


Doing it one sheet per region is probably wisest, to avoid the issue of large results sets overwriting each other.

Yes, it's possible with crest, but it's one lookup per item (getting full history)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Offspring Doshu
Sajuuk-Kharr
#7 - 2014-11-07 22:24:59 UTC
Steve Ronuken wrote:
Offspring Doshu wrote:
Steve Ronuken wrote:
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs

importXML is bad, and getting worse as time goes on.


Thanks for the prompt response I really need to start reading into scrips and vba. I don't understand the specific commands in your script but I do see where you clean the typeids to get a proper sorted list. I also see the part where you basically make a call for each typeid with all the info (buymax,min, sellmax,min etc).

Isn't this basically the same with the change that the import only happens when you turn the script on? Won't the script be problematic with thousands of items (if I remember correctly EveCentral has a limit to the number of calls you are allowed to make)? If I want multiple regions, systems stations I guess I should get a sheet for each?

Until now, I got the item volumes history (daily units sold as seen in game) from eve-merketdata. I understand that you can now get it from CREST?



It would be problematic with thousands of items. you could, if you wanted, extend it so it goes through the list in chunks below the limit of evecentral


Doing it one sheet per region is probably wisest, to avoid the issue of large results sets overwriting each other.

Yes, it's possible with crest, but it's one lookup per item (getting full history)



Do you happen to know the restriction eve central imposes?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#8 - 2014-11-07 22:27:46 UTC
Afraid not.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Offspring Doshu
Sajuuk-Kharr
#9 - 2014-11-07 22:30:36 UTC
Steve Ronuken wrote:
Afraid not.


Either way, I'll experiment now. Thanks a lot for the help :D