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.
123Next page
 

Google Docs & ImportXML woes regarding EVE-central. Pull stops working

First post
Author
Shahrokh Dariush
Conspicuous Trading Company
#1 - 2014-07-26 19:50:50 UTC  |  Edited by: Shahrokh Dariush
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
#2 - 2014-07-28 00:46:31 UTC
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
#3 - 2014-07-28 06:35:23 UTC
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
Aliastra
Gallente Federation
#4 - 2014-08-01 02:31:21 UTC
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
#5 - 2014-08-01 05:11:09 UTC
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
Aliastra
Gallente Federation
#6 - 2014-08-01 06:36:52 UTC
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
Garoun Investment Bank
Gallente Federation
#7 - 2014-08-02 09:21:55 UTC
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
Frozen Dawn Inc
Frozen Dawn Alliance
#8 - 2014-09-05 16:22:46 UTC
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
#9 - 2014-09-05 19:23:23 UTC
Shahrokh Dariush
Conspicuous Trading Company
#10 - 2014-09-06 11:19:05 UTC
Thanks for the function! It's working out well so far.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#11 - 2014-09-06 16:00:24 UTC  |  Edited by: Steve Ronuken
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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Lilu Egnald
DeadMan's Squad
Test Alliance Please Ignore
#12 - 2014-09-08 07:38:37 UTC  |  Edited by: Lilu Egnald
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
Dreddit
Test Alliance Please Ignore
#13 - 2014-11-06 16:07:53 UTC
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.

http://youtu.be/YVkUvmDQ3HY

Aivlis Eldelbar
Caldari Independent Navy Reserve
Curatores Veritatis Alliance
#14 - 2014-11-13 21:21:29 UTC
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
W.A.S.T.Y.A.
#15 - 2014-12-23 22:27:28 UTC
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
#16 - 2014-12-24 09:52:07 UTC
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
#17 - 2014-12-24 11:28:20 UTC
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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Cpt Patrick Archer
Burning Napalm
Northern Coalition.
#18 - 2014-12-24 13:30:54 UTC  |  Edited by: Cpt Patrick Archer
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.
Argonicus
Wastion Dominion
W.A.S.T.Y.A.
#19 - 2014-12-24 20:21:21 UTC  |  Edited by: Argonicus
New question, I'm trying to make function work for direct systems in direct region, but i got next error:
TypeError: function forEach can not be found in object 45. (row 32, файл Eve-google)

var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&regionlimit="+regionID+"&usesytem="+systemID+"&typeid=";

!32 priceIDs.forEach (function (row) {
row.forEach ( function (cell) {
if (typeof(cell) === 'number' ) {
dirtyTypeIds.push(cell);
}
});
});
quikfingrs
Salamander Researches And Industries
#20 - 2014-12-24 22:31:01 UTC
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?
123Next page