I tried a small fix for the problem where the sheet recommends freezing DIP cadets when it shouldn't.
Thanks a lot, seems like a good suggestion. Included it in 1.80!
Is it just me or is the DIP Cadet Roster still broken for Ex Astris Scientia? The only Cadet DIP that is listed for me to keep is Dabo Girl Leeta and she can't do Ex Astris Scientia. I have Ensign Sato and Enterprise-D Picard in my sheet (I don't have Gaia Odo) and they are both listed as Space/Freeze. The Gaia Odo that I don't have is listed as Space but if I mark him as having him (just for testing) he is listed as Temporary Hold.
Weirdly Enough, if I remove either Picard or Sato the other one then says Cadet Roster, but... if I mark either one that I remove as cryo the one I left is changed back to Space/Freeze. The only way I can get Picard or Sato to list as Cadet Roster is to remove the other from the active column and not mark them as cryo.
It is a side-effect of the way the sheet makes recommendations for the DIP Cadet Roster. It is a fairly complicated set of recommendations to make, as there are several possible combinations of crew to cover the Starfleet/Female/Alien requirements. In addition, it needs to take into account whether these crew are active, frozen, or whether you do not have them yet.
In this situation, the sheet says that you do not need Sato, as you already have the Picard + Leeta combination, and it also says that you do not need Picard, as you already have the Sato + Leeta combination. If you disable either of the two, the sheet will (correctly) mark the other one as necessary again, as you have noticed.
After spending some quality time with the formulas to see whether there is any way of fixing this, I have managed to find a solution that fixes the strangest recommendations the sheet gives, such as spacing/freezing everyone. It does prioritise some crew combinations to do this, however, so it may nag at you to freeze Picard and Leeta and get Sato and Odo out of the freezer, despite the fact that both are valid combinations.
Trying to sort that mess out further is probably not impossible, but will make things much more complicated, so I'm leaving that for now. Wait for version 1.91 to see the new recommendations in action.
BTW @Leshy I heard that the Michael Burnham counting as alien crew is a bug that will be patched. I thought they were giving her Vulcan upbringing/training or something but I guess not.
I can see how those mixed recommendations would make things harder. Thank you also for all your continued work on this spreadsheet. This is a phenomenal resource that makes the game that much more enjoyable for me. My wife always jokes that she can tell that a game is going to take over my life when I start making spreadsheets for it. I'm glad to know I'm not the only person who can get lost in a spreadsheet black hole. Keep up the good work.
I've got something strange going on with Going Viral (E08M14) on the crew and missions page. On the crew page, I own Ambassadors K'Ehleyr and Shras. Both of them have Going Viral listed in their "why do I need" field. On the missions page, Going Viral has no crew listed in its "who can pass" field, and I'm trying to determine why those two crew suggest themselves. Was the DIP node with Communicator as its bonus trait locked at one point?
I found this in December and I want to congratulate Leshy for the good work.
I have a few more charatcers since the last update I made - in December - and want to know if a new update will be expected soon.
Thanks once agian Leshy.
I found this in December and I want to congratulate Leshy for the good work.
I have a few more charatcers since the last update I made - in December - and want to know if a new update will be expected soon.
Thanks once agian Leshy.
Uhm, did you read thread? This updates like weekly. It currently lacking only Landry, Mudd and Lissan.
And, well, 15 February stated on first page for last version is certainly waaay past December.
I found this in December and I want to congratulate Leshy for the good work.
I have a few more charatcers since the last update I made - in December - and want to know if a new update will be expected soon.
Thanks once agian Leshy.
Uhm, did you read thread? This updates like weekly. It currently lacking only Landry, Mudd and Lissan.
And, well, 15 February stated on first page for last version is certainly waaay past December.
Well, the idea is that I want to update my DB; however, since the update is meaning copy-paste all the crew and rechecking the missions, I would prefer to include also the crew that I have acquired since 15th of February in order to avoid redoing this operation again in a few days.
So my question was more like "is an new update expected soon, so I will wait for it before putting my new crew in the file ?"
Thanks
Well, the idea is that I want to update my DB; however, since the update is meaning copy-paste all the crew and rechecking the missions
After a while it takes like 5 minutes every week. Sorting by alphabet then by version really helps with mass porting crew, missions now can too be simply copy-pasted. And then sorting both list same way with active and cryo being last two sortings allow for very quick check "just in case" by alt-tab and page-down.
I've got something strange going on with Going Viral (E08M14) on the crew and missions page.
Had a look, and it turns out that there was an incorrect reference on the Missions page, so the eligible crew for 'Going Viral' were not correctly picked up. I fixed that, but then double checked and noticed that 'Going Viral' currently has no locked nodes at all
Not sure when that happened – I am pretty sure at release that one of the DIP nodes did require someone with the 'Communicator' trait. At any rate, given that there is no longer any specific crew needed for that mission, I have removed it altogether. That also makes the Episode 8 victory choice irrelevant, so that one is gone, too.
I found this in December and I want to congratulate Leshy for the good work. I have a few more charatcers since the last update I made - in December - and want to know if a new update will be expected soon.
Thank you The latest update is now in the OP, actually. Newest crew, a couple of fixes, and the review of stats and traits is approaching the end of the alphabet!
I found this in December and I want to congratulate Leshy for the good work. I have a few more charatcers since the last update I made - in December - and want to know if a new update will be expected soon.
Thank you The latest update is now in the OP, actually. Newest crew, a couple of fixes, and the review of stats and traits is approaching the end of the alphabet!
I'd like to parrot everyone else in this thread and pass on my thanks for your time and effort that you've invested into making this spreadsheet. Its made my life playing the game much more enjoyable now that I have a guide to fall back on to help me move forward with it.
Its just a small thing, really, but the missions page on version 1.91 isn't showing Episode 8 at the moment. Is that due to come with a bit further work on this version of the spread sheet?
First of all, thanks! Glad to know the sheet is of use to you
All relevant missions from Episode 8 are listed on the Missions page. The victory choice for Episode 8 is no longer listed, as it does not matter: the last missions for the Hirogen and Dominion victory choices do not require any specific crew.
First of all, thanks! Glad to know the sheet is of use to you
All relevant missions from Episode 8 are listed on the Missions page. The victory choice for Episode 8 is no longer listed, as it does not matter: the last missions for the Hirogen and Dominion victory choices do not require any specific crew.
Oh really? Well, I haven't got to Episode 8 yet anyway lol! Nearly there!
Still, good to know for when I do get to it. Thanks again
Slightly unrelated but @Leshy , as the master of Google Sheets, is there any way for me to save a sort procedure? I have a multi-level sort process that I organize my sheet by and I hate having to fill in the steps each time. Figured it anyone would know, you would!
As far as I know, there's no way of saving specific sort criteria. It's something that I would find quite useful as well, and I've seen the question asked around online as well.
You can save Filter views, but I do not think Filters can recreate a 'Sort by X, then by Y, then by Z' sort procedure, unfortunately.
As far as I know, there's no way of saving specific sort criteria. It's something that I would find quite useful as well, and I've seen the question asked around online as well.
You can save Filter views, but I do not think Filters can recreate a 'Sort by X, then by Y, then by Z' sort procedure, unfortunately.
Slightly unrelated but @Leshy , as the master of Google Sheets, is there any way for me to save a sort procedure? I have a multi-level sort process that I organize my sheet by and I hate having to fill in the steps each time. Figured it anyone would know, you would!
Hey guys, I am no programmer, but an avid follower of The Great Leshy's Spreadsheet. Google Sheets DOES have a way to do as described, as a programmed macro within google docs. You can access the macro creator under the "Tools" menu --> Script Editor. It requires some programming knowledge to utilize, but I know some basics and will gladly help out/spend some time in figuring out basic requests to share with the forum as to not take your time away from what you already do.
I also don't know if Leshy can add a sorting script to the sheet, as I know some people and software gets weird with custom macros due to security risks (malicious intent, which I know none of us has here ) However, scripts can be created and shared here so people who want to run them in their copy of Leshy's sheets can do so.
I created a basic sort procedure to see if this works for us (per Dext's comment), as I too like to resort a specific way. To run this yourself, open the Script Editor under the Tools menu, and replace the text there with:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Crew = ss.getSheetByName("Crew Members");
Crew.activate();;
Crew.sort(1)
Crew.sort(5)
}
In Laymen's Terms (what this does):
1)It anchors to the active spreadsheet (Leshy's, where you ran the script)
2)Creates a variable called "Crew", which is set to the Crew Member tab
3)Assigns the next action to the Crew Spreadsheet specifically
4)Sorts by column in the order specified. In this case, I sorted by Name first (column 1) then by Version number (column 5). You can add additional Crew.sort(#) commands to sort extra columns you want.
*note, the default is to sort descending. If you ever want to reverse that, you change the sort argument to (#, False). Example, Sorting Version number in reverse (to show the most recent crew on top) would say Crew.sort(5, False)
As I stated, I don't know too much, but I can figure it out quick. I enjoy this game, in part because of these forums and these efforts to make gameplay both efficient and productive. Let me know how I may help!
This is super useful to know, thanks a lot! I'm pretty okay with Google sheets (although by no means a spreadsheet wizard), but generally do not dabble a lot in macros, so I had not looked into them.
I have gotten a sorting script working in my copy of the sheet, but would have to look further into actually integrating something that is useful to more/most people, and doing it in a way that is easy to use. For now, the one that I have added to sort by Status, then by Stars (desc.), then by Name:
function sortbyStatus() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var crew = ss.getSheetByName("Crew Members");
var range = crew.getRange("A:H");
range.sort([{column: 7, ascending: true}, {column: 6, ascending: false}, {column: 1, ascending: true}])
}
I could see the collections being an additional requirement that people can toggle on or off as needed, but I will have to see how they work and how many there are before I can say anything really meaningful about them
I created a basic sort procedure to see if this works for us (per Dext's comment), as I too like to resort a specific way. To run this yourself, open the Script Editor under the Tools menu, and replace the text there with:
Amazing. I created my sort but now how does it run? It appears in Script Edtior, but it's not doing the action. Do I have to do something to toggle it?
@dext74 For now, to toggle, once it's in the script, put it for a test run. (There's a "Play" button on the top of the page). As long as the spreadsheet is still open in another tab, it will execute. To test, change the sort to something odd (I do stars, then name descending to mix it up), then test the script. It should restore the order by the time you click back to the script.
@Leshy I assume there's only one way the crew page needs to be sorted for people to just copy/paste to a new version. I know you have a lot of hidden columns and such. I did some research and there is a way to build the proper sort into your sheet (thus, everyone has access to the feature) without messing things up. I'm working on it now and testing a thousand times before I send it your way.
PS. Also, I think it's not a new bug. Because looking at what setting of 10 gives me after deleting freezer column I suddenly understand that I used said setting exactly because of said bug and just 6 will do nicely when it works properly.
Thanks for the info and screenshots. You're right: the column that the 'Best Active Skill' requirements were checking against, does not differentiate between Active and Frozen crew – it just checked whether you have them.
I have stealth-fixed this in the current 1.88 version, so it should now work correctly. Can you have another look?
The reason why it use to check BOTH active and frozen is because a number of us marked our
non-FFFE crew as cryo'd, and only FFFE as active. If we had crew that weren't FFFE (like Kotar with only 3 out of 5 stars), but they were still in our top Sec list, we'd know to work on them - it was also a safety net in case you accidentally cryo'd or mismarked a crew member. I use the spreadsheet to know which of my crew are "best" that I should be working on and keeping, and which ones that I can work on to airlock/actually cryo because they don't serve a purpose (and make more room for new crew). With the change to the 1.88+ versions, I can no longer tell which of my crew are actually "best" - or at least I'm going to have to redo how I fill in my crew sheet to try and produce the desired results Perhaps this was the reason you added a Notes column to the crew member page? I'd be interested in hearing how some of you are now marking your sheets for non-FFFE crew to keep track on what to do with them.
Also two suggestions:
1. in the Advanced Settings, add a section or additional boxes in the Starbase bonus for us to add in our [personal] bonuses we've gained thru the new cryo collections. I know we could just add them together and put that as our Starbase bonus, but it would be future proofing in case they do something funky with the math, as well as just an easy way to check you have the right numbers and didn't mess up the math yourself.
2. Any way to create entries in necessities for crew members needed for merging into a new crew member? i.e. marking Lursa Duras and B'Etor Duras as needed for The Duras Sisters (merge)
Thanks for the awesome spreadsheet and all the work you put into it - it's greatly appreciated by all of us who use it!!
@Doq'cha Until you brought this up, I didn't realize the change, but I'm new enough I don't have too many frozen yet to create the level of impact many players would.
In response to "How you mark your sheet", I alternate weekly:
Week 1: Copy over Mission completion sheet. Start crew from scratch. I mark all active players with their current level. If they are fully fused, I add an asterisk (*) I was putting 100 on the Cryo side before, but with the change you pointed out, we can just add an F or something for "Frozen" in a single column.
I go by level because when I look at what missions are coming up, I can instantly tell who is the closest to being ready to use (who to level next). I also sort by the "Why do I still need this crew member" tab, which allows me to see which missions have a lot of people who can clear. This is useful if you're tight on crew slots. Often you have a Rare (3*) that the sheet says you still need for a mission when you also have a Super Rare (4*) or two who can pass the same mission. So I space the 3* if I need the room for something that is better if a mission is the only req (I keep all Advance Cadet Mission candidates it flags).
Week 2: Since that takes time, for Week 2, I literally just copy the list over and don't update the levels. I add any new event crew I obtained, recheck the sheet for new space/freeze reqs and move on.
Of course with the new Collections system, I'll have trouble "reserving space" for leveling up common/uncommon crew, or crew the sheet says to space. Crew Slot management is helped sooo much from Leshy's sheet, but many will have trouble when its updated to keep for collections too!
Question: For Epic The United Federation: Of Two Evils, shouldn't there be a mission: cadet missions check for the Diplomacy node? Seems like Persis might be the only one who can get the rare reward for that - or am I mistaken?
Is it just me or is there something wrong with ver 1.91? The missions don't match version 1.89 (I usually just update every other week). Mission Victories doesn't list Episode 8, and Episode 8 itself is missing M14 Going Viral. I don't think it's just my copy because I see the same thing on the original right now. Or am I missing something?
Is it just me or is there something wrong with ver 1.91? The missions don't match version 1.89 (I usually just update every other week). Mission Victories doesn't list Episode 8, and Episode 8 itself is missing M14 Going Viral. I don't think it's just my copy because I see the same thing on the original right now. Or am I missing something?
Did you read the change log on the first sheet? That should be the first thing you do after you copy the new version to your drive. Episode 8 was removed because there were no locks and the victory choice was irrelevant for the rest of the spreadsheet.
Is it just me or is there something wrong with ver 1.91? The missions don't match version 1.89 (I usually just update every other week). Mission Victories doesn't list Episode 8, and Episode 8 itself is missing M14 Going Viral. I don't think it's just my copy because I see the same thing on the original right now. Or am I missing something?
Did you read the change log on the first sheet? That should be the first thing you do after you copy the new version to your drive. Episode 8 was removed because there were no locks and the victory choice was irrelevant for the rest of the spreadsheet.
Ah, thanks - I typically do read the change log but I must have forgotten this time. Also thanks to all who contribute to the sheet and idea on improving it. It's a huge help!
Comments
Also, quick update to 1.9 to add the latest event crew!
Is it just me or is the DIP Cadet Roster still broken for Ex Astris Scientia? The only Cadet DIP that is listed for me to keep is Dabo Girl Leeta and she can't do Ex Astris Scientia. I have Ensign Sato and Enterprise-D Picard in my sheet (I don't have Gaia Odo) and they are both listed as Space/Freeze. The Gaia Odo that I don't have is listed as Space but if I mark him as having him (just for testing) he is listed as Temporary Hold.
Weirdly Enough, if I remove either Picard or Sato the other one then says Cadet Roster, but... if I mark either one that I remove as cryo the one I left is changed back to Space/Freeze. The only way I can get Picard or Sato to list as Cadet Roster is to remove the other from the active column and not mark them as cryo.
It is a side-effect of the way the sheet makes recommendations for the DIP Cadet Roster. It is a fairly complicated set of recommendations to make, as there are several possible combinations of crew to cover the Starfleet/Female/Alien requirements. In addition, it needs to take into account whether these crew are active, frozen, or whether you do not have them yet.
In this situation, the sheet says that you do not need Sato, as you already have the Picard + Leeta combination, and it also says that you do not need Picard, as you already have the Sato + Leeta combination. If you disable either of the two, the sheet will (correctly) mark the other one as necessary again, as you have noticed.
After spending some quality time with the formulas to see whether there is any way of fixing this, I have managed to find a solution that fixes the strangest recommendations the sheet gives, such as spacing/freezing everyone. It does prioritise some crew combinations to do this, however, so it may nag at you to freeze Picard and Leeta and get Sato and Odo out of the freezer, despite the fact that both are valid combinations.
Trying to sort that mess out further is probably not impossible, but will make things much more complicated, so I'm leaving that for now. Wait for version 1.91 to see the new recommendations in action.
tl;dr: DB, please release a Female, Alien, Starfleet 2* crew. Thanks!
I can see how those mixed recommendations would make things harder. Thank you also for all your continued work on this spreadsheet. This is a phenomenal resource that makes the game that much more enjoyable for me. My wife always jokes that she can tell that a game is going to take over my life when I start making spreadsheets for it. I'm glad to know I'm not the only person who can get lost in a spreadsheet black hole. Keep up the good work.
I have a few more charatcers since the last update I made - in December - and want to know if a new update will be expected soon.
Thanks once agian Leshy.
And, well, 15 February stated on first page for last version is certainly waaay past December.
Well, the idea is that I want to update my DB; however, since the update is meaning copy-paste all the crew and rechecking the missions, I would prefer to include also the crew that I have acquired since 15th of February in order to avoid redoing this operation again in a few days.
So my question was more like "is an new update expected soon, so I will wait for it before putting my new crew in the file ?"
Thanks
Not sure when that happened – I am pretty sure at release that one of the DIP nodes did require someone with the 'Communicator' trait. At any rate, given that there is no longer any specific crew needed for that mission, I have removed it altogether. That also makes the Episode 8 victory choice irrelevant, so that one is gone, too.
Thanks for noticing!
Thank you
Great news
I'd like to parrot everyone else in this thread and pass on my thanks for your time and effort that you've invested into making this spreadsheet. Its made my life playing the game much more enjoyable now that I have a guide to fall back on to help me move forward with it.
Its just a small thing, really, but the missions page on version 1.91 isn't showing Episode 8 at the moment. Is that due to come with a bit further work on this version of the spread sheet?
First of all, thanks! Glad to know the sheet is of use to you
All relevant missions from Episode 8 are listed on the Missions page. The victory choice for Episode 8 is no longer listed, as it does not matter: the last missions for the Hirogen and Dominion victory choices do not require any specific crew.
Oh really? Well, I haven't got to Episode 8 yet anyway lol! Nearly there!
Still, good to know for when I do get to it. Thanks again
You can save Filter views, but I do not think Filters can recreate a 'Sort by X, then by Y, then by Z' sort procedure, unfortunately.
Hey guys, I am no programmer, but an avid follower of The Great Leshy's Spreadsheet. Google Sheets DOES have a way to do as described, as a programmed macro within google docs. You can access the macro creator under the "Tools" menu --> Script Editor. It requires some programming knowledge to utilize, but I know some basics and will gladly help out/spend some time in figuring out basic requests to share with the forum as to not take your time away from what you already do.
I also don't know if Leshy can add a sorting script to the sheet, as I know some people and software gets weird with custom macros due to security risks (malicious intent, which I know none of us has here
I created a basic sort procedure to see if this works for us (per Dext's comment), as I too like to resort a specific way. To run this yourself, open the Script Editor under the Tools menu, and replace the text there with:
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var Crew = ss.getSheetByName("Crew Members");
Crew.activate();;
Crew.sort(1)
Crew.sort(5)
}
In Laymen's Terms (what this does):
1)It anchors to the active spreadsheet (Leshy's, where you ran the script)
2)Creates a variable called "Crew", which is set to the Crew Member tab
3)Assigns the next action to the Crew Spreadsheet specifically
4)Sorts by column in the order specified. In this case, I sorted by Name first (column 1) then by Version number (column 5). You can add additional Crew.sort(#) commands to sort extra columns you want.
*note, the default is to sort descending. If you ever want to reverse that, you change the sort argument to (#, False). Example, Sorting Version number in reverse (to show the most recent crew on top) would say Crew.sort(5, False)
As I stated, I don't know too much, but I can figure it out quick. I enjoy this game, in part because of these forums and these efforts to make gameplay both efficient and productive. Let me know how I may help!
~Muldorian
This is super useful to know, thanks a lot! I'm pretty okay with Google sheets (although by no means a spreadsheet wizard), but generally do not dabble a lot in macros, so I had not looked into them.
I have gotten a sorting script working in my copy of the sheet, but would have to look further into actually integrating something that is useful to more/most people, and doing it in a way that is easy to use. For now, the one that I have added to sort by Status, then by Stars (desc.), then by Name:
function sortbyStatus() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var crew = ss.getSheetByName("Crew Members"); var range = crew.getRange("A:H"); range.sort([{column: 7, ascending: true}, {column: 6, ascending: false}, {column: 1, ascending: true}]) }By all means, feel free to add on and share!
Amazing. I created my sort but now how does it run? It appears in Script Edtior, but it's not doing the action. Do I have to do something to toggle it?
@Leshy I assume there's only one way the crew page needs to be sorted for people to just copy/paste to a new version. I know you have a lot of hidden columns and such. I did some research and there is a way to build the proper sort into your sheet (thus, everyone has access to the feature) without messing things up. I'm working on it now and testing a thousand times before I send it your way.
Weeeelll... Should be easy enough to code, but quite annoying in simple quantity.
The reason why it use to check BOTH active and frozen is because a number of us marked our
non-FFFE crew as cryo'd, and only FFFE as active. If we had crew that weren't FFFE (like Kotar with only 3 out of 5 stars), but they were still in our top Sec list, we'd know to work on them - it was also a safety net in case you accidentally cryo'd or mismarked a crew member. I use the spreadsheet to know which of my crew are "best" that I should be working on and keeping, and which ones that I can work on to airlock/actually cryo because they don't serve a purpose (and make more room for new crew). With the change to the 1.88+ versions, I can no longer tell which of my crew are actually "best" - or at least I'm going to have to redo how I fill in my crew sheet to try and produce the desired results
Also two suggestions:
1. in the Advanced Settings, add a section or additional boxes in the Starbase bonus for us to add in our [personal] bonuses we've gained thru the new cryo collections. I know we could just add them together and put that as our Starbase bonus, but it would be future proofing in case they do something funky with the math, as well as just an easy way to check you have the right numbers and didn't mess up the math yourself.
2. Any way to create entries in necessities for crew members needed for merging into a new crew member? i.e. marking Lursa Duras and B'Etor Duras as needed for The Duras Sisters (merge)
Thanks for the awesome spreadsheet and all the work you put into it - it's greatly appreciated by all of us who use it!!
In response to "How you mark your sheet", I alternate weekly:
Week 1: Copy over Mission completion sheet. Start crew from scratch. I mark all active players with their current level. If they are fully fused, I add an asterisk (*) I was putting 100 on the Cryo side before, but with the change you pointed out, we can just add an F or something for "Frozen" in a single column.
I go by level because when I look at what missions are coming up, I can instantly tell who is the closest to being ready to use (who to level next). I also sort by the "Why do I still need this crew member" tab, which allows me to see which missions have a lot of people who can clear. This is useful if you're tight on crew slots. Often you have a Rare (3*) that the sheet says you still need for a mission when you also have a Super Rare (4*) or two who can pass the same mission. So I space the 3* if I need the room for something that is better if a mission is the only req (I keep all Advance Cadet Mission candidates it flags).
Week 2: Since that takes time, for Week 2, I literally just copy the list over and don't update the levels. I add any new event crew I obtained, recheck the sheet for new space/freeze reqs and move on.
Of course with the new Collections system, I'll have trouble "reserving space" for leveling up common/uncommon crew, or crew the sheet says to space. Crew Slot management is helped sooo much from Leshy's sheet, but many will have trouble when its updated to keep for collections too!
Did you read the change log on the first sheet? That should be the first thing you do after you copy the new version to your drive.
Ah, thanks - I typically do read the change log but I must have forgotten this time. Also thanks to all who contribute to the sheet and idea on improving it. It's a huge help!