GESI

Google Sheets™ add-on for interacting with EVE ESI API. GESI provides an EVE Online SSO flow to authorize your character(s), as well as a wrapper around ESI in order to access your EVE Online data within Google Sheets™; much like =importXML() worked with the old XML API.

Setup:

1. Install the add-on. From within Google Sheets™: Go to Add-Ons => Get add-ons => Search for GESI, click on it, and click Install.
2. Give the script access to what it needs.
3. There will now be a GESI option under the Add-Ons option in the menu bar. Click it and then click GESI => Authorize Character.
4. Click the EVE SSO button in the modal. Login => select what character you want to authorize => Authorize.
5. (Optional) Repeat step 4 to authorize other characters.
6. Done.

Script Editor

By default, one does not have access to GESI functions for use in custom functions in the script editor. In order to gain access to these functions for custom logic, add GESI as a library to your script:

1. Install the add-on, follow the setup instructions.
2. Within the script editor, click the + icon next to the Libraries heading.
3. Paste in 1KjnRVVFr2KiHH55sqBfHcZ-yXweJ7iv89V99ubaLy4A7B_YH8rB5u0s3 into the Script ID box and click Look up.
4. Select the most recent version that is NOT HEAD, and click Add.

In order to use this, functions must be perpended with GESI, which maps to the Identifier field in the Libraries modal. For example, GESI.universe_types();

NOTE: Libraries do not update on their own. When a new version of GESI is released, click on GESI under the Libraries heading and select the most recent version that is NOT HEAD.

Usage

GESI works by defining custom functions that map to ESI routes. For example, if you wanted to get a list of your assets, you would use this endpoint; GET https://esi.evetech.net/v5/characters/{character_id}/assets/. In GESI, this would be =characters_character_assets() within the sheet.

Arguments can also be passed to the functions, =universe_types_type(34), would return type information for Tritanium. In addition to arguments specific to a function, GESI also defines some arguments that are common between all functions.

• {string} name - Name of the character used for authentication. Defaults to the first authenticated character
  • Only present on functions that map to authenticated endpoints
  • See this for some additional information
• {boolean} show_column_headings - If column headings should be shown. Defaults to true.
  • Mostly for use within the sheet. Determines if the column headings are displayed, such as location_id, or type_id, etc.
• {string} version - Which ESI version to use for the request. Defaults to the latest stable version.
  • See this for some additional information

The common arguments have defaults, you do not need to provide them if your values are the same as the defaults. I.e. =characters_character_assets("MyMainCharacter", true, "latest") is the same as =characters_character_assets().

Using The Autocomplete/Tooltip Window

Google Sheets includes autocomplete for all custom functions, from both within the sheet and script editor. This is a super helpful tool for getting an idea of what each function does.

Each specific function also has a tooltip window that provides a summary of a function, what arguments it takes, etc.

The EXAMPLE section includes a sample function call, with the expected types included. See this for a breakdown of what those types mean.

NOTE: If the tooltip window is missing, you may need to expand it by clicking the little arrow next to the X.

Advanced Usage

The previous section goes over how to use GESI in its most basic form, getting data from ESI into a spreadsheet; much like =importXML() worked with the old XML API. However, GESI can also be used within Google App Scripts to enable creation of more advanced/complex logic via JavaScript.

NOTE: Be sure to complete the script editor setup instructions.

Built-In Functions

In addition to the ESI related functions, GESI also provides some methods intended to be used within the script editor.

• invokeMultiple()/invokeMultipleRaw() - See this section for details.
• getAuthenticatedCharacters() - Returns an object representing the characters that have been authenticated. Keyed by character name, value being an object including their character, corporation, and alliance ids.
• getAuthenticatedCharacterNames() - Returns an array of character names that have authenticated, or null if none have been.
• getCharacterData(characterName: string) - Returns an object with data related to the given characterName. Essentially is equivalent to ``getAuthenticatedCharacters()[characterName]`.
• invoke(functionName: string, params: IFunctionParams = { show_column_headings: true }) - Invokes the functionName with the given params. Returns sheet formatted data (2D array). E.x. invoke("universe_types_type", { type_id: 34 }). Essentially the same as universe_types_type(34) but allows calling functions based on JS variables.
• invokeRaw(functionName: string, params = { show_column_headings: false } as IFunctionParams) - Same as invoke, but returns the raw JSON data from the ESI request.
• getClient(characterName?: string) - Returns an ESIClient for the given characterName. Defaults to the main character if no character name is provided. See the next section for details.

ESIClient

Internally, each function call results in the creation of an ESIClient for a specific character. The client is used to build and execute the request, parse the response, and format the data to return to the sheet. The creation of a client has some overhead (~0.3s) when created. While this normally does not have any noticeable impact when invoking a single request for a single character; it can become a problem when wanting to do a series of requests based for the same character/endpoint. An example of this could be fetching price history data for a list of type_ids.

Normally one would call markets_region_history(type_id, region_id) in a loop, adding results to some other array for processing later. The problem with this is that each invocation of markets_region_history would result in the creation of a new ESIClient. Fetching 100 ids would take a minimum of 30 seconds (0.3 * 100), when the total time of making those requests is less than 1 second.

The ESIClient can be used by advanced users who are developing advanced/complex sheets in order to increase performance. It can also be used by those who are building on top of GESI, using GESI more as a way to handle authentication while implementing the ESI interactions on their own.

getClient accepts an optional character name argument. This is the character who the client will be tied to, i.e. who the ESI requests will be made on behalf of. If no character name is given, it defaults to the main character. Before the client can be used, it must be initialized with a function name. For example:

var client = GESI.getClient().setFunction('characters_character_assets');

Any future calls to the client will be in the context of this function.

execute / executeRaw

These methods are essentially the same as invoke and invokeRaw mentioned within the Built-In Functions section. However, the API is slightly different. Since they are methods of the ESIClient, only the params need to be provided, since the function name is set directly on the client.

var client = GESI.getClient().setFunction('universe_types_type');
var type_data = client.executeRaw({ type_id: 34 });

buildRequest

The ESIClient also exposes a method that can be used to build a request object given a set of parameters. The most common use case for this would be to build out an array of requests before executing them manually via UrlFetchApp.fetchAll. This way, the user is able to have full control over the ESI data, while not having to worry about authentication, etc.

NOTE: There is no validation of arguments before executing the requests. Be sure to provide all required arguments, of the correct types etc.

var client = GESI.getClient().setFunction('markets_region_history');

var type_ids = [34, 35, 36];
var requests = type_ids.map(function(type_id) { return client.buildRequest({ type_id: type_id, region_id: 10000002 }); });

var responses = UrlFetchApp.fetchAll(requests);

// Process the responses

FAQ

How do I know if I have the latest version of GESI?

GESI will automatically update when a new version is released. To see what changed visit the forum thread or the Github Releases page.

NOTE: Changes in the ESI spec, such as adding/removing columns, name changes etc. may break your sheet.

How do I know what functions are available?

Check out functions.ts. This file lists all the available functions, as well as a description of what they return and the available parameters.

What if I want to use a specific ESI route version?

By default GESI uses the version currently on the `latest