Help with Google Sheets xml call

EVE Technology and Research Center Third Party Developers
#1

Hello all o7

I have an industry worksheet on Google Sheets that I want to use to pull market data from Eve Marketer. Here is the situation I need some assistance with.

I am using this formula in cell D2.
=importxml(“https://api.evemarketer.com/ec/marketstat?usesystem=30000142&typeid=34","//sell/min”)

I’m trying to pull the sell/min data from Jita for the item listed in typeid, in this situation it is Tritanium. I get a “Formula Parse Error” with this entered into cell D2.

How do I complete this call? Also, how would I be able to use cell B2 to reference typeid, instead of typing each specific id into every formula?

TIA
Apollo North

#2

It would probably be easier to use https://market.fuzzwork.co.uk/api/.

For example, add this function to your script editor:

/**
* Query's Fuzz market API for the given types
* @param {range} A vertical range of type_ids.
* @return maxBuy and minSell for each type_id
* @customfunction
*/
function fuzzApiPriceData(type_ids) {
  if (!type_ids) throw 'type_ids is required';
  const ids = Array.isArray(type_ids) ? type_ids.map(id => id[0]) : [type_ids];
  const fuzz_price_data = JSON.parse(UrlFetchApp.fetch(`https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=${ids.join(',')}`));
  return [['minSell', 'maxBuy']].concat(type_ids.map(type_id => [parseFloat(fuzz_price_data[type_id]['sell']['min']), parseFloat(fuzz_price_data[type_id]['buy']['max'])]));
}

You could then use it like =fuzzApiPriceData(A1:A100), and it’ll return the max buy and min sell for each type_id in that range.

1 Like
Importing Market data to GoogleSheets
#3

Thank you for the response. I don’t have any experience using scripts in spreadsheets. When I click on Script Editor in my Tools dropdown, a new tab opens that says “Sorry, unable to open the file at this time.”
So without digging to deep into fixing my google issue, I am trying to learn the function for importxml, so I can adapt and scale my queries upwards as I learn more.

#4

https://support.google.com/docs/thread/10723995?hl=en

Should solve your problem.

#5

Thank you for the quick script editor fix. I will poke around and give the script you shared a try.

However, for my own knowledge, I am still hoping to learn what was wrong with the importxml function i posted, and how I would be able to reference a specific cell for typeID.

Cheers

#6

Your quotes aren’t the right quotes. Try =importxml("https://api.evemarketer.com/ec/marketstat?usesystem=30000142&typeid=34","//sell/min").

Prob something like importxml("https://api.evemarketer.com/ec/marketstat?usesystem=30000142&typeid="&A1,"//sell/min")

However, XML is a legacy format. You would be better off learning custom functions and Steve’s JSON API. It’s a bit easier to use, and allows for more flexibility.

#7

Thank you so much! You rock.

#8

This looks neat and worked on first try. Can you tell me how to adapt this to limit the query to orders in Jita, Amarr, Perimeter, etc from another cell reference?

#9

the “usesystem=” part of the call determines your specific system, though you’ll need to know the systems id number, like 30000142 = Jita

so your formula would be like this:

=importxml("https://api.evemarketer.com/ec/marketstat?usesystem=" & < cell reference > & “&typeid=” & < cell reference >, “//sell/min”)

#10

You would just replace the station= value with the station ID you want. See the docs at the top of https://market.fuzzwork.co.uk/api/.

#11

Yes, thats how I’m doing it now. I meant, can the function to be changed to ask for a cell with the station name? Like FuzzWhatsItsName(A3:A21, A1) Where the second section asks for the station ID, so that I can make a dropdown for it.

Also, I get weird results using anything but stations. System=[jitacode] or structure=[TTT code] and so on give results that are all the same, perhaps those cluster-wide, but I guess that is another matter.

#12

Fuzzwork only support some stations/systems:

    Global - 0
    Jita - 30000142
    Perimeter - 30000144
    Jita 4-4 CNAP - 60003760
    Amarr VIII - 60008494
    Dodixie - 60011866
    Rens - 60004588
    Hek - 60005686

It does not support the system or structure parameters (only region and station)
Just use region for everything supported, that is what I do, it’s fine:

(Due to how it works, you can give a station or a system as a region, and it will return correctly. These are all precalculated aggregates)

EDIT:
if you’re using evemarketer, it’s the same, only support regionlimit or usesystem
Here is the reference: https://api.evemarketer.com/ec/