# \[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:** 9

<div class="post-metadata">

**Author:** ![Arthur\_Bennington](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/arthur_bennington/32/87011_2.png) [@Arthur\_Bennington](https://forums.eveonline.com/u/Arthur_Bennington)\
**Post date:** [September 27, 2018, 7:27am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/165 "2018-09-27T07:27:00Z")

</div>

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

---

<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:** [September 27, 2018, 2:36pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/166 "2018-09-27T14:36:40Z")

</div>

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

---

<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 8, 2018, 4:18am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/167 "2018-10-08T04:18:32Z")

</div>

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

---

<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 8, 2018, 2:52pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/168 "2018-10-08T14:52:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KenFlorian](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/kenflorian/32/449998_2.png) [@KenFlorian](https://forums.eveonline.com/u/KenFlorian)\
**Post date:** [November 19, 2018, 12:40pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/169 "2018-11-19T12:40:01Z")

</div>

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?

---

<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 19, 2018, 1:07pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/170 "2018-11-19T13:07:44Z")

</div>

For an NPC station

```auto
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.

```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) {
  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

---

<div class="post-metadata">

**Author:** ![KenFlorian](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/kenflorian/32/449998_2.png) [@KenFlorian](https://forums.eveonline.com/u/KenFlorian)\
**Post date:** [November 19, 2018, 1:25pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/171 "2018-11-19T13:25:36Z")

</div>

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?

---

<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 19, 2018, 3:29pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/172 "2018-11-19T15:29:16Z")

</div>

Oops, good catch. I updated the post. Will have to do [this](https://github.com/Blacksmoke16/GESI#script-editor) to have access to GESI functions in the script itself.

---

<div class="post-metadata">

**Author:** ![KenFlorian](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/kenflorian/32/449998_2.png) [@KenFlorian](https://forums.eveonline.com/u/KenFlorian)\
**Post date:** [November 20, 2018, 3:26pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/173 "2018-11-20T15:26:37Z")

</div>

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!

---

<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 26, 2018, 4:51pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/174 "2018-11-26T16:51:04Z")

</div>

**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](https://github.com/Blacksmoke16/GESI#what-if-i-want-to-use-a-specific-esi-route-version) for more info.

---

<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 15, 2019, 1:53am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/176 "2019-01-15T01:53:02Z")

</div>

**7.4.0 aka version 7** January 14, 2019

* * *

# ESI Updates

- [https://github.com/esi/esi-issues/blob/master/changelog.md#2019-01-14](https://github.com/esi/esi-issues/blob/master/changelog.md#2019-01-14)
- [https://github.com/esi/esi-issues/blob/master/changelog.md#2018-09-14](https://github.com/esi/esi-issues/blob/master/changelog.md#2018-09-14)
- [https://github.com/esi/esi-issues/blob/master/changelog.md#2018-12-10](https://github.com/esi/esi-issues/blob/master/changelog.md#2018-12-10)
- [https://github.com/esi/esi-issues/blob/master/changelog.md#2018-12-11](https://github.com/esi/esi-issues/blob/master/changelog.md#2018-12-11)

# 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

---

<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, 2019, 1:03am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/177 "2019-01-16T01:03:56Z")

</div>

**7.4.1 aka version 8** January 15, 2019

* * *

## Changes

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

---

<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 20, 2019, 7:00pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/178 "2019-01-20T19:00:48Z")

</div>

**7.4.2 aka version 9** January 20, 2019

* * *

## Changes

- Allow endpoints without a scope to accept arbitrary character/corporation/alliance\_ids (#49)

---

<div class="post-metadata">

**Author:** ![Tradulix](https://sea1.discourse-cdn.com/eveonline/user_avatar/forums.eveonline.com/tradulix/32/275143_2.png) [@Tradulix](https://forums.eveonline.com/u/Tradulix)\
**Post date:** [January 22, 2019, 8:26am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/179 "2019-01-22T08:26:45Z")

</div>

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.

---

<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 22, 2019, 1:28pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/180 "2019-01-22T13:28:49Z")

</div>

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](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.

---

<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:** [March 25, 2019, 12:09am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/182 "2019-03-25T00:09:08Z")

</div>

**7.5.0 aka version 10** March 24, 2019

* * *

# ESI Updates

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

---

<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 12, 2019, 12:22pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/183 "2019-04-12T12:22:17Z")

</div>

**7.6.0 aka version 11** April 12, 2019

* * *

# ESI Updates

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

---

<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:** [April 12, 2019, 9:03pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/184 "2019-04-12T21:03:34Z")

</div>

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?

---

<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 12, 2019, 11:50pm UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/185 "2019-04-12T23:50:48Z")

</div>

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.

---

<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:** [April 13, 2019, 9:47am UTC](https://forums.eveonline.com/t/10-0-0-version-52-gesi-google-sheets-esi-add-on-now-an-eve-online-partner/13406/186 "2019-04-13T09:47:49Z")

</div>

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

[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=8)

[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=10)