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

**URL:** <https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406>\
**Category:** Third Party Developers\
**Tags:** esi, tool\
**Created:** [August 3, 2017, 10:59pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406 "2017-08-03T22:59:11Z")\
**Posts on this page:** 20\
**Page:** 13

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [October 27, 2020, 4:19pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/249 "2020-10-27T16:19:03Z")

</div>

Got an example of a character this is happening for?

---

<div class="post-metadata">

**Author:** ![Vicktor\_Bout](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/vicktor_bout/32/390510_2.png) [@Vicktor\_Bout](https://forums.eveonline.com/u/Vicktor_Bout)\
**Post date:** [October 27, 2020, 4:19pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/250 "2020-10-27T16:19:39Z")

</div>

Mailed you ingame

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [October 27, 2020, 4:45pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/251 "2020-10-27T16:45:32Z")

</div>

I’d file a bug report for this [https://github.com/esi/esi-issues/issues/new?template=bug.md](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.

---

<div class="post-metadata">

**Author:** ![Vicktor\_Bout](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/vicktor_bout/32/390510_2.png) [@Vicktor\_Bout](https://forums.eveonline.com/u/Vicktor_Bout)\
**Post date:** [October 27, 2020, 4:52pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/252 "2020-10-27T16:52:04Z")

</div>

Thank you

---

<div class="post-metadata">

**Author:** ![Vicktor\_Bout](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/vicktor_bout/32/390510_2.png) [@Vicktor\_Bout](https://forums.eveonline.com/u/Vicktor_Bout)\
**Post date:** [November 4, 2020, 10:51am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/254 "2020-11-04T10:51:54Z")

</div>

> [@Vicktor\_Bout](#):
>
> using “universe\_ids” to resolve character IDs it works ok, but what if there two characters with the same name (1st one deleted in Doomheim corporation and 2nd one is active)

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

---

<div class="post-metadata">

**Author:** ![Sheebah\_Severasse](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/sheebah_severasse/32/239767_2.png) [@Sheebah\_Severasse](https://forums.eveonline.com/u/Sheebah_Severasse)\
**Post date:** [November 9, 2020, 6:24am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/255 "2020-11-09T06:24:57Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [November 9, 2020, 1:49pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/256 "2020-11-09T13:49:24Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Highfly\_Chastot](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/highfly_chastot/32/377320_2.png) [@Highfly\_Chastot](https://forums.eveonline.com/u/Highfly_Chastot)\
**Post date:** [November 27, 2020, 1:39am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/257 "2020-11-27T01:39:52Z")

</div>

@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 🙂 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.

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [November 27, 2020, 2:38am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/258 "2020-11-27T02:38:16Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Abraham\_Pek](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/abraham_pek/32/244691_2.png) [@Abraham\_Pek](https://forums.eveonline.com/u/Abraham_Pek)\
**Post date:** [December 5, 2020, 11:01pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/259 "2020-12-05T23:01:26Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [December 5, 2020, 11:45pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/260 "2020-12-05T23:45:32Z")

</div>

@Abraham_Pek There won’t be (built in at least). GESI is just a wrapper to [ESI](https://esi.evetech.net/ui/) 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:

```auto
/**
 * 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/](https://market.fuzzwork.co.uk/api/), which would look something like:

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

```

---

<div class="post-metadata">

**Author:** ![Abraham\_Pek](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/abraham_pek/32/244691_2.png) [@Abraham\_Pek](https://forums.eveonline.com/u/Abraham_Pek)\
**Post date:** [December 6, 2020, 8:33pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/261 "2020-12-06T20:33:13Z")

</div>

whoa, thx for the exhaustive reply 🙂

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [December 19, 2020, 9:28pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/262 "2020-12-19T21:28:25Z")

</div>

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](https://github.com/Blacksmoke16/GESI/issues/75).

---

<div class="post-metadata">

**Author:** ![Mysterious\_Dealer](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/mysterious_dealer/32/244738_2.png) [@Mysterious\_Dealer](https://forums.eveonline.com/u/Mysterious_Dealer)\
**Post date:** [December 24, 2020, 1:26pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/263 "2020-12-24T13:26:04Z")

</div>

Any news about GESI migration? 🙄

---

<div class="post-metadata">

**Author:** ![Mysterious\_Dealer](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/mysterious_dealer/32/244738_2.png) [@Mysterious\_Dealer](https://forums.eveonline.com/u/Mysterious_Dealer)\
**Post date:** [January 15, 2021, 7:56pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/264 "2021-01-15T19:56:08Z")

</div>

![tenor](https://global.discourse-cdn.com/eveonline/original/3X/d/e/de9fc7f368de8e0399356a51a225bd329922f78f.gif)

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [January 16, 2021, 3:15pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/265 "2021-01-16T15:15:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [January 18, 2021, 8:17pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/266 "2021-01-18T20:17:19Z")

</div>

**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

- [https://github.com/esi/esi-issues/blob/master/changelog.md#2020-12-16](https://github.com/esi/esi-issues/blob/master/changelog.md#2020-12-16)
  - Remove `/characters/{character_id}/stats/`

---

<div class="post-metadata">

**Author:** ![Mystic\_Dealer](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/mystic_dealer/32/390508_2.png) [@Mystic\_Dealer](https://forums.eveonline.com/u/Mystic_Dealer)\
**Post date:** [April 4, 2021, 7:18am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/267 "2021-04-04T07:18:55Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Blacksmoke16](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/blacksmoke16/32/376589_2.png) [@Blacksmoke16](https://forums.eveonline.com/u/Blacksmoke16)\
**Post date:** [April 8, 2021, 10:31pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/268 "2021-04-08T22:31:53Z")

</div>

Still having issues?

---

<div class="post-metadata">

**Author:** ![Mystic\_Dealer](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/mystic_dealer/32/390508_2.png) [@Mystic\_Dealer](https://forums.eveonline.com/u/Mystic_Dealer)\
**Post date:** [April 8, 2021, 10:45pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/269 "2021-04-08T22:45:48Z")

</div>

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

[Previous page](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406.md?page=12)

[Next page](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406.md?page=14)