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). |
|
|
|
|
Pages: [1] 2 :: one page |
First page | Previous page | Next page | Last page |