[10.0.0 - version 52] GESI - Google Sheets ESI Add-On - Now an EVE Online Partner!

EVE Technology and Research Center Third Party Developers
,
165

o/ is it possible to at the function =getAllCharacter() beside =getMainCharacter()?

1 Like
166

@Arthur_Bennington What would you want that to return? Just a list of all the characters’ names you have authed?

167

7.2.0 aka version 4 October 8, 2018

Additions

  • Better error reporting

Changes

  • Fix missing params on some unauthed endpoints (#38)
  • Fix error when trying to use a function without an authed character (#40)

Removals

  • Logging
168

7.2.1 aka version 5 October 8, 2018

Changes

  • Fix error when using a character that is not authed (#41)

Removals

  • Global property constants
169

Have any third-party (fourth-party?) developers used GESI to write a support function to extract the highest buy price and lowest sell price for a given item and region, or NPC, or Structure? If you’ve built that and are willing to share it, much appreciated.

I know this is out-of-scope for what Blacksmoke’s set for his work, but maybe somebody has done this?

170

For an NPC station

function typePriceData(type_id, region_id, location_id) {
  var data = GESI.markets_region_orders("all", region_id, -1, type_id);
  var minSell = Number.POSITIVE_INFINITY;
  var maxBuy = 0;
  var result = [];
  
  data.forEach(function(order) {
    if (order[3] === location_id) {
      if (order[1]) {
        if(order[6] > maxBuy) {
          maxBuy = order[6];
        }
      } else {
        if(order[6] < minSell) {
          minSell = order[6];
        }
      }
    }
  });
  return [['minSell', 'maxBuy'], [minSell, maxBuy]];
}

For a structure can edit that one to use the markets_structure_structure endpoint instead. However, if you’re wanting a hub, like jita/amarr then id use Fuzz API.

/**
* 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) {
  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'])]; }));
}

These should work pretty fine. Depending on how many items you want price data for, and how big the market there is, there might be better ways of doing it. But for a few types these would be fine

1 Like
171

This should work. One google/scripts function question…

I’ve got your code included in a .gs in my sheet but it is not seeing the GESI function “market_region_orders”).

Do I need to tell my .gs sheet how to get visibility into the GESI functions?

172

Oops, good catch. I updated the post. Will have to do this to have access to GESI functions in the script itself.

173

Thanks for coding this for me. I’ve done enough coding in my life to recognize what it’s doing, and I’ll be able to use this to build more stuff for myself.

I know this was out of scope for the library, so I especially appreciate you taking the time to write it!

174

7.3.0 aka version 6 November 26, 2018

Additions

  • Add a version parameter to the functions to specify which ESI route version to use. See the readme for more info.
176

7.4.0 aka version 7 January 14, 2019

ESI Updates

Changes

  • ESI requests are now fetched concurrently. ~3x faster for multi page requests
    • Also reduces calls to the Auth Data sheet. Should be more performant than before.
  • Fix missing headers for single value responses
  • Update install url and add license to comply with Google add-on changes.

Removals

  • getRawData function
  • Local cacheing
177

7.4.1 aka version 8 January 15, 2019

Changes

  • Cannot call method “getCell” of null (#47)
178

7.4.2 aka version 9 January 20, 2019

Changes

  • Allow endpoints without a scope to accept arbitrary character/corporation/alliance_ids (#49)
179

Nice work. :slight_smile:

I am using your Add-On for tracking the corporation wallet in google sheets. It’s working fine so far. I am able to fetch the data I will need to extract the “bounty_prices” with following command in A1: =corporations_corporation_wallets_division_journal(1,“main_character”)

My question is how can I get this google sheet to refresh cell A1 every hour? I am very curious, because F5 doesn’t work. I have to cause an error in the formula by typo, and correct it manually. Only this will force the sheet to update the corporation wallet.

Furthermore I have to wait like minimum two hours to get it updated this way. Before he won’t change the fetched datas.

180

Good to hear :slight_smile:

That endpoint is cached on CCP’s end for 1 hour. So once it pulls new data, it won’t get new data for another hour. There is no way around that and is by design. However there is talk/feature request to reducing it. https://github.com/esi/esi-issues/issues/829

There are a few ways to force refresh the data (in Google’s point of view). One by either cut pasteing the function to another cell, delete and retype it in, or add a dummy param at the very end (will need to fill in all parameters along the way) that points to a cell. Then when you edit that cell value it would force Google to fetch new data (but might still be cached on CCP’s end).

I also wouldn’t advise doing something that causes it to refresh when you’re not even looking at it. Would be kind of pointless, especially when sheets has a limit on the number of HTTP requests you can make in a day.

182

7.5.0 aka version 10 March 24, 2019

ESI Updates

1 Like
183

7.6.0 aka version 11 April 12, 2019

ESI Updates

184

Hi, first off all thanx for your great work.
Im successfully using GESI for characters, but im stuck with corporation auth.
How to do this?

185

Corporation endpoints use same auth as characters. You just have to have the required in game roles to use the corp endpoints. I.e. you can’t access corp assets unless you have director roles in game.

1 Like
186

Thanx, have no idea why i was trying to get data by pasting corp name