Slow Execution With Multiple Characters

[AbsoluteTruth]

Hi. I have a few custom functions that piggyback off of GESI. I recently upgraded from an old version of GESI, where the auth information (i.e., the refresh tokens), was stored in Properties. By way of background, I have about 115 characters authed.

The goal of my scripts is to get the skill queue, attributes, and trained skills for each of my 115 characters, and then output them to a sheet. So for each character, I call the API three separate times. In the old version of GESI, where the auth data was stored in Properties, the entire script took about ~150 seconds to run, since getting the auth data for each character is significantly quicker from Properties than it is from the Auth Data spreadsheet.

In the new version of GESI, those same scripts take almost 2x as long to run, since for each call to the API, GESI reads the auth information from the Auth Data spreadsheet. So for each of the three GESI functions I am using, each function makes a separate call to the Auth Data sheet. Multiply that by 115 characters and im sure you can see the problem. As im sure you are aware, reading the auth data from a spreadsheet is significantly slower than calling that same information from properties.

As a result, the same scripts which executed in 150 seconds in the old version of GESI now take twice as long to execute in the new. Often I hit the execution time limit. Is there a way to reduce the amount of calls to the Auth Data sheet when running multiple functions? Perhaps storing the information from the Auth Data spreadsheet in Properties as well, and then using that information from Properties to make the API calls? Anything to reduce the amount of calls to the Auth Data spreadsheet when running multiple functions for the same character would be appreciated.

[Blacksmoke16]

Not real easily. The properties storage is limited to key value pairs, and isn't super reliable; the reason i switched was due to the tokens of authed people getting lost after some time. The lookup to the auth data sheet is required for two reasons. One it validates the character you are using is authed, and the row returned is used for the ESI calls to happen, for example getting that character's character_id.

Really for the average person this wouldn't be a big deal, but having that many characters is anything but average. In reality GESI, and sheets in general, was not meant to handle this amount of characters. But you have a few options...

One would be write a custom implementation of GESI and just store your refresh tokens and character_ids and stuff in an array of objects in the script editor. Then you could pretty much just lookup based on that array which should be quite fast. There are functions that can easily help with this, such as refreshToken and getAccessToken. Can hit me up on Discord if you want to talk more about this.

The other option would be write a quick python app or something to do this. Would prob be quite a bit faster and less restrictions on total requests you can make etc.

[AbsoluteTruth]

For instance, compare this (old version of GESI)

18-11-24 12:30:48:244 PST] Properties.getProperty([Ghost74_refresh_token]) [0.022 seconds]

to this (new version of GESI)

[18-11-24 13:04:43:381 PST] Spreadsheet.getSheetByName([Auth Data]) [0 seconds]
[18-11-24 13:04:43:608 PST] Sheet.getDataRange() [0.226 seconds]

[AbsoluteTruth]

FWIW, i've been a heavy user of GESI since it was first introduced, and I never once had a character lose a token, and i've been using the GESI version that stored same in Properties until now. Are you sure that the underlying issue wasn't the fault of the user in some fashion?

Until I upgraded to the new GESI version, the old version of GESI and google sheets was handling the volume of characters with no issues.

Is there a way to add some sort of a simple toggle in GESI to store the auth data in the Auth Data sheet OR the Properties storage, if the user so elects? I'm trying to avoid a custom implementation of GESI since I don't want to have to register the sheet as its own developer application.

[Blacksmoke16]

Having that toggle wouldn't be something i'd be interested in doing.

You wouldn't have to. Are functions that allow you to get your access_token and to refresh a token. I.e. (some pseudo code)

characters = [] of objects with `character_name, character_id, corp_id, refresh_token`

for each character use the `getAccessToken` function, if its expired use the `refreshAccessToken` to refresh it.

Then manually make a call to ESI using UrlFetchApp, only thing you really need to do that is the access token which you'll have from one of those 2 functions.

Do what you want with result

This would bypass a lot of the Auth Data lookups and other logic that GESI implements.

[AbsoluteTruth]

I appreciate the response. However, doing anything with the authentication flow is above my programming skill level. I know juuuuust enough to work with the data returned from GESI, but anything above and beyond that isnt within my skills. I guess im SOL. Thanks anyways.

[Blacksmoke16]

Hit me up on discord, it's really not as hard as you're thinking.

[AbsoluteTruth]

Whats your discord name?

[Blacksmoke16]

Blacksmoke16#0016

[Blacksmoke16]

Going to reopen this as I think I can add the properties service as a cache layer to the Auth Data sheet. I.e. try from properties, if there use that, otherwise try Auth Data sheet.

[Blacksmoke16]

Not actually going to do this. The refactor i did for concurrent requests cut the amount of times GESI has to fetch the user row to only 1 per function call. As such the benefits of this would be negligible.