Pages: 1 2 :: [one page] |
|
Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Shahrokh Dariush
Conspicuous Trading Company
7
|
Posted - 2014.07.26 19:50:00 -
[1] - Quote
Hello!
TL;DR Is there any way to control how often you pull data using importXML in google docs, without first pulling it to a proxy which only pulls once per hour?
In desperation I'm turning to the EVE tech forum :) I recently moved my offline spreadsheet to a google doc spreadsheet, to make use of this guys excellent work: http://eve-prosper.blogspot.se/2014/07/building-better-spreadsheets-crius.html mainly to get a hold of system index costs.
After that I decided to increase automation by importing prices from the eve-central API, by using 3 ImportXML calls, about 50 typeids each. This worked fine for a day or so, but today I have run into constant problems, and I fear I am getting locked out for not controlling how often my document pulls the data. For the past 4 hours, around ~:30 every hour, it stops working and only yields #N/A. I can get it working again by removing or adding 1 typeid, but one hour later, it again ceases to work. To me, this smells awfully lot like I'm being blocked for calling it too often (I have no clue how often Google Docs pulls it).
I have searched and searched for a solution and the only one seems to point to having another server/proxy set up, with a script, that pulls the data every hour, and caches a copy, which is then referenced to the spreadsheet.
Is there ANY other solution to my problem?
Would dearly appreciate any help you guys can offer! |
Shahrokh Dariush
Conspicuous Trading Company
8
|
Posted - 2014.07.26 19:50:50 -
[2] - Quote
Hello!
TL;DR Is there any way to control how often you pull data using importXML in google docs, without first pulling it to a proxy which only pulls once per hour?
In desperation I'm turning to the EVE tech forum :) I recently moved my offline spreadsheet to a google doc spreadsheet, to make use of this guys excellent work: http://eve-prosper.blogspot.se/2014/07/building-better-spreadsheets-crius.html mainly to get a hold of system index costs.
After that I decided to increase automation by importing prices from the eve-central API, by using 3 ImportXML calls, about 50 typeids each. This worked fine for a day or so, but today I have run into constant problems, and I fear I am getting locked out for not controlling how often my document pulls the data. For the past 4 hours, around ~:30 every hour, it stops working and only yields #N/A. I can get it working again by removing or adding 1 typeid, but one hour later, it again ceases to work.
I have searched and searched for a solution and the only one seems to point to having another server/proxy set up, with a script, that pulls the data every hour, and caches a copy, which is then referenced to the spreadsheet.
Is there ANY other solution to my problem?
Edit: After leaving it alone for ~30 mins, it was able to import with the original set of typeid's again, so I guess it's not a permanent lock out. Edit2: Or is this simply an overload issue, by too many people requesting data on the hour (e.g. 19:00 or 19:30)?
Would dearly appreciate any help you guys can offer! |
Jocobo Terisi
Perkone Caldari State
0
|
Posted - 2014.07.28 00:46:00 -
[3] - Quote
I'm running into the same issue myself and I have significantly more than three ImportXML calls. On some occasions it works great, while on others it hangs for hours. A serious bummer when I'm trying to get anything done.
I thought it was tied into some QUERY functions, but the problem reappeared even after I shifted them out. |
Jocobo Terisi
Perkone Caldari State
0
|
Posted - 2014.07.28 00:46:31 -
[4] - Quote
I'm running into the same issue myself and I have significantly more than three ImportXML calls. On some occasions it works great, while on others it hangs for hours. A serious bummer when I'm trying to get anything done.
I thought it was tied into some QUERY functions, but the problem reappeared even after I shifted them out. |
Shahrokh Dariush
Conspicuous Trading Company
7
|
Posted - 2014.07.28 06:35:00 -
[5] - Quote
Yeah, manually refreshing even three is annoying so I feel for you. I'd say that for Sunday it worked about 50% of the time. Some hours it worked fine, other times it broke like clockwork. I'll keep an eye on it today, and see if it breaks less frequently, which might indicate that the problem is load related (and thus not on our end...). |
Shahrokh Dariush
Conspicuous Trading Company
8
|
Posted - 2014.07.28 06:35:23 -
[6] - Quote
Yeah, manually refreshing even three is annoying so I feel for you. I'd say that for Sunday it worked about 50% of the time. Some hours it worked fine, other times it broke like clockwork. I'll keep an eye on it today, and see if it breaks less frequently, which might indicate that the problem is load related (and thus not on our end...). |
Richard Strohker
Brave Newbies Inc. Brave Collective
1
|
Posted - 2014.08.01 02:31:00 -
[7] - Quote
I too have been having this issue--on my own google docs spreadsheet, and on another from my corp. It happened to me constantly as I was building the sheet over the past few days (a learning process for me as I'm new to it), whether it was ~7 typeID's or 50+, but as you say it really seems to vary over time. |
Richard Strohker
Brave Newbies Inc. Brave Collective
1
|
Posted - 2014.08.01 02:31:21 -
[8] - Quote
I too have been having this issue--on my own google docs spreadsheet, and on another from my corp. It happened to me constantly as I was building the sheet over the past few days (a learning process for me as I'm new to it), whether it was ~7 typeID's or 50+, but as you say it really seems to vary over time. |
Shahrokh Dariush
Conspicuous Trading Company
7
|
Posted - 2014.08.01 05:11:00 -
[9] - Quote
Yeah I had zero issues on monday or tuesday. A few on wednesday and some on thursday. I expect it will be broken a lot come this weekend. |
Shahrokh Dariush
Conspicuous Trading Company
8
|
Posted - 2014.08.01 05:11:09 -
[10] - Quote
Yeah I had zero issues on monday or tuesday. A few on wednesday and some on thursday. I expect it will be broken a lot come this weekend. |
|
Richard Strohker
Brave Newbies Inc. Brave Collective
1
|
Posted - 2014.08.01 06:36:00 -
[11] - Quote
In the meantime I set up my sheet so I can just copy the price data to a backup column and use that in the event of this error occurring. Better to have slightly old data than none at all, I figure. |
Richard Strohker
Brave Newbies Inc. Brave Collective
1
|
Posted - 2014.08.01 06:36:52 -
[12] - Quote
In the meantime I set up my sheet so I can just copy the price data to a backup column and use that in the event of this error occurring. Better to have slightly old data than none at all, I figure. |
Elisa-Verena Elestair
GAZNOROCK Inc. GANOR INC.
0
|
Posted - 2014.08.02 09:21:00 -
[13] - Quote
That's the reason why I'm getting my eve-central data (many many hundrets, if not thousands of items) though Excel and importing that data into google sheets. There I built a special sheet to gather the data of each update, so I can see the price-history related on my updates and build different average prices (overall price avg. 90-days-avg., 30-days-avg., last 5 updates avg. .....)
Well it's not working completly automatically, just need to move some data manually, but that's done in half a minute each day ^^
Google docs doesn't have settings for xml-updates. Well maybe some time later, a month, year, decade, who knows :D
Ah and btw. I just tried to import that many data in google sheets, but already got some errors between the data from the beginning. It's strange cause that were just single errors between dozens of data and different xmlimports. That's why I just moved back to Excel to get so many data importet, never had any problems there |
Elisa-Verena Elestair
GAZNOROCK Inc. GANOR INC.
0
|
Posted - 2014.08.02 09:21:55 -
[14] - Quote
That's the reason why I'm getting my eve-central data (many many hundrets, if not thousands of items) though Excel and importing that data into google sheets. There I built a special sheet to gather the data of each update, so I can see the price-history related on my updates and build different average prices (overall price avg. 90-days-avg., 30-days-avg., last 5 updates avg. .....)
Well it's not working completly automatically, just need to move some data manually, but that's done in half a minute each day ^^
Google docs doesn't have settings for xml-updates. Well maybe some time later, a month, year, decade, who knows :D
Ah and btw. I just tried to import that many data in google sheets, but already got some errors between the data from the beginning. It's strange cause that were just single errors between dozens of data and different xmlimports. That's why I just moved back to Excel to get so many data importet, never had any problems there |
Zad Murrard
Pulssi Inc.
13
|
Posted - 2014.09.05 16:22:00 -
[15] - Quote
LIke said in another thread, if using 'new google sheets' the problem is with 'new google sheets'. The importXML function is very unreliable.
The best solution is to write a custom replacement function which does what importXML does.
Works much much better. |
Zad Murrard
Pulssi Inc.
13
|
Posted - 2014.09.05 16:22:46 -
[16] - Quote
LIke said in another thread, if using 'new google sheets' the problem is with 'new google sheets'. The importXML function is very unreliable.
The best solution is to write a custom replacement function which does what importXML does.
Works much much better. |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
3808
|
Posted - 2014.09.05 19:23:00 -
[17] - Quote
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs Woo! CSM 9! http://fuzzwork.enterprises/ Twitter: @fuzzysteve on Twitter |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4133
|
Posted - 2014.09.05 19:23:23 -
[18] - Quote
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Shahrokh Dariush
Conspicuous Trading Company
8
|
Posted - 2014.09.06 11:19:00 -
[19] - Quote
Thanks for the function! It's working out well so far. |
Shahrokh Dariush
Conspicuous Trading Company
8
|
Posted - 2014.09.06 11:19:05 -
[20] - Quote
Thanks for the function! It's working out well so far. |
|
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
3812
|
Posted - 2014.09.06 16:00:00 -
[21] - Quote
It gave me a reason to kick google scripting around :) It's actually pretty simple.
- Zip through the columns and rows of the passed in argument, sticking the values into an array.
- Dedup that array.
- Stick that into the eve central api url and retrieve it
- create an array of type elements
- stick the relevant values into an array for each type, and push that array on to another.
- Return it
Woo! CSM 9! http://fuzzwork.enterprises/ Twitter: @fuzzysteve on Twitter |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4133
|
Posted - 2014.09.06 16:00:24 -
[22] - Quote
It gave me a reason to kick google scripting around :) It's actually pretty simple.
- Zip through the columns and rows of the passed in argument, sticking the values into an array.
- Dedup that array.
- Stick that into the eve central api url and retrieve it
- create an array of type elements
- stick the relevant values into an array for each type, and push that array on to another.
- Return it
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Lilu Egnald
No-Mercy The Unthinkables
0
|
Posted - 2014.09.08 07:38:00 -
[23] - Quote
I get "Error: Reference does not exist." whenever I try ur function. |
Lilu Egnald
No-Mercy The Unthinkables
0
|
Posted - 2014.09.08 07:38:37 -
[24] - Quote
I get "Error: Reference does not exist." whenever I try ur function.
If I manually write the typeID into the range it works.. but if I even copy and paste the numbers into the range it doesn't (or query).
Had to do a *Cell*+0 formula in an adjacent column and it would work. |
Soldarius
Deadman W0nderland The 99 Percent
872
|
Posted - 2014.11.06 16:07:53 -
[25] - Quote
Google Sheets is ****. I've got one sheet that flat out refuses to run anything that involves XML, even with steve's script, which works fine in another sheet.
It just sits there saying "Loading Data..."
RIP my booster production.
"Remember remember the 4th of November!"
Phoebe. Coming soon to Eve Online.
|
Aivlis Eldelbar
Ubuntu Inc. The Fourth District
43
|
Posted - 2014.11.13 21:21:29 -
[26] - Quote
I have the same issues, and now Google is threatening to convert my old-version spreadsheets into new-version ones. I think it may be time to do this the old fashioned way (offline), but it's annoying as hell not being able to keep it online.
|
Argonicus
Wastion Dominion Home Front Coalition
4
|
Posted - 2014.12.23 22:27:28 -
[27] - Quote
I try to use your script. Link to google docs open, but script doesn't work. I'm copy script text, create new script in my google scripts, but if i add function =loadPrices, i got message #Name Who can helps me with this? |
quikfingrs
Salamander Researches And Industries
1
|
Posted - 2014.12.24 09:52:07 -
[28] - Quote
Argonicus wrote:I try to use your script. Link to google docs open, but script doesn't work. I'm copy script text, create new script in my google scripts, but if i add function =loadPrices, i got message #Name Who can helps me with this?
have a gander at the top of steve scirpt and see this part
=loadPrices(A4:A92, 10000002) thats mine his is similer :) jus tneed to read the small print :)
by the way steve is that script of ure able to pull station data or system data other than region? |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4605
|
Posted - 2014.12.24 11:28:20 -
[29] - Quote
quikfingrs wrote:Argonicus wrote:I try to use your script. Link to google docs open, but script doesn't work. I'm copy script text, create new script in my google scripts, but if i add function =loadPrices, i got message #Name Who can helps me with this? have a gander at the top of steve scirpt and see this part =loadPrices(A4:A92, 10000002) thats mine his is similer :) jus tneed to read the small print :) by the way steve is that script of ure able to pull station data or system data other than region?
It's currently set just for region. But that's just due to the url that's being used. you could change it easily enough.
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Cpt Patrick Archer
Quam Singulari Northern Associates.
4
|
Posted - 2014.12.24 13:30:54 -
[30] - Quote
Steve Ronuken wrote:https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs
Awesome, this is what I've been looking for for a while now. Set it up last night, worked perfectly (pulling data for like 200 cells). |
|
Argonicus
Wastion Dominion Home Front Coalition
4
|
Posted - 2014.12.24 20:21:21 -
[31] - Quote
Ok, looks it working for me for now. But I have a question, if I don't need full list of parameters, how i can show only sell max, buy min? I try:
var rows=xml.getRootElement().getChild("marketstat").getChildren("type"); for(var i = 0; i < rows.length; i++) { var price=[rows[i].getAttribute("id").getValue()]; /*rows[i].getChild("buy").getChild("volume").getValue(), rows[i].getChild("buy").getChild("avg").getValue(), rows[i].getChild("buy").getChild("min").getValue(), rows[i].getChild("buy").getChild("stddev").getValue(), rows[i].getChild("buy").getChild("median").getValue(), rows[i].getChild("buy").getChild("percentile").getValue(), rows[i].getChild("sell").getChild("volume").getValue(), rows[i].getChild("sell").getChild("avg").getValue(), rows[i].getChild("sell").getChild("max").getValue(), rows[i].getChild("sell").getChild("stddev").getValue(), rows[i].getChild("sell").getChild("median").getValue(), rows[i].getChild("sell").getChild("percentile").getValue()];*/ rows[i].getChild("sell").getChild("min").getValue(), rows[i].getChild("buy").getChild("max").getValue(), prices.push(price); } } return prices; } but i still get a full list of rows
P.S. After deleting all that in /* */ it works as i need |
quikfingrs
Salamander Researches And Industries
1
|
Posted - 2014.12.24 22:31:01 -
[32] - Quote
Steve Ronuken wrote:quikfingrs wrote:Argonicus wrote:I try to use your script. Link to google docs open, but script doesn't work. I'm copy script text, create new script in my google scripts, but if i add function =loadPrices, i got message #Name Who can helps me with this? have a gander at the top of steve scirpt and see this part =loadPrices(A4:A92, 10000002) thats mine his is similer :) jus tneed to read the small print :) by the way steve is that script of ure able to pull station data or system data other than region? It's currently set just for region. But that's just due to the url that's being used. you could change it easily enough.
i take it ure talking about line 26 ,13, 15?
i havent really go ta clue about code but wha ti have tryd doesnt work how would i edit it to pull jita 4-4 or jita only? |
Argonicus
Wastion Dominion Home Front Coalition
4
|
Posted - 2014.12.24 22:47:39 -
[33] - Quote
If use version of Steve Ronuken it shows prices in jita well Of want i may send full version of my edtition |
quikfingrs
Salamander Researches And Industries
1
|
Posted - 2014.12.25 11:24:14 -
[34] - Quote
yeh the majority of the market is good for jita region wide but some things ar emuch cheaper in the region else where from jita so it can muddle up the correct prices slightly,
that would be nice but tbh id like to understand the code a little aswell to be able to do similer stuf my self if the future :) hate being one of these can u do this for me attitude :), |
Omega Flames
Last Resort Inn
101
|
Posted - 2014.12.25 15:00:51 -
[35] - Quote
Cpt Patrick Archer wrote:Steve Ronuken wrote:https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs Awesome, this is what I've been looking for for a while now. Set it up last night, worked perfectly (pulling data for like 200 cells). Edit: I found that sometimes it stops on "Loading.." and does not pull the data, even after quite a while of waiting. Anyone know the reason for that? Because of this, my spreadsheet does not work on some occasions because it does not show marketdata at all. We've actually been discussing this on the EVE-central google group https://groups.google.com/forum/#!forum/eve-central
below is the relevant info.
Quote:I believe I have figured out the difference. Old version google spreadsheets are not actually attemping to pull any data from your website. The importXML command isn't being processed correctly. If you use the new version of google spreadsheets it will work. Making a copy will not convert an old version spreadsheet to the new version. Quote:https://support.google.com/docs/answer/3544847?hl=en is the current workaround. Google is supposed to one day switch all old version sheets to the new version however since the new version has been out since March 2014 I wouldn't hold your breath on that. Intentional or not the importXML breaking for old version sheets is going to force almost all google docs users to switch themselves as that's our only solution. |
Alphae
Blue Republic RvB - BLUE Republic
4
|
Posted - 2014.12.26 16:12:01 -
[36] - Quote
Hey guys, I'm having the same difficulties as you guys so i tried Steve's script (thank you!) I modified the script just a bit to get the system info instead of the region; I also got rid of rows I didn't want and kept only Buy Volume, Sell Volume, And min Sell. I just changed these parts :
function loadPrices(priceIDs,systemID,cachebuster){ if (typeof systemID == 'undefined'){ systemID=30000142; //Jita }
...
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid=";
...
rows[i].getChild("buy").getChild("volume").getValue(), rows[i].getChild("sell").getChild("volume").getValue(), rows[i].getChild("sell").getChild("min").getValue()];
This is working nicely, but I'm having a couple of issues with the spreadsheet I'm working on :
- I cannot do more than 159 calls with the functions, after that it gives me an #ERROR! : If I use : =loadPrices('trade sheet'!B4:B163,30000142,1) >>> It works If I use : =loadPrices1('trade sheet'!B4:B164,30000142,1) >>> It doesn't
Request failed for http://api.eve-central.com/api/marketstat?cachebuster=1&usesystem=30000142 returned code 414 / Truncated server response: URL length exceeds the configured limit of 2048 characters (Use muteHttpExceptions option to examine full response). (line 29).
line 29 is : var xmlFeed = UrlFetchApp.fetch(url+cleanTypeIds.join("&typeid="), parameters).getContentText();
- I cannot use vlookup to fetch the ID from the "price load" sheet, to get the price sell. It returns an #ERROR! :
I use this formula : =VLOOKUP(B4,price load!A:D,4,FALSE)
B4 : is the Type ID (fetched from another sheet. formula in B4 is : =iferror(vlookup(A4,TypeID!B:C,2,FALSE),"Error") A4 : is the name of the Item on the 'price load' sheet : A is the Type ID, B is buy volume, C is sell volume, and D is sell min
So basically I want the vlookup to check the ID on sheet X, check it on sheet Y and returns the min sell which is in column D. It keeps saying #N/A : Did not find value '22448' in VLOOKUP evaluation.
I tried the same vlookup process on non dynamic numbers, and it works, what am I missing ? Thank you for your help, and I hope I deviat so much from the OP subject !
regards.
|
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4609
|
Posted - 2014.12.26 18:05:17 -
[37] - Quote
quikfingrs wrote:Steve Ronuken wrote:quikfingrs wrote:Argonicus wrote:I try to use your script. Link to google docs open, but script doesn't work. I'm copy script text, create new script in my google scripts, but if i add function =loadPrices, i got message #Name Who can helps me with this? have a gander at the top of steve scirpt and see this part =loadPrices(A4:A92, 10000002) thats mine his is similer :) jus tneed to read the small print :) by the way steve is that script of ure able to pull station data or system data other than region? It's currently set just for region. But that's just due to the url that's being used. you could change it easily enough. i take it ure talking about line 26 ,13, 15? i havent really go ta clue about code but wha ti have tryd doesnt work how would i edit it to pull jita 4-4 or jita only?
on line 37, the url to pull from evecentral is specified.
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"®ionlimit="+regionID+"&typeid=";
if you were to change it to
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+regionID+"&typeid=";
you could then restrict it to Jita, by passing in jita's systemid (30000142).
of course, it's less than ideal, if you don't go and rename all the regionID to systemID but it should work.
(copying the function and renaming it might be wise. just so you can still get region level pricing)
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4609
|
Posted - 2014.12.26 18:18:05 -
[38] - Quote
Alphae wrote:Hey guys, I'm having the same difficulties as you guys so i tried Steve's script (thank you!) I modified the script just a bit to get the system info instead of the region; I also got rid of rows I didn't want and kept only Buy Volume, Sell Volume, And min Sell. I just changed these parts : function loadPrices(priceIDs,systemID,cachebuster){ if (typeof systemID == 'undefined'){ systemID=30000142; //Jita }
...
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid=";
...
rows[i].getChild("buy").getChild("volume").getValue(), rows[i].getChild("sell").getChild("volume").getValue(), rows[i].getChild("sell").getChild("min").getValue()];
This is working nicely, but I'm having a couple of issues with the spreadsheet I'm working on : - I cannot do more than 159 calls with the functions, after that it gives me an #ERROR! : If I use : =loadPrices('trade sheet'!B4:B 163,30000142,1) >>> It works If I use : =loadPrices1('trade sheet'!B4: B164,30000142,1) >>> It doesn't Request failed for http://api.eve-central.com/api/marketstat?cachebuster=1&usesystem=30000142 returned code 414 / Truncated server response: URL length exceeds the configured limit of 2048 characters (Use muteHttpExceptions option to examine full response). (line 29). line 29 is : var xmlFeed = UrlFetchApp.fetch(url+cleanTypeIds.join("&typeid="), parameters).getContentText();
- I cannot use vlookup to fetch the ID from the "price load" sheet, to get the price sell. It returns an #ERROR! : I use this formula : =VLOOKUP(B4,price load!A:D,4,FALSE) B4 : is the Type ID (fetched from another sheet. formula in B4 is : =iferror(vlookup(A4,TypeID!B:C,2,FALSE),"Error") A4 : is the name of the Item on the 'price load' sheet : A is the Type ID, B is buy volume, C is sell volume, and D is sell minSo basically I want the vlookup to check the ID on sheet X, check it on sheet Y and returns the min sell which is in column D. It keeps saying #N/A : Did not find value '22448' in VLOOKUP evaluation. I tried the same vlookup process on non dynamic numbers, and it works, what am I missing ? Thank you for your help, and I hope I deviat so much from the OP subject ! regards.
bah! I wrote a response. Then the forum ate it
I'll rewrite in a while.
Anyway, the 2048 error is as you're trying to pull too much at once.
http://stackoverflow.com/questions/8495687/split-array-into-chunks may work, for splitting it up, into multiple chunks. you want to break up cleanTypeIDs into chunks of, say, 100 entries.
The vlookup may be down to a text/number thing. Do you have a parseInt around the typeid in the function?
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
quikfingrs
Salamander Researches And Industries
1
|
Posted - 2014.12.26 19:58:40 -
[39] - Quote
Steve Ronuken wrote:quikfingrs wrote:Steve Ronuken wrote:quikfingrs wrote:Argonicus wrote:I try to use your script. Link to google docs open, but script doesn't work. I'm copy script text, create new script in my google scripts, but if i add function =loadPrices, i got message #Name Who can helps me with this? have a gander at the top of steve scirpt and see this part =loadPrices(A4:A92, 10000002) thats mine his is similer :) jus tneed to read the small print :) by the way steve is that script of ure able to pull station data or system data other than region? It's currently set just for region. But that's just due to the url that's being used. you could change it easily enough. i take it ure talking about line 26 ,13, 15? i havent really go ta clue about code but wha ti have tryd doesnt work how would i edit it to pull jita 4-4 or jita only? on line 37, the url to pull from evecentral is specified. var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"®ionlimit="+regionID+"&typeid=";
if you were to change it to var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+regionID+"&typeid=";
you could then restrict it to Jita, by passing in jita's systemid (30000142). of course, it's less than ideal, if you don't go and rename all the regionID to systemID but it should work. (copying the function and renaming it might be wise. just so you can still get region level pricing)
Yeh it took me a day to iorn it out but got there in the end but that's exactly what I did including editing all regionid to system id and usesystem, but thanks for the info =ƒÿÇ everyday a learning day lol
I did hit the same thing mentiond about exceeding the url length though lol was going to make a second sheet till I seen the bottom post, but had two ideas if I used the link info above about chunks where would I put that Comand line in ure sheet would it be after the eve central api function ?
The other thing was groupid's does that pull all data of the group and give you all the info of all items in that group or am I reading it wrong ? Cos if it did I could change itemid to groupid's and that would slash the app pull of all 159 items I need by a hell of a lot |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4609
|
Posted - 2014.12.26 21:02:21 -
[40] - Quote
the idea would be to, before line 52, put in
var i,j,temparray,chunk = 100; for (i=0,j=cleanTypeIds.length; i < j; i+=chunk) { temparray = cleanTypeIds.slice(i,i+chunk);
replace the cleanTypeIds on line 52 with temparray
then before the return price; on line 76, add a }
That should do it.
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
|
Omega Flames
Last Resort Inn
101
|
Posted - 2014.12.26 21:44:49 -
[41] - Quote
Alphae wrote:- I cannot use vlookup to fetch the ID from the "price load" sheet, to get the price sell. It returns an #ERROR! : I use this formula : =VLOOKUP(B4,price load!A:D,4,FALSE) B4 : is the Type ID (fetched from another sheet. formula in B4 is : =iferror(vlookup(A4,TypeID!B:C,2,FALSE),"Error") A4 : is the name of the Item on the 'price load' sheet : A is the Type ID, B is buy volume, C is sell volume, and D is sell minSo basically I want the vlookup to check the ID on sheet X, check it on sheet Y and returns the min sell which is in column D. It keeps saying #N/A : Did not find value '22448' in VLOOKUP evaluation. I tried the same vlookup process on non dynamic numbers, and it works, what am I missing ? Thank you for your help, and I hope I deviat so much from the OP subject ! regards. If you are referencing a sheet with a space in the name then you must wrap the name inside quotation marks (it's either ` ' or " I can't remember exactly but one of those 3). It's best to simply keep all your sheet names without spaces in them so just rename price load to PriceLoad instead. |
Kaladr
Brave Newbies Inc. Brave Collective
47
|
Posted - 2014.12.28 05:37:23 -
[42] - Quote
As a follow on to this thread, and watching the API usage, what do people prefer in terms of querying EVE-Central?
The current APIs are ancient (circa 2008), and are rather inconsistent. Also, the filtering is limited.
Would a pseudo-query language for EVE-Central make sense as opposed to just parameters? Is more filtering desirable?
Creator of EVE-Central.com, the longest running EVE Market Aggregator
|
Alphae
Blue Republic RvB - BLUE Republic
4
|
Posted - 2014.12.28 12:02:10 -
[43] - Quote
Quote:bah! I wrote a response. Then the forum ate it I'll rewrite in a while. Anyway, the 2048 error is as you're trying to pull too much at once. http://stackoverflow.com/questions/8495687/split-array-into-chunks may work, for splitting it up, into multiple chunks. you want to break up cleanTypeIDs into chunks of, say, 100 entries. The vlookup may be down to a text/number thing. Do you have a parseInt around the typeid in the function?
I don't have the necessary knowledge to use the split array into chunks method, If you could give me a concrete example ? I'll try digging into it.
The Vlookup thing : I tried to change the format of cells into numbers, text, nothing works. I don't have parseInt around the typeid in the function, i don't know what it is, would that help ?
Quote:If you are referencing a sheet with a space in the name then you must wrap the name inside quotation marks (it's either ` ' or " I can't remember exactly but one of those 3). It's best to simply keep all your sheet names without spaces in them so just rename price load to PriceLoad instead.
I renamed the sheet, it's not working either. |
Omega Flames
Last Resort Inn
101
|
Posted - 2014.12.28 13:45:30 -
[44] - Quote
=VLOOKUP(B4,priceload!A1:D,4,FALSE) |
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4613
|
Posted - 2014.12.28 18:30:25 -
[45] - Quote
Alphae wrote:Quote:bah! I wrote a response. Then the forum ate it I'll rewrite in a while. Anyway, the 2048 error is as you're trying to pull too much at once. http://stackoverflow.com/questions/8495687/split-array-into-chunks may work, for splitting it up, into multiple chunks. you want to break up cleanTypeIDs into chunks of, say, 100 entries. The vlookup may be down to a text/number thing. Do you have a parseInt around the typeid in the function? I don't have the necessary knowledge to use the split array into chunks method, If you could give me a concrete example ? I'll try digging into it. The Vlookup thing : I tried to change the format of cells into numbers, text, nothing works. I don't have parseInt around the typeid in the function, i don't know what it is, would that help ? Quote:If you are referencing a sheet with a space in the name then you must wrap the name inside quotation marks (it's either ` ' or " I can't remember exactly but one of those 3). It's best to simply keep all your sheet names without spaces in them so just rename price load to PriceLoad instead. I renamed the sheet, it's not working either.
https://forums.eveonline.com/default.aspx?g=posts&m=5344850#post5344850
I've not tested it, admittedly, but it should work.
Anyway, with the parseInt bit:
If you look at where it's building up the array of price data to return (line 58 in https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs) try
var price=[parseInt(rows[i].getAttribute("id").getValue()),
as it tells the code to handle it as a number, rather than a bit of text. might make a difference.
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4613
|
Posted - 2014.12.28 21:18:39 -
[46] - Quote
https://docs.google.com/spreadsheets/d/11X4KXNGeW6JCtRODDM4ikq4xvQU_rsAm7Fg-skFgT6M/
is a sheet with all the functions on it, including a copy of the updated evecentral loader. (100 at a time)
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs
I'm seeing if I can get it published as an addon.
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Alphae
Blue Republic RvB - BLUE Republic
4
|
Posted - 2014.12.29 12:36:24 -
[47] - Quote
I love you man, everything seems to work amazingly ! you're the man ! Thank you ! |
Soldarius
Deadman W0nderland The 99 Percent
999
|
Posted - 2014.12.29 17:12:27 -
[48] - Quote
Steve Ronuken wrote:Alphae wrote:Hey guys, I'm having the same difficulties as you guys so i tried Steve's script (thank you!) I modified the script just a bit to get the system info instead of the region; I also got rid of rows I didn't want and kept only Buy Volume, Sell Volume, And min Sell. I just changed these parts : function loadPrices(priceIDs,systemID,cachebuster){ if (typeof systemID == 'undefined'){ systemID=30000142; //Jita }
...
var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid=";
...
rows[i].getChild("buy").getChild("volume").getValue(), rows[i].getChild("sell").getChild("volume").getValue(), rows[i].getChild("sell").getChild("min").getValue()];
This is working nicely, but I'm having a couple of issues with the spreadsheet I'm working on : - I cannot do more than 159 calls with the functions, after that it gives me an #ERROR! : If I use : =loadPrices('trade sheet'!B4:B 163,30000142,1) >>> It works If I use : =loadPrices1('trade sheet'!B4: B164,30000142,1) >>> It doesn't Request failed for http://api.eve-central.com/api/marketstat?cachebuster=1&usesystem=30000142 returned code 414 / Truncated server response: URL length exceeds the configured limit of 2048 characters (Use muteHttpExceptions option to examine full response). (line 29). line 29 is : var xmlFeed = UrlFetchApp.fetch(url+cleanTypeIds.join("&typeid="), parameters).getContentText();
- I cannot use vlookup to fetch the ID from the "price load" sheet, to get the price sell. It returns an #ERROR! : I use this formula : =VLOOKUP(B4,price load!A:D,4,FALSE) B4 : is the Type ID (fetched from another sheet. formula in B4 is : =iferror(vlookup(A4,TypeID!B:C,2,FALSE),"Error") A4 : is the name of the Item on the 'price load' sheet : A is the Type ID, B is buy volume, C is sell volume, and D is sell minSo basically I want the vlookup to check the ID on sheet X, check it on sheet Y and returns the min sell which is in column D. It keeps saying #N/A : Did not find value '22448' in VLOOKUP evaluation. I tried the same vlookup process on non dynamic numbers, and it works, what am I missing ? Thank you for your help, and I hope I deviat so much from the OP subject ! regards. bah! I wrote a response. Then the forum ate it I'll rewrite in a while. Anyway, the 2048 error is as you're trying to pull too much at once. http://stackoverflow.com/questions/8495687/split-array-into-chunks may work, for splitting it up, into multiple chunks. you want to break up cleanTypeIDs into chunks of, say, 100 entries. The vlookup may be down to a text/number thing. Do you have a parseInt around the typeid in the function?
Yah, servers can only handle requests of a certain max length. The easiest work-around is to simply break up your requests into blocks of a certain length. 100 records/rows/entries seems to work well. I'm guessing that changing the request to a Post request might do the trick as well. But I haven't tried it.
The vlookup issue is because the sheet name includes a white-space. You can do this. But for it to work the sheet name must be enclosed in single-quotes.
=VLOOKUP(B4,'price load'!A:D,4,FALSE)
Or you can just rename the sheet to exclude the white-space (recommended).
http://youtu.be/YVkUvmDQ3HY
|
Soldarius
Deadman W0nderland The 99 Percent
999
|
Posted - 2014.12.29 17:57:42 -
[49] - Quote
Double-post because I wanted this part separate from the rest for clarity.
I've modified Steve's original code because there were a couple limitations I ran into, like it not accepting single values (they are numbers not arrays so you get a forEach type error), only systems (Jita by default) and not regions, ranges must be a single column, and a lot of extra data I wasn't interested in. It will now accept a single itemID, an array of itemIDs, or ranges of arbitrary size, as well as system or region ids.
Steve Ronuken, you're welcome to incorporate this into your code on github. Everyone else is more than welcome to use it as well.
For what I do I'm only interested in the 4 buy/sell percentile and volume columns. But that is easily changed to whichever ones you want to include and in whatever order suits you. See Eve-central's API reference for details.
function loadPrices(priceIDs,locationID,cachebuster){ // priceIDs = [[35],[36],[37]]; if (typeof locationID == 'undefined'){ locationID=10000002; // default region is The Forge } var str = locationID.toString(); // is region or system, throw if neither var strScope = str.substr(0,1); if(strScope=="3") var scope = "usesystem="; else if(strScope=="1") var scope = "regionlimit="; else throw 'Invalid locationID'; // no typeIds specified if (typeof priceIDs == 'undefined'){ throw 'need typeids'; } if (typeof cachebuster == 'undefined'){ cachebuster=1; }
// define some arrays and base uri var prices = new Array(); var dirtyTypeIds = new Array(); var cleanTypeIds = new Array(); var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&"+scope+locationID+"&typeid="; if(typeof priceIDs === 'number') // check for single number typeID dirtyTypeIds.push(priceIDs); else if(!Array.isArray(priceIDs)) // check for invalid input throw 'Invalid typeID(s)'; else priceIDs.forEach (function (row) { // So must be a range reference or array dirtyTypeIds.push(row); row.forEach ( function (cell) { if (typeof(cell) === 'number' ) { dirtyTypeIds.push(cell); } }); }); cleanTypeIds = dirtyTypeIds.filter(function(v,i,a) { return a.indexOf(v)===i; });
var parameters = {method : "get", payload : ""}; var xmlFeed = UrlFetchApp.fetch(url+cleanTypeIds.join(","), parameters).getContentText(); var xml = XmlService.parse(xmlFeed); var prices = new Array; if(xml) { var rows=xml.getRootElement().getChild("marketstat").getChildren("type"); for(var i = 0; i < rows.length; i++) { var price=[rows[i].getChild("sell").getChild("percentile").getValue(), rows[i].getChild("buy").getChild("percentile").getValue(), rows[i].getChild("sell").getChild("volume").getValue(), rows[i].getChild("buy").getChild("volume").getValue()]; prices.push(price); } } return prices; }
http://youtu.be/YVkUvmDQ3HY
|
Sharkey Bones
The Scope Gallente Federation
0
|
Posted - 2014.12.30 10:02:56 -
[50] - Quote
Any advise for those of us using the quicklook api to sum up total volume? Here's what I was using before everything got borked on the new google docs sheets
=SUM(importXml("http://api.eve-central.com/api/quicklook?typeid="&join("&typeid=",B1)&"&usesystem=XXXXXX&refresh=" &$H$1,"//sell_orders//vol_remain")) |
|
Gregor Noobius
The Desolate Order Brave Collective
0
|
Posted - 2015.01.02 11:31:16 -
[51] - Quote
Once I get the script to spit out the data I want, is there a way to then format the cells to currency, etc? |
salacious necrosis
Federal Defense Union Gallente Federation
7
|
Posted - 2015.01.02 14:20:28 -
[52] - Quote
Gregor Noobius wrote:Once I get the script to spit out the data I want, is there a way to then format the cells to currency, etc?
https://support.google.com/docs/answer/3094071
or more generally:
https://support.google.com/docs/answer/3094075?hl=en
EveKit - your EVE data on the web!
|
Steve Ronuken
Fuzzwork Enterprises Vote Steve Ronuken for CSM
4640
|
Posted - 2015.01.02 15:50:01 -
[53] - Quote
Sharkey Bones wrote:Any advise for those of us using the quicklook api to sum up total volume? Here's what I was using before everything got borked on the new google docs sheets
=SUM(importXml("http://api.eve-central.com/api/quicklook?typeid="&join("&typeid=",B1)&"&usesystem=XXXXXX&refresh=" &$H$1,"//sell_orders//vol_remain"))
Not hellishly difficult, tbh. Assuming you're only ever going to want to pull the sum of sell order volume remaining,in a particular system:
https://gist.github.com/fuzzysteve/909b6a868b168e070866 is a (not very tested) way to do it.
typeid,systemID,sellbuy,cachebuster
If you don't set the 2nd attribute, it defaults to jita. If you don't set the 3rd attribute, or set it to 1, it loads the sell volume. If it's set to anything else, then it's the buy. The 4th is just added to the url.
=loadSystemVolume(34,30000142,0) - Jita buy vol for trit =loadSystemVolume(34,30000142,1) - jita sell vol for trit
Woo! CSM 9!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
quikfingrs
Salamander Researches And Industries
1
|
Posted - 2015.01.05 14:27:19 -
[54] - Quote
im not sure if it is me or a slightly modified version of your script but,
when i used to do the api pull from eve central it would automatically update my sheet on the fly ( think hourly or 30mins ) with new prices and would pretty much be bob on,
since ive been using your script ive noticed that it doesn't update my script for over 2hrs at a time and some ive sat and watched for close on a hr nothing came back a hr after that and still nothing, so unsure the time interval of when it does update but im pretty sure it does update at some point in 24hrs lol,
i made a separate sheet and uploaded your new script and the first load of prices i compared them to eve-central and they were 2hrs out so its defiantly around the 2hr+ marker
i have set my triggers for the script for time driven 1hr also even set it to 15min just to see and nothing changes,
so is this how it is or has something gone pete tonge my end lol |
Hiro Kaichi
The Folkvangr Project
0
|
Posted - 2015.03.03 16:21:59 -
[55] - Quote
Hi, can someone help me with my spreadsheet because it was working for awhile, now it says loading indefinitely and N/A. Here is the link to the spreadsheet
https://docs.google.com/spreadsheets/d/1KyTS-EJm-61Z5fLHYFewyvEz2GMUspJv5fZbMe_FG-s/edit?usp=sharing
Thanks |
Admiral MastaKilla AlphaU
Itrek Innovations
0
|
Posted - 2015.03.30 18:10:18 -
[56] - Quote
I have a similar loading issue to the one mentioned above. This code I linked below worked fine for over a year until about a month or two ago and I still can't figure out what is the issue. Is this something on EVE Central side that is now changed? I have tried this in new spread sheets with other type id's and none of them work anymore.
=ImportXML(-¦http://api.eve-central.com/api/marketstat?&usesystem=30000142&typeid=-¦& -¦2868-¦ ,-¦/evec_api/marketstat/type/sell/min-¦)
In short, this code above is pulling the min sell price of Integrity Response Drones from the Jita system. Does this work for anyone else using Google Docs? |
Bear Templar
Full Spectrum Inc Greater Western Co-Prosperity Sphere
9
|
Posted - 2015.04.03 19:55:17 -
[57] - Quote
Admiral MastaKilla AlphaU wrote:I have a similar loading issue to the one mentioned above. This code I linked below worked fine for over a year until about a month or two ago and I still can't figure out what is the issue. Is this something on EVE Central side that is now changed? I have tried this in new spread sheets with other type id's and none of them work anymore.
=ImportXML(-¦http://api.eve-central.com/api/marketstat?&usesystem=30000142&typeid=-¦& -¦2868-¦ ,-¦/evec_api/marketstat/type/sell/min-¦)
In short, this code above is pulling the min sell price of Integrity Response Drones from the Jita system. Does this work for anyone else using Google Docs?
I just copied and pasted this and after making some adjustments I got it to work.
I changed the second argument in the function to "//sell/min", and also I noticed that the URL wasn't being read properly and so I removed and then re-added all the quotation marks (on both arguments).
Mine looks like this now:
=IMPORTXML("http://api.eve-central.com/api/marketstat?&usesystem=30000142&typeid=2868", "//sell/min")
If a fish weighs 1 Kilogram plus half its own weight, how much does it weigh? (It's not 1.5kg btw)
|
|
|
|
Pages: 1 2 :: [one page] |