Pokemon Battle Simulator - Google Sheets, GAS, HTML
Systems Design Project
The main objective of this project was to make a tool to iterate over new possible moves and pokemon with user-controlled stats by testing how those work out in battle.
All calculations consider formulas used in Gen 6.
The sheet consists of the main damage formula, stat modifier tables, accuracy modifier tables, critical hit chance calculations, moves database, pokemon database etc.
New moves and pokemon can be added by through custion menus built using Google Apps Script and HTML.
Click here to check out the sheet (sign in for the google apps scripts to work)
I used a formula to fetch the stats of the selected pokemon from the pokemon database sheet into the simulator sheet. This worked, but the user lost the ability to change any stat values as that would overwrite the formula by constants and the sheet would stop working as expected.
Hence, I wrote a script in the Google Apps Script to populate the stat cells with the formula each time the pokemon is changed, and then replace the formula with the actual value of the cell.
This resulted in a workflow where the user can modify any value they want without causing any issues to the sheet itself.
For the user, viewing the intermediate values in the damage formula is beneficial as those can be used to better visualize the effect different variables have on the final damage value. This also resulted in having less complicated formulas and reduced the need for named formulas.
For example, if the user notices that a particular move isn't as effective in battle, the user can check these calculations to see if the move's power should be increased, or perhaps the critical hit chance should be increased so that the attacking pokemon can ignore the target pokemon's defences more often.
Even though the designer will rarely see the actual functions, I still wanted to make the functions as readable as possible for the sake of debugging and being able to modify formulas easily. I used named ranges and functions for this which not only shorten the formulas used, but also act as documentation themselves that explains what the formula does quite easily.
e.g. GET_POKEMON_DATA("Mudkip", "Type 1")
is much more readable than
INDEX(Pokemon!A2:P28, Match("Mudkip", Pokemon!A2:A1000, 0), MATCH("Type 1", Pokemon!1:1, 0))
The formula
P_ROUND(FLOOR(P_ROUND(FLOOR(P_ROUND(P_ROUND(D32*F30) * 1.5) * G30) * D30) * H28) * C30)
would be completely unreadable if P_ROUND wasn't defined as a named function with
P_ROUND(value) = IF(MOD(value, 0.5)=0, ROUNDDOWN(value), ROUND(value))