ImportJSON to Google Sheets

Xao_Acakypa · May 7, 2020, 11:04am

Hello, everyone!
Since evemarketer.com have problems (market aggregator return error when trying retrive data with “usesystem”), i try use fuzzwork API, but there not XML I am not familiar with programming and can’t figure out how to make Fuzzwork’s API work correctly.
The problem is that I can not import the desired array. For ImportJSON I use this script - https://gist.github.com/paulgambill/cacd19da95a1421d3164
There example of how I try to do it - https://docs.google.com/spreadsheets/d/1ToqiEqkYUu3jzbNK-LjDT5kyQF6kG7p9N7PvwsNQUVs/edit?usp=sharing
Hope someone can help me and sorry for bad english.

Blacksmoke16 · May 7, 2020, 1:06pm

/**
* 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';
  var type_ids = Array.isArray(type_ids) ? type_ids.map(function(id) { return id[0]; }) : [type_ids];
  var fuzz_price_data = JSON.parse(UrlFetchApp.fetch("https://market.fuzzwork.co.uk/aggregates/?station=60003760&types=" + type_ids.join(',')));
  return [['minSell', 'maxBuy']].concat(type_ids.map(function(type_id) { return [parseFloat(fuzz_price_data[type_id]['sell']['min']), parseFloat(fuzz_price_data[type_id]['buy']['max'])]; }));
}

Can copy/paste this into your sheets script editor, Tools => Script Editor.

Then you can use it by passing it a vertical range of IDs to get prices for. By default the function is getting min sell and max buy in Jita.

=fuzzApiPriceData(A1:A500)

Suggested way to use this would be have a sheet for static data, like type_id and type_name, then use this function to get price data for those items. This would allow you to then easily use vlookup to get the prices/name of a type_id.

Xao_Acakypa · May 7, 2020, 1:38pm

Thank you! It’s working. I suppose this will not work with Perimeter (system) or Tranquillty Trading Tower (structure)?

Blacksmoke16 · May 7, 2020, 1:39pm

See https://pr.reddit.com/r/evetech/comments/get652/spreadsheets_are_hard/fprlp7n/ for handling structures.

Xao_Acakypa · May 7, 2020, 1:56pm

this function returns me an error:
ReferenceError: GESI is not defined (line 10).

Blacksmoke16 · May 7, 2020, 1:57pm

Ah yes, you’ll need to install GESI. Can follow these steps: https://github.com/Blacksmoke16/GESI#script-editor.

Xao_Acakypa · May 7, 2020, 2:08pm

Oh. I fogget to Add a library. Now it’s work perfect!
BTW, this function get data from ESI without any of agreggator? Not depending on Fuzzwork status?

Blacksmoke16 · May 7, 2020, 2:12pm

Correct. However because of that, it’s not as easy to use (only 1 ID at a time). Also sheets has a limit on the number of requests that can be made per day. So just be mindful of that.

Steve_Ronuken · May 7, 2020, 6:59pm

github.com

fuzzysteve/eve-googledocs-script/blob/master/FuzzworkMarket.gs

// Requires a list of typeids, so something like Types!A:A
// https://docs.google.com/spreadsheets/d/1IixV0eNqg19FE6cLzb83G1Ucb0Otl-Jnvm6csAlPKwo/edit?usp=sharing for an example

function loadRegionAggregates(priceIDs,regionID){
  if (typeof regionID == 'undefined'){
    regionID=10000002;
  }
  if (typeof priceIDs == 'undefined'){
    throw 'Need a list of typeids';
  }

  var prices = new Array();
  var dirtyTypeIds = new Array();
  var cleanTypeIds = new Array();
  var url="https://market.fuzzwork.co.uk/aggregates/?region="+regionID+"&types="
  
  priceIDs.forEach (function (row) {
    row.forEach ( function (cell) {
     if (typeof(cell) === 'number' ) {
        dirtyTypeIds.push(cell);

This file has been truncated. show original

I did link this on the api page on my site. along with a sample sheet.

system1 · August 5, 2020, 6:59pm

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.