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

EVE Technology and Research Center Third Party Developers
,
249

Got an example of a character this is happening for?

250

Mailed you ingame

251

Iā€™d file a bug report for this https://github.com/esi/esi-issues/issues/new?template=bug.md. I confirmed itā€™s only returning the one character, however the /search endpoint is correctly returning both IDs.

1 Like
252

Thank you

254

BTW, the problem was solved by itself, when deleted character disappear from search results.
So maybe there is some database cleaning (ones per day? or during DT)

Anyway thanks

1 Like
255

I hope this isnā€™t a dumb question, but I canā€™t seem to find how to Auth a Corporation. My corp stores a lot of material in Offices and Iā€™d like to use corporations_corporation_assets to inventory them. This currently returns corpname is misspelt or unauthed.

Thanks in advance.

256

You donā€™t auth a corporation directly persay. You just use a character whoā€™s in that corporation and has the required in game roles to access the data. In this case, that character would need to have the director role.

257

@Blacksmoke16 Hey just want to say fantastic work setting all of this up, really helped create an amazing piece and could not have done it without your work :slight_smile: Buuut I do have a problem, Iā€™ve got an error randomly with the function ā€œ=characters_character_wallet_transactions(,ā€œNAME HEREā€)ā€ and the error message says ā€œError: {ā€œerrorā€:ā€œNot foundā€} (line 185).ā€ Not sure what happened as that was one of the first functions inputted into my sheet and it has worked flawlessly since.

258

This is a known issue. Iā€™d just hang tight until it gets fixed on ESIā€™s end.

259

I hope there will be a new function derived for station to station trading, for example

markets_station_orders (station_id, order_type, type_id, show_column_headings, version)

260

@Abraham_Pek There wonā€™t be (built in at least). GESI is just a wrapper to ESI so if ESI doesnā€™t expose something like that, GESI wonā€™t be able to do it. However you can write a custom function to achieve the same goal using what ESI exposes, something like:

/**
 * Returns the minSell and MaxBuy price for the provided typeId at the provided locationId.
 *
 * @param {number} typeId the typeId to get price data for
 * @param {number} locationId locationId to filter on.  Can either be a structure or station ID.
 * @param {number} regionId regionId to filter on.  Only required if locationId is a station ID.
 * @customfunction
 */
function typePriceData(typeId, locationId, regionId) {
  var data = locationId > 1000000000000 ? GESI.invokeRaw("markets_structures_structure", { structure_id: locationId }) : GESI.invokeRaw("markets_region_orders", { region_id: regionId, order_type: "all", type_id: typeId });
  var minSell = null;
  var maxBuy = null;
  var result = [];

  data.forEach(function(order) {
    if (order.location_id === locationId && order.type_id === typeId) {
      if (order.is_buy_order) {
        if (!maxBuy || order.price > maxBuy) {
          maxBuy = order.price;
        }
      } else {
        if (!minSell || order.price < minSell) {
          minSell = order.price;
        }
      }
    }
  });
  return [['minSell', 'maxBuy'], [minSell, maxBuy]];
}

Feel free to modify that to fit your needs. Also realize the only way to get this data at the moment is to iterate through EVERY order in the region, checking if each one is your desired type_id. This is not super efficient due to the request limit of Sheets, especially when youā€™re looking up a bunch of items and/or the regions have a lot of pages.

If you ARE needing to lookup a lot of items, it may be easier to define one method that handles updating prices in a dedicated sheet from one (or a few if in diff regions) requests. This way you limit the number of requests youā€™re making. It would also be faster as the other logic in your sheet could use vlookup from that data sheet.

NOTE: If you only care about the main trade hubs (jita, amarr ,etc) it would be much more efficient to use an aggregate API like https://market.fuzzwork.co.uk/api/, which would look something like:

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

whoa, thx for the exhaustive reply :slight_smile:

262

As a result of a migration to a newer Google Cloud Platform project, you may see Google hasn't verified this app when authorizing GESI with your Google account. Part of the migration requires going thru the OAuth verification process again. Until that is completed, you can click advanced and then Go to GESI (unsafe) option.

Iā€™ll be sure to post here again when it has been verified.

Update: See https://github.com/Blacksmoke16/GESI/issues/75.

263

Any news about GESI migration? :roll_eyes:

264

tenor

265

GESI should be back to normal. OAuth verification has been completed and its back in the marketplace. For those of you using local installs, be sure to delete your local GESI files and EVE dev application before installing.

266

8.3.0 aka version 36 January 18, 2021

This version has been live for quite some time. Given there hasnā€™t been any reported issues I think itā€™s finally time to make it official.

Additions

  • Adds an Enable Sheet Auth Storage menu option to support an alternative auth storage mechanism for handling large amounts of characters

Changes

  • Update README and other administrative items to support new script editor and GSP project

Fixes

  • Better support Firefox
    • No longer need to do the right click, open in new tab workaround

ESI Updates

1 Like
267

Strange issue with google spreadsheet few days. gesi works fine but spreadsheet seems change formula calculation delay or gesi load data slower then before so all formulas based on gesi data donā€™t work or need to manually refresh after gesi data fetched

268

Still having issues?

269

Yes, im did some formulas trick to renew cells after gesi load data, but yes issue persists.
Have no idea whatā€™s happens.