PopGoesTheWza / swgoh-tb-sheets

SWGoH Territory Battles Spreadsheets
MIT License
7 stars 2 forks source link

Enhancement Request: Deployment Orders #30

Open swgoh-kore opened 5 years ago

swgoh-kore commented 5 years ago

I'm in possession of a different sheet that was a predecessor to the current TB Sheet. I still use it because it allows me to send general Deployment orders; i.e. Middle, Bottom, Save.

As far as I know the current sheet doesn't have such a feature but by using the Estimate tab a TB commander could assign Deployment based upon "% GP" (column X) and then push the orders to Discord via webhook.

The "Save" category allows for holding back certain players (usually volunteers who can deploy at the last moment) in case margins are narrow for meeting the TP required for a given Territory star-level.

The sheet itself has many features that are completely broken that could be fixed but that would only be secondary to having separate tabs for each phase (2-6 for LSTB and 1-6 for DSTB) that contain a column of all guild members in the roster and a column with a combo box to select Middle, Bottom, or Save.

Please see attached shared Google Docs and output sample.

DSTB: https://docs.google.com/spreadsheets/d/1jwSY3owq5tOijsQ87rns6bJ4hft0A7dYZb4VYUum1OI/edit?usp=sharing LSTB: https://docs.google.com/spreadsheets/d/1biZ1rVEQyURHI6Etd-ikS-SaO8sSKqujAKnRLALzh_o/edit?usp=sharing dstb phase 6 dstb phase 6

swgoh-kore commented 5 years ago

I forgot to point out that it doesn't even pull SWGOH.GG information anymore. Because of this I use the TB Sheet to copy-paste the names into these sheets. But if a new tab (with drop-down for phase like the Platoon tab) was created it would just copy from Roster B2:B51.

PopGoesTheWza commented 5 years ago

@swgoh-kore I ran into a copy of this sheet a long time ago.

So the idea is to help TB Officers in drafting a Deployment plan, and issue the plan via a Discord webhook.

Currently the TB Sheets have the Estimate tab (which could benefit some attention) and the Breakdown tab (which has no interaction)

Do you have a precise idea of the functionnalities you would like? Maybe a mockup?

skiadas commented 5 years ago

Here are the elements I would like to see added (some are my additions to the sheet (link to my version of it https://docs.google.com/spreadsheets/d/1ppSj6m6BJ9HLnVGKmJ2j1SUvkEdbquc_UG_ja5M7l9M/edit?usp=sharing)

  1. A new "Deployments" tab, which follows the phase number of the Platoons tab. A single tab that updates with the platoons should suffice I think, rather than the 6 tabs in the other sheet.
  2. A "reserves" column either in the deployments tab or in some meta tab. this lists a number of names of people who have volunteered to be "saved" and not deploy until later based on where they are needed. This can follow the "Filtered" setting of the discord list.
  3. Two columns in the Deployments tab with members and deployment orders for them "save/middle/bottom". Optionally, conditional formatting if saved people are not in the reserves and if non-saved people are on the reserves.
  4. Computations somewhere for how much gp these members bring to middle/bottom respectively, and how much gp is on "reserve" via the saved. Preferably these computations will make suitable allowances for the gp used up on the CMs/SM (which goes directly in the corresponding zones regardless of where the person is assigned to deploy), and that can be set to some value (say 80k average) or it could be user-specified.
  5. A script that announces the deployment orders in discord. This is triggered by the "Send" buttons on the different phase pages, and is implemented in the sendWebhook function of the Code.gs file.
  6. A script that computes deployment orders based on the estimates setting and the reserves list. It is implemented in the function updateDeploymentsForSheet in the update.gs script (code probably needs to be cleaned up a bit, it was a rush job but seems to work for us). It works as follows:
    • Keeps tallies of the middle-assigned gps and the bottom-assigned gps, starting at 0.
    • Orders the members in gp order, starting from the highest one. Loops over them.
    • If the member is in reserves, it is assigned Save.
    • Otherwise the tallies assigned middle and bottom are compared to the target percent from the estimates page (via odds-ratio comparison). Then this member is assigned to the side that is lacking. For instance if the estimate is 40% middle and 60% bottom, for an odds ratio of 2/3, and the current ratio of assigned gp is more, then the person is assigned to bottom, otherwise the person is assigned to middle.
swgoh-kore commented 5 years ago

I would keep it simple. I prefer granular control of who deploys where. The main functions would be to show the estimated amount of GP going to each territory and being saved.

There are a couple of ways this can be estimated and summed up. But one part of the calculation is to remove the estimated squad GP per CM and SM from each player's Hero GP pool and apply it directly to the sum of TP in each location; generally 2 squads worth for Middle and 1 squad worth for Bottom, except Phase 5 for DSTB. This is also offset by where the SM for the phase appears, including Phase 4 of DSTB where the SM is in Fleet.

In the mock-up I provided a field where this estimated squad GP may be adjusted.

These calculations would also need to not operate on the assumption of 50 members, so counting members from the Roster tab will be required.

From here we can sum up the Hero GP assigned to each territory. One way to calculate this could be by counting the number of assignments and multiplying by the AVG-Hero-GP per player, after subtracting the 4 S/CMs worth of squad GP. More accurately, though, would be pulling the Hero-GP for each player, subtracting the estimated GP for 4 S/CMs worth of heroes, and then summing them based upon Deployment assignment (Middle, Bottom, or Save). The multiplier is reduced from 4 to 3 for DSTB Phase 4 where the SM is Fleet.

The last bit of functionality is the button that pushes a message via webhook to Discord. I've created a couple of additional fields meant to be placed on the existing Discord tab. The message would include the Custom Message and then 3 columns for each of the assignment categories (see screenshot in first post).

Here is my attempt at a mock-up:

https://docs.google.com/spreadsheets/d/1nMGOByn-CP97aGQqJw2hAL1CAK3PmN6X3vAFvVWce8I/edit?usp=sharing

PopGoesTheWza commented 5 years ago

I like this.