These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

Science & Industry

 
  • Topic is locked indefinitely.
 

Spreadsheets won't pull Eve-Central data

First post
Author
Lucius Uta
KarmaFleet University
#1 - 2014-11-14 13:28:04 UTC
I'm using Google Sheets for my blueprints and some time ago they stopped pulling data from Eve-Central for some items, like Morphite. So for example the following formula gives #N/A Import Internal Error


=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=11399", "/evec_api/marketstat/type/sell/min")


Is this a known problem and is there a workaround?
Vladdy Tepes
The Scope
Gallente Federation
#2 - 2014-11-14 18:44:19 UTC
I notice that google docs is not very reliable.

Go to your formula and just delete a character and put it back. If it turns all green then it will work again.
Neugeniko
Insight Securities
#3 - 2014-11-15 06:25:42 UTC
Lucius Uta wrote:
I'm using Google Sheets for my blueprints and some time ago they stopped pulling data from Eve-Central for some items, like Morphite. So for example the following formula gives #N/A Import Internal Error


=importXML("http://api.eve-central.com/api/marketstat?usesystem=30000142&typeid=11399", "/evec_api/marketstat/type/sell/min")


Is this a known problem and is there a workaround?


The importxml function in the new google sheets isn't very reliable for some websites. Try @Fuzzysteve google script custom functions instead. https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs

Also keep a eye out for @HLIBIndustry future work as more market data comes to the CREST API. https://github.com/lockefox/CriusDev/blob/master/gdoc_scripts.js

Hope this helps,
Neug
Soldarius
Dreddit
Test Alliance Please Ignore
#4 - 2014-11-18 22:03:33 UTC
Confirmed, importXML sucks ballz. Use Steve's script.

http://youtu.be/YVkUvmDQ3HY

Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#5 - 2014-11-19 06:41:12 UTC
The main difference to using a script like mine is:

You load all the prices you want onto a separate sheet. Then you use vlookup to grab them as needed.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Lucius Uta
KarmaFleet University
#6 - 2014-11-19 11:55:14 UTC
I will try with FuzzySteve's function then. One question though - what is the cachebuster parameter for, what values it should have and would it be a good idea to remove it?
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2014-11-19 12:16:07 UTC
Lucius Uta wrote:
I will try with FuzzySteve's function then. One question though - what is the cachebuster parameter for, what values it should have and would it be a good idea to remove it?



It's completely optional, so you can leave it out quite happily (like if you leave out the region, you get jita).

It was added on request, as someone wanted to be able to get round the caching from google. (they obey eve central's caching rules, and only pull once an hour) That value just changes the url enough that it can get new data. As long as you change it.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Lucius Uta
KarmaFleet University
#8 - 2014-11-19 14:24:28 UTC
Steve Ronuken wrote:
The main difference to using a script like mine is:

You load all the prices you want onto a separate sheet. Then you use vlookup to grab them as needed.


That's why I modified your function to return single values:

function loadPriceCell(typeID,systemID,cachebuster){
  if (typeof systemID == 'undefined'){
    systemID=30000142; //Jita
  }
  if (typeof typeID == 'undefined'){
    throw 'need typeids';
  }
  if (typeof cachebuster == 'undefined'){
    cachebuster=1;
  }

  var url="http://api.eve-central.com/api/marketstat?cachebuster="+cachebuster+"&usesystem="+systemID+"&typeid="+typeID;
  var parameters = {method : "get", payload : ""};
  var xmlFeed = UrlFetchApp.fetch(url, parameters).getContentText();
  var xml = XmlService.parse(xmlFeed);
 
  if(xml) {
    var rows=xml.getRootElement().getChild("marketstat").getChildren("type");
    var price = rows[0].getChild("sell").getChild("min").getValue();
  }
  return price;
}


and typing
= loadPriceCell(11399)
in a cell (naturally I don't memorize or copy/paste all the TypeIDs, but use vLookup)
gives me minimum sell value of Morphite in Jita, hooray!
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#9 - 2014-11-19 16:23:34 UTC
Sad

That's a somewhat less than friendly to eve-central option.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Lucius Uta
KarmaFleet University
#10 - 2014-11-19 17:39:15 UTC
Steve Ronuken wrote:
Sad

That's a somewhat less than friendly to eve-central option.


Is it because of cachebuster, or because there's bound to be multiple calls in my sheets?
If it's the former, then I just remove that parameter (or call the function without it) and problem fixed.
If it's the latter, it's going to present a spreadsheet challenge for me, since I use a separate sheet (tab) for every blueprint (which is probably not the most efficient way to run spreadsheets, but I only recently started doing this stuff). Though I just noticed that typing something like
=loadPrices(B2:E2)
in my sheet will fill the cells with price data for materials I need, just not the cells where I want the data to be (though that's hardly a problem) . And I still need one call for every sheet.
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#11 - 2014-11-19 19:13:30 UTC
one call per sheet is nicer than one call per price. (and don't you have a consolidated list of all the materials you need?)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

Lucius Uta
KarmaFleet University
#12 - 2014-11-19 21:44:02 UTC  |  Edited by: Lucius Uta
Steve Ronuken wrote:
one call per sheet is nicer than one call per price. (and don't you have a consolidated list of all the materials you need?)


I use the ingame Notepad for that What?

Though I could reduce the number of calls further by considering the fact that some blueprints use the same materials and by using named ranges

And thank you for your help of course
AlertForce
The First Foundation
Legion of xXDEATHXx
#13 - 2014-12-24 21:13:28 UTC