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.
Previous page123Next page
 

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

First post
Author
Argonicus
Viziam
Amarr Empire
#21 - 2014-12-24 22:47:39 UTC  |  Edited by: Argonicus
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
#22 - 2014-12-25 11:24:14 UTC
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
Caldari Provisions
Caldari State
#23 - 2014-12-25 15:00:51 UTC
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
Aliastra
Gallente Federation
#24 - 2014-12-26 16:12:01 UTC
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
#25 - 2014-12-26 18:05:17 UTC
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+"&regionlimit="+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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#26 - 2014-12-26 18:18:05 UTC
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: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.



bah! I wrote a response. Then the forum ate it Sad

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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

quikfingrs
Salamander Researches And Industries
#27 - 2014-12-26 19:58:40 UTC  |  Edited by: quikfingrs
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+"&regionlimit="+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
#28 - 2014-12-26 21:02:21 UTC
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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Omega Flames
Caldari Provisions
Caldari State
#29 - 2014-12-26 21:44:49 UTC
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 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.

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
Viziam
Amarr Empire
#30 - 2014-12-28 05:37:23 UTC
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
Aliastra
Gallente Federation
#31 - 2014-12-28 12:02:10 UTC
Quote:
bah! I wrote a response. Then the forum ate it Sad

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
Caldari Provisions
Caldari State
#32 - 2014-12-28 13:45:30 UTC
=VLOOKUP(B4,priceload!A1:D,4,FALSE)
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#33 - 2014-12-28 18:30:25 UTC
Alphae wrote:
Quote:
bah! I wrote a response. Then the forum ate it Sad

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

Smile 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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#34 - 2014-12-28 21:18:39 UTC
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 XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Alphae
Aliastra
Gallente Federation
#35 - 2014-12-29 12:36:24 UTC
I love you man, everything seems to work amazingly ! Big smileBig smile you're the man ! ShockedLol Thank you !
Soldarius
Dreddit
Test Alliance Please Ignore
#36 - 2014-12-29 17:12:27 UTC
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: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.



bah! I wrote a response. Then the forum ate it Sad

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
Dreddit
Test Alliance Please Ignore
#37 - 2014-12-29 17:57:42 UTC
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
#38 - 2014-12-30 10:02:56 UTC
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
Brutor Tribe
Minmatar Republic
#39 - 2015-01-02 11:31:16 UTC
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
Garoun Investment Bank
Gallente Federation
#40 - 2015-01-02 14:20:28 UTC
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

Use EveKit ! - Tools for EVE Online 3rd party development

Previous page123Next page