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

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.
1 Like

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!

@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.

@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.

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.

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

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

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”)

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 :stuck_out_tongue:

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.

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?

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.

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)
1 Like

5.2.1 April 6, 2018


Bug Fixes

  • Fix endpoints.gs syntax error

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

@BornToRiseHell

Read my post here.

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.

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

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.

1 Like

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

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.

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.