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

Arthur_Bennington · September 27, 2018, 7:27am

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

Blacksmoke16 · September 27, 2018, 2:36pm

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

Blacksmoke16 · October 8, 2018, 4:18am

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

Blacksmoke16 · October 8, 2018, 2:52pm

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

KenFlorian · November 19, 2018, 12:40pm

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?

Blacksmoke16 · November 19, 2018, 1:07pm

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

KenFlorian · November 19, 2018, 1:25pm

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?

Blacksmoke16 · November 19, 2018, 3:29pm

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

KenFlorian · November 20, 2018, 3:26pm

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!

Blacksmoke16 · November 26, 2018, 4:51pm

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.

Blacksmoke16 · January 15, 2019, 1:53am

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

Blacksmoke16 · January 16, 2019, 1:03am

7.4.1 aka version 8 January 15, 2019

Changes

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

Blacksmoke16 · January 20, 2019, 7:00pm

7.4.2 aka version 9 January 20, 2019

Changes

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

Tradulix · January 22, 2019, 8:26am

Nice work.

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.

Blacksmoke16 · January 22, 2019, 1:28pm

Good to hear

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.

Blacksmoke16 · March 25, 2019, 12:09am

7.5.0 aka version 10 March 24, 2019

ESI Updates

https://github.com/esi/esi-issues/blob/master/changelog.md#2019-03-11
- Adds faction support to universe_names

Blacksmoke16 · April 12, 2019, 12:22pm

7.6.0 aka version 11 April 12, 2019

ESI Updates

https://github.com/esi/esi-issues/blob/master/changelog.md#2019-04-10
- Adds title to /characters/{character_id}/

Vicktor_Bout · April 12, 2019, 9:03pm

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?

Blacksmoke16 · April 12, 2019, 11:50pm

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.

Vicktor_Bout · April 13, 2019, 9:47am

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