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

Tau_Hopper · January 29, 2020, 10:24am

Still no news?

Blacksmoke16 · January 29, 2020, 3:14pm

I been talking with them and things on my end should be resolved. So just waiting on them to actually review it now as before was mainly waiting on me to fix some things as it turns out.

doogymoogy · February 18, 2020, 8:09pm

Hey, thank you for your work on such an awesome project. I know we are still waiting on the add-on to be verified by Google, but as I am just learning and am new to API stuff, I thought I would have a play and managed to use the older manual method. I managed to get all the way to logging in with my character, but unfortunately, I keep getting a json unexpected end of input error. I nearly got to the final hurdle. I wonder if the issue is that I am copying from old GESI.gs/endpoint.gs/funtion.gs files as they are 2 years old. If you could point me to more up to date ones if they exist that would be great. Alternatively, I’ll wait untill Google approves the App.

Sorry if I am completely going about this in the wrong way but I am excited to get involved in this area.

Thanks again

Blacksmoke16 · February 19, 2020, 7:14pm

You can get the latest .gs code from https://script.google.com/d/1KjnRVVFr2KiHH55sqBfHcZ-yXweJ7iv89V99ubaLy4A7B_YH8rB5u0s3/edit?usp=sharing.

The main things you’ll have to do is like replace ScriptApp.getScriptId() with your sheet’s scriptID, PropertiesService.getScriptProperties().getProperty('CLIENT_ID') with your clientId, etc.

doogymoogy · February 20, 2020, 3:02pm

Wow thank you so much. I’ll give it a try.

doogymoogy · February 20, 2020, 6:58pm

Unfortunately, I gave it a try but I think my skills are not yet up to the task. I did make some progress though.

I replaced the things you advised but got the following error:

{“body”:"{“error”:“invalid_client”,“error_description”:“Missing or invalid client credentials.”}",“code”:401,“path”:“https://login.eveonline.com/v2/oauth/token",“sheet_id”:“1qYlvfULQxUT2GOFj-bkzJh2jBMbWfG1vHH3p8mM_ImI”,"character”:null} (line 292, file “GESI”)

I’m thinking:

Does line 76 need to be replaced with my character name?
Should I be amending lines 199 and 231 with my client id and secret id?

I wonder if you accept in game ISK as a reward for helping in my new endeavour? I managed to use the google chrome postman app to extract data from Eve Swagger and am playing around with the data but I have a lot to learn still and thought your GESI app is a marvelous thing

Thanks again,

Doogymoogy

Blacksmoke16 · February 20, 2020, 7:12pm

No that one is fine as MAIN_CHARACTER just ends up being the first char you auth.

Yes, of course. Will want to update any references of CLIENT_ID and CLIENT_SECRET to be your credentials. I’d suggest defining a variable at the top, then reference that in the code.

doogymoogy · February 20, 2020, 7:17pm

Thanks again. I’ll give it another go soon as im just letting my brain cool down for a bit

Kegsy · March 2, 2020, 10:59pm

Hi Blacksmoke

Amazing work Thank you.

Solomon_Iskander · March 5, 2020, 8:29pm

I am bad at programming so I am not able to figure out the changes that need to be made to get this working again. Do you have any idea how long Google takes to review an app?

Blacksmoke16 · March 5, 2020, 8:40pm

I had to resubmit it as I was originally missing some scopes. Probably a couple more weeks.

In the meantime, you can refer to

Steps I took to make this work:

Copy all the files from https://script.google.com/d/1KjnRVVFr2KiHH55sqBfHcZ-yXweJ7iv89V99ubaLy4A7B_YH8rB5u0s3/edit?usp=sharing to your Google Sheets within script editor. Be sure to go to view => show manifest file and copy that too.

Create application in https://developers.eveonline.com/

Callback for the app should be : https://script.google.com/macros/d/YOUR_SCRIPT_EDITOR_CODE/usercallback
3.1 Where YOUR_SCRIPT_EDITOR_ID would be the long string in the URL when you’re in the script editor. For example, 1KjnRVVFr2KiHH55sqBfHcZ-yXweJ7iv89V99ubaLy4A7B_YH8rB5u0s3 from the link above.

Obtain Client ID and Secret Key

Replace all PropertiesService.getScriptProperties().getProperty('CLIENT_ID') and PropertiesService.getScriptProperties().getProperty('CLIENT_SECRET') with the above values in gesi.gs

Replace PropertiesService.getScriptProperties().getProperty('BASE_URL') with https://esi.evetech.net/

Authenticate via Add-ons menu.

You are ready to use GESI within Google Sheets.

If you have any trouble feel free to join the Discord server and we can get it working.

Solomon_Iskander · March 5, 2020, 9:06pm

Thank you! I will try that tonight.

Blacksmoke16 · March 7, 2020, 1:30pm

Should be good to go now.

Tau_Hopper · March 14, 2020, 3:01pm

Indeed!

Now that I was finally able to install GESI through the G Suite Marketplace I have a small doubt, though.

I’m not sure if I missed something about it in the documentation or in a previous comment, but when I use a range of cells as input to e.g. universe_names, I get some incorrect outputs - although I suspect that, rather than being actually wrong, they are simply not aligned with the input values.

Is it the case that the order of the outputs is not guaranteed to match that of the queried values when an array is provided?

rayman500 · April 16, 2020, 8:08am

Hay Fist off thanks a lot for this it is so helpful and makes it possible for me to do the kind of industry I want to do!!
but I am now getting a error in my sheet that has been working fine for the last year.

when I use the formula:

=characters_character_assets(,-1,-1)

I get “#ERROR!”

I have uploaded an image of the error.

can anyone help with this?

thanks, Ray

Blacksmoke16 · April 16, 2020, 12:46pm

Use "v5" in the version argument. This’ll be fixed when I release the new GESI version this weekend. Until then that should do the trick.

rayman500 · April 16, 2020, 2:44pm

I used:
=characters_character_assets(,-1,-1,“v5”)
and it works perfectly.

Thanks!

Deollyn_Dulcatsil · April 17, 2020, 1:06pm

Hey, thanks for this. I’m just starting out using it and I’m wondering how often data will update for various industry function calls?

Is there somewhere I can see this?

Blacksmoke16 · April 19, 2020, 5:43pm

There are two levels of caching involved. One on Google’s end, another on ESI’s end. You wouldn’t get new data until both of them are cleared. A common way to handle Google’s cache is adding a dummy parameter to the end of a GESI function call, to point to a cell in the sheet. Then when you change the value of this cell, it would invalidate Google’s cache, and cause the function to refetch new data.

However, for ESI’s cache is time based, which you’ll just have to wait. E.x. characters_character_assets() as an hour long cache timer; meaning once you fetch data you wont get new data until an hour later. Or in other words, if you fetch your asset list, then move some items in game, ESI won’t reflect those changes until an hour has passed.

Amiean · April 19, 2020, 8:31pm

Howdy! Love the GESI, its a fantastic tool!
I am just wondering if there is currently a way to use the GESI to pull a list of the reprocessing a character has done with details like date/time, item reprocessed & yields