4.1.0 February 13, 2018
corporations_corporation_structures endpoint was updated to v2 route, columns returned are different now.
-1
Is there an easy/obvious fix for:
Request failed for https://login.eveonline.com/oauth/token returned code 400. Truncated server response: {āerrorā:āinvalid_requestā,āerror_descriptionā:āRefresh token is missing.ā} (use muteHttpExceptions option to examine full response) (line 218).
That sounds like the name you set in the CHARACTERS array doesnāt match the name you used as a param, or changed it after you authed.
Solution would be to reauth that char.
If that doesnāt help, convo me in game and we can see if we can figure it out.
It was only for a certain function which Iāve now forgotten - Iām only currently using structure_market.
Speaking of which, is there a way to pre-filter returned values within the function? It would be nice to be able to circumvent needing to pull several pages of data if I could just filter my all for only typeid = 34.
Assuming you are talking about markets_structures_structure there is not, you would have to either edit the script yourself or filter the result using some google sheet function.
@Blacksmoke16 Thank you =) now it works!!! But i think its hard to add over 60 chars in this way xDā¦ Do you see a solution for this problem?
At this point i also have to thank you for your work!!! I love to use GESI =)
Yes, at that point you might want to look into an actual app 
4.1.2 February 22, 2018
Not meaning to double post, but while iām thinking of itā¦
The current plan for non GET Endpoints involves having a sidenav, similar to the one that opens to auth your character, that allows you to pick an endpoint, and select cells/values to use, then hit a button to do the request.
This way the POST/PUT/DELETE endpoint calls are not tied to cell functions. This will solve the one off nature of them.
I do not have an ETA for this, as some R&D will be required to make this work how i want it to.
I have a question. Iām very new to coding here so please bare with me as I stumble through. Iāve got a Spreadsheet set up with several sheets that grab data; industry jobs, market order, wallet transactions and the like. Iām trying to find a way to set an auto refresh them all once an hour, and was wondering if there was a function in GESI i could run with the hourly trigger?
Thanks for your time.
This kind of data is available thru ESI and by extension GESI.
However in regards to your specific question, that functionality isnāt already made in GESI and would be up to you to develop for your specific need. GESI just returns to you what ESI returns.
I have a Sheet using GESI that pulls market history, and generates a 30 day summary. Iām am only pulling about twenty items each in two trade hubs, but I am getting consistent urlFetch quota exceedences about half way through the day. The data seems to aggressively refresh, somehow turning 40 calls into 20000 plus over time. I am in the process of changing those calls to a menu triggered manual update, but making them sequentially takes forever. So, questions.
Finally, thanks for your scripts. I would still be banging my head over Oath2.
How many pages are there for each item? Each page counts as its own request if you are using the page = -1. So for example 5 pages per 20 items in two hubs would be 200 requests when it updates. Other than that, iām not too familar with how google sheet updates its functions.
I can look into it, possibly include it in v5.
It was 2 sheets, each with about 20 cells that had market history requests. AFAIK, ESI market history does not use pages, it just returns a full year of data for a regionid, typeid pair.
I changed my setup::
I reduced the number of calling cells to one on each sheet.
I added console.log to the urlfetch call in GESI.
and did some testingā¦
Checking the log this morning (after a night of no human activity) I found that the script was calling pathData: /login.eveonline.com/oauth/token and my two market history calls twice every nine or ten minutes, all night long.
so, one authed toon, two history calls, resulted in six urlfetch calls every nine or ten minutes.
I wonder if you have observed this kind of background refreshing behavior, or if I have broken things in strange new ways.
It calling login.eveonline.com/oauth/token makes sense in that it is updating the tokens for the requests. I thought i had logic that didnāt refresh tokens if it is an unauthed request. It must have got reverted in some merge i missed.
As a work around you can comment out https://github.com/Blacksmoke16/GESI/blob/master/GESI.gs#L163. Only will work if you are not making ANY authed requests. This will cut down a fair bit of queries.
I will readd that logic in v5 as well as look more into how sheets does refreshing.
A bit more poking around has shown me that a couple of importrange statements are linked to the refresh behavior. I was trying to consolidate all my data in a central location, for use in other spreadsheets. It seems that using importrange to get the data from a sheet that was using markets_region_history was causing a refresh token and refresh of the data every nine minutes or so. When I removed the importrange functions, the odd behavior stopped. I am currently testing the same setup with a raw UrlFetchApp.fetch to see if it does the same thing.
Wrong. See edit below. -> importrange on a bare UrlFetchApp.fetch does not cause the called page to refresh endlessly. hmm.
Let me explainā¦ No, there is too much. Let me sum up.
There are two spreadsheets, A and B.
A is used to collect data in one place, to make it easier to maintain, but available for sharing. A uses GESI for its live data.
B consumes data in A, using ImportRange to pull arrays of cells. In this case market history data.
A has a sheet with a column of typeids, and next to each typeid is a formula that consumes market history data and returns an array that represents a summary of the history data. 20 typeids = 20 calls to markets_region_history that are rendered into summaries.
B imports the data from A using importRange. This causes the sheet in A to reload data about every 9-10 minutes.
Wrong -> In the same situation, but with A using bare UrlFetchApp.fetch calls to the ESI endpoints, this odd refresh behavior is not observed.
Edit: ok, after MORE tests the behavior is exactly the same. there is something about importRange that is forcing the sheet to reload every ten minutes or so. This reload is calling all the UrlFetchApp.fetch cells again and again. This is not a problem with GESI.
Solution: Use menu triggered page updates in A, instead of formulas in cells. This is actually better for single calls for data that doesnāt change often, like system cost indexes. But it is much slower in the case of many multiples of calls like pulling lots of market history because the calls seem to be handled sequentially while calls in cells on a sheet seem to load concurrently. fetchall could be the answer in this limited case, but I have not tried to confirm that it makes concurrent calls behind the scenes. Another solution would be for the user to generate a temporary sheet, via menu command, that uses the column of typeIDs - individual cell - formula - download style of getting data, then copy just the data to another sheet before erasing the temp sheet.
I did some testing regarding fetchAll.
It seems fetchAll is quite a bit faster. I ran the test three times and these were the results.
Test Round 1
[18-03-04 23:39:29:335 EST] fetch completed in: 0.827 seconds
[18-03-04 23:39:29:462 EST] fetchAll completed in: 0.126 seconds
Test Round 2
[18-03-04 23:40:22:603 EST] fetch completed in: 0.532 seconds
[18-03-04 23:40:23:185 EST] fetchAll completed in: 0.582 seconds
Test Round 3
[18-03-04 23:41:11:544 EST] fetch completed in: 1.041 seconds
[18-03-04 23:41:11:706 EST] fetchAll completed in: 0.162 seconds
function fetchOne() {
var start = new Date();
var response = UrlFetchApp.fetch('https://esi.tech.ccp.is/v1/universe/types/');
var pages = response.getHeaders()['x-pages'];
var data = JSON.parse(response);
for (var p = 2; p <= pages; p++) {
data = data.concat(JSON.parse(UrlFetchApp.fetch('https://esi.tech.ccp.is/v1/universe/types/?page=' + p)));
}
var end = new Date();
Logger.log("fetch completed in: " + ((end - start) / 1000) + " seconds");
}
function fetchTwo() {
var start = new Date();
var requests = [];
for (var p = 1; p <= 34; p++) {
requests.push({
'url': 'https://esi.tech.ccp.is/v1/universe/types/?page=' + p,
'method': 'get'
});
}
var data = UrlFetchApp.fetchAll(requests);
var end = new Date();
Logger.log("fetchAll completed in: " + ((end - start) / 1000) + " seconds");
}
function test() {
var fetch = fetchOne();
var fetchAll = fetchTwo();
}
So it seems worth while to implement; however it will prob be a minor bump in version 5, as it will take a decent amount of work to do and I do not want to delay version 5.
5.0.0 March 6, 2018
This major version release focuses on refactoring where SSO auth data (char name/id, corp/alliance id, refresh_token) are stored as well as some QoL improvements for people using the script for corp management.
Previously I was relying upon documentProperties service of Google scripts. However, #21 as well as messages from others brought to my attention that it for one reason or another it loses the data every so often. This
This new method creates a new sheet Auth Data within your spreadsheet to store the data, much like a database, one row per character. This allows users to easily export that data and copy paste it back in if something breaks. Or if you want to remove auth of a particular character, just delete their row. This change makes GESI much more reliable and easier to maintain for corp directors and CEOs (or people with a lot of characters).
By default the Auth Data sheet is hidden and protected so that it is only unhideable/viewable by the sheet owner. i.e. only the sheet owner can see the refresh tokens of people authād on that spreadsheet. If you wish to give other people access you will have to manually add them using (Data -> protected sheets and ranges -> show all -> Auth Data -> change permissions and change Only Me to Custom). However auth data can still be retrieved by anyone with edit access by writing a custom function to iterate over the Auth Data sheet and log data.
From my testing, only people with edit access can auth. In terms of corp management, this either means giving each member edit access, have them auth, then revoke edit access to view only or none at all, or only use the sheet for CEO/Director level usage.
If you require absolute security I would suggest the following (i didnāt test this so any issues message me):
function authCallback(request) {
return HtmlService.createHtmlOutput('Your refresh token is: ' + getAccessToken_(request.parameter.code)['refresh_token']);
}
This way general members only ever get to see their own refresh_token and the master spreadsheet can be kept secure with only Leadership/CEO/Directors having edit access.
As usual any issues/etc feel free to get in touch.
Smoke
5.0.1 March 7, 2018