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

Blacksmoke16 · March 11, 2018, 1:28am

5.1.0 March 10, 2018

Changes

Bump characters_character_orders to v2.
Bump corporation_corporation_orders to v2.
Make it more clear to NOT include the curly braces in your callback url in the dev app.

Logistics · March 14, 2018, 11:05am

I am very new and learning Google sheets and API stuff, and I would very much appreciate some help.

Is it possible to limit the request of, =characters_character_assets and/or =corporations_corporation_assets, to a station/citadel or at least a system?

Or very least any way that I have probably missed by other means?

Thank you very much in advance for the assistance!

Blacksmoke16 · March 14, 2018, 3:46pm

@Logistics

Short answer: no.

Longer answer: Not by default but doable with some custom code/spreadsheet magic. You can either do the parsing in the spreadsheet AFTER calling the function or when parsing the response from ESI in GESI.gs.

What you will have to do is keep a list of allowable structures/station/system IDs or whatever. Then look at the location_type and location_flag columns. If location_type is station then you can just lookup that station in your static list or using =universe_stations_station(station_id) function and see if it is one of the ones you want to keep. If the location_type is other, it is probably either a structure or container of some sort. if location_flag is Hanger it is an item in your item hanger in a structure, if it is AutoFit it is in a container. If it is in a container you have to lookup the location_id in the item_id column, then lookup that items location_id to get where the container is.

I would lookup either the =FILTER or =QUERY google sheets commands.

What also works nice is if you highlight the columns that the data is in, then go to Data -> create a filter. From there you can filter out data, i.e. where location_flag IS NOT Hanger/Autofit, so you filter out items that are part of a ship to reduce the total number of rows you have to parse.

Specially how to do this I do not know off the top of my head, would have to mess with it more but this should give you a good start.

Logistics · March 14, 2018, 10:34pm

@Blacksmoke16

Thank you! I will take a look at this once I am home. My goal is to limit the number of calls through the API because it seems to have issues with the very large number of items in assets. I figured if I limited it to the system/station I am actually wanting the data from it would prevent the failures in pulling all data.

Pulling corp hanger data wasn’t getting all assets and I am missing the information I am looking for.

Blacksmoke16 · March 15, 2018, 12:19am

Np.

Be sure to use -1 page param to fetch all pages. The missing data might be on another page as by default GESI only returns page 1.

Logistics · March 15, 2018, 5:32am

Ah, that was my problem. I believe it is pulling all data now. Thanks! Again…I am learning this as I go. lol

Donal_Childe · March 26, 2018, 2:42pm

Thanks for looking into that, and for providing a useful example of using fetchall.

Grayclay · March 26, 2018, 9:49pm

Google sheets gives me “Internal error executing the custom function.” when trying to use -1 with markets_structures_structure

edit: After updating my scripts, I’m getting “ESI response error: No reply within 10 seconds (line 203).”

Maybe trying to pull every order for one of the most active citadels in the game isn’t going to work?

=query(markets_structures_structure(citadel_id,-1),“SELECT * WHERE Col2 = 34 OR Col2 = 35 OR Col2 = 36 OR Col2 = 37 OR Col2 = 38 OR Col2 = 39 OR Col2 = 40”)

my jank solution is

=query({markets_structures_structure(citadel_id,1);markets_structures_structure(citadel_id,2);markets_structures_structure(citadel_id,3)},“SELECT * WHERE Col2 = 34 OR Col2 = 35 OR Col2 = 36 OR Col2 = 37 OR Col2 = 38 OR Col2 = 39 OR Col2 = 40”)

Blacksmoke16 · March 26, 2018, 10:23pm

No reply within 10 seconds is an ESI error meaning the request couldn’t reach the monolith or something.

Internal error executing the custom function. sounds like Google doesnt like what you are trying to do with your functions there but i can assume there is a better way

Is possible that it might just be a lot of data and takes some time to process. Am not super sure without looking into it more.

Grayclay · March 26, 2018, 10:26pm

The Internal Error is specific to using -1 as a page, which you mentioned above is a Fetch_All option.

Is that only for specific functions?

Blacksmoke16 · March 26, 2018, 10:28pm

Can you message me on discord and can see if we can figure this out?

EDIT: Google Sheets/GESI just can’t handle super large amounts of data in one call. I will have to look into it, but no ETA.

Blacksmoke16 · April 5, 2018, 12:11pm

5.2.0 April 5, 2018

Changes

