These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Google sheets help
Author
Previous Topic Next Topic
h4kun4
Banana-Republic.
Shadow Cartel
#1 - 2017-01-24 16:17:15 UTC  |  Edited by: h4kun4
I need assistance with two formulae in google sheets regarding my corps character table.

First:
I know there is Evemon, but it would be far more straightforward for me to have it all in the sheet, since i need not only to know the character name that has a low skill queue, but also the account name of said character. That service Evemon is unfortunately unable to provide, therefore the spreadsheet is usually open regardless of Evemon.
All formulae i tried so far have ended with an error i couldn't locate. I suspect the catch is the charID. My formulae with only key id and vCode work perfectly fine but the charID is troublesome.

Example for the formula i use for account status (alpha or omega)
=IF(B2<>"",IMPORTXML("https://api.eveonline.com/account/AccountStatus.xml.aspx?keyID="&$K2&"&vCode="&$L2, "//result/paidUntil"),"")

the "=IF(B2" is so i recognize quickly if an account is missing in the list

What i need now is a formula that shows me how long the active skill queue lasts so i can put "status color overlays" with conditional formatting on it.

Second:
Is tied to the first, since i also need the charID working for that, i would also enjoy to have the SP of all the chars displayed there. Maybe there is a way to draw the charID directly from the API and then use it to read out the char name, SP, skillqueue and other important info, would mean less work if new members joined.
Samuel Sachs
#2 - 2017-01-24 18:38:50 UTC  |  Edited by: Samuel Sachs
Maybe you would use my SheetMon version:

SheetMon_V2

Any problems let me know.

So, about your idea, i like also to use spreadsheets in space and here is your solution to call the skillqueue


rowset name="skillqueue" key="queuePosition" columns="queuePosition,typeID,level,startSP,endSP,startTime,endTime"


Use this to get the position, were 0 is the actual skill as result and 1 the next:

=importxml("https://api.eveonline.com/char/SkillQueue.xml.aspx?keyID="&Accounts!B6&"&vCode="&Accounts!C6&"", "eveapi/result/rowset[@name='skillqueue']/row/@queuePosition")


And this to call the end time of the skill:

=importxml("https://api.eveonline.com/char/SkillQueue.xml.aspx?keyID="&Accounts!$B$6&"&vCode="&Accounts!$C$6&"", "eveapi/result/rowset[@name='skillqueue']/row/@endTime")


This returns a list with skills in queue and the endTime from them, now you can sort them in your mastersheet using a vlookup to filter the results easy.

The problem you run into when you try to call only the first skill is you have sometimes very short skills in queue, its better to call the complete list and sort after this.

Any help needed let me know, if i can i help :-)

SheetMon_V2 a gdocs spreadsheet what pulls lots of stuff, feel free to make a copy :-)
h4kun4
Banana-Republic.
Shadow Cartel
#3 - 2017-01-24 21:08:08 UTC  |  Edited by: h4kun4
so you can't just poll the end time and date of the whole queue?

Also your sheetmon template doesn't help me really, the formulae used are with vlookup or some other command i cant incorporate into my sheet. Still thanks, ill try to use some of it regardless.
Samuel Sachs
#4 - 2017-01-25 02:26:50 UTC
h4kun4 wrote:
so you can't just poll the end time and date of the whole queue?

Also your sheetmon template doesn't help me really, the formulae used are with vlookup or some other command i cant incorporate into my sheet. Still thanks, ill try to use some of it regardless.


Thats the downside on using spreadsheets, you have to filter yourself this is why you use a mastersheet and some subsheets to import the data and filter them before they go to the master.

As far as i know you can not call the end of the queue, so i import them always, filter them and give them out with the lookup functions.

SheetMon_V2 a gdocs spreadsheet what pulls lots of stuff, feel free to make a copy :-)
Zad Murrard
Frozen Dawn Inc
Frozen Dawn Alliance
#5 - 2017-01-25 07:41:25 UTC  |  Edited by: Zad Murrard
Recommended reading: http://www.w3schools.com/xml/xpath_intro.asp

Many many surprising things are possible with xpath.

This works for this particular question.

"/eveapi/result/rowset/row[last()]/@trainingEnd"
h4kun4
Banana-Republic.
Shadow Cartel
#6 - 2017-01-25 11:44:27 UTC
thanks a lot, i will look into it once i have time to test it properly