REMOVAL GET /legacy/corporations/{corporation_id}/assets/ (v1)
PROMOTION GET /latest/corporations/{corporation_id}/assets/ (v3)
PROMOTION GET /latest/characters/{character_id}/notifications/ (v2)
PROMOTION GET /latest/corporations/{corporation_id}/blueprints/ (v2)
PROMOTION GET /latest/corporations/{corporation_id}/containers/logs/ (v2)

Blacksmoke16 · April 6, 2018, 3:32am

5.2.1 April 6, 2018

Bug Fixes

Fix endpoints.gs syntax error

BornToRiseHell · April 10, 2018, 8:35am

Hey. I have been using your script for quite some time for just a single character. Now that ain’t enough for me and I need to add a ■■■■ ton of characters in the future. My problem is though, with the script changing because of ESI changes blah blah, I can’t seem to find an option for multiple characters. Trying to add chars to "MAIN_CHARACTER = " Doesn’t work past 1. I can’t seem to find anywhere to write more chars. I’ve also tried to just authing more chars and simply writing their name in a command like: =Characters_Character_Industry_Jobs(False, NAME, True)
I tried a lot but I’m lost.
This is all with the newest release of you script version 5.2.1

Blacksmoke16 · April 10, 2018, 3:01pm

@BornToRiseHell

Read my post here.

Genevieve_Mitsuda · April 10, 2018, 5:06pm

I’m having a problem with markets_region_orders. I put buy, sell, and all in for order_type and it errors " is not valid. ‘order_type’ is required.

My full query is =markets_region_orders(all, 10000002, 40)

I’m super new to esi so I apologize if it’s malformed but I used the super handy pop-up guide as I was typing it.

Genevieve_Mitsuda · April 10, 2018, 5:23pm

Figured it out! The string had to be in quotes. Thanks again for all the hard work on this!!

Blacksmoke16 · April 10, 2018, 6:54pm

No problem. For future reference, use the example function when previewing a function (the little window that tells you what each param is etc) markets_region_orders(string, integer, integer, integer, boolean). Notice how each param has a type.

You can then reference https://github.com/Blacksmoke16/GESI#parameter-datatype-samples to see how to input each type.

BornToRiseHell · April 11, 2018, 7:04am

That’s what I’ve been trying for a while now and even before reading that post very thoroughly. It’s doesn’t work and it keeps giving me the error= Error
TypeError: Cannot read property “1.0” from undefined. (line 219).
It works if I put nothing in the () but if I fx. use a function with my main char like
=characters_character_industry_jobs(“fireeight”)
or
=characters_character_industry_jobs(Fireeight)
Then it will not work. The function only works if it’s empty

Blacksmoke16 · April 11, 2018, 2:52pm

Few things to check.

For one thing, the name isn’t the first param in characters_character_industry_jobs If you look at the function in the functions.gs file, or as you are typing in the params you would notice that it is the second.

github.com

Blacksmoke16/GESI/blob/master/function.gs#L1007


}


/** 
* List industry jobs placed by a character
* @param {boolean} include_completed  Whether retrieve completed character industry jobs as well.
* @param {string} name Name of the character used for auth. If none is given, defaults to AUTHING_CHARACTER.
* @param {boolean} opt_headers Default: True, Boolean if column headings should be listed or not.
* @return List character industry jobs.
* @customfunction
*/
function characters_character_industry_jobs(include_completed, name, opt_headers) {
return parseData_(arguments.callee.name,{include_completed:include_completed,name:name,opt_headers:opt_headers});
}


/** 
* Paginated record of all mining done by a character for the past 30 days
* @param {string} name Name of the character used for auth. If none is given, defaults to AUTHING_CHARACTER.
* @param {integer} page  Which page of results to return.
* @param {boolean} opt_headers Default: True, Boolean if column headings should be listed or not.
* @return Character mining ledger.
* @customfunction

SO the function should be like =characters_character_industry_jobs(1, "Fireeight"). It works when it is empty because the MAIN_CHARACTER variable takes over. Make sure the name you are putting in is the exact name as the character you authed, can look in the Auth Data sheet for a list of characters that have been authed.

If you still can’t get it to work, message me on discord and we can get it sorted.

Topic		Replies	Views
ESI is not for the Noob My EVE	8	790	February 12, 2018
Simplest possible connection? Third Party Developers	4	337	January 3, 2019
Need Help New Citizens Q&A	20	595	August 11, 2019
GESI - Skills Third Party Developers	3	532	December 11, 2019
The Pickle's Sheet of Things! With suggestions/issues that might be worth updating EVE x Microsoft Excel Add-in	4	497	December 11, 2023

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

Changes

Changes

Bug Fixes

Related topics