I play an online game called EVE Online in my free time. We use a set of google sheets for in game payouts. We need to automate the process for payouts as well as keep a running total of what payouts are done to which players on which day. We also need to be able to report on past data.
Link to google sheet document used today:
[url removed, login to view]
I have been playing around with scripting to see if I could accomplish the task however in December Google moved to using HTML/CSS for front end GUI.
Two html UI windows that will work with the google sheet and some simple apps scripts to copy data to/from sheets
HTML window 1:
1) This window will launch based on an apps script
2) it will populate based on data from the Loot Calculation sheet.
2a) the data that populates needs to be dynamic; Some days there may be 5 lines, other days there could be 8 or 10 or 21, etc.
3) Each line of data should have two fields.
3a) the first field should be read only and display the Item Name
3b) the second field should be editable, accepting only numeric values
3b1) the second field should read data, if available, from the corresponding Item_Quantity field.
4) there should be a cancel button
4a) the cancel button should close the window
4b) none of the values entered should be saved
5) there should be a second button (name TBD)
5a) when pressed, the button should save the Item_Quanity values to the Loot Calculation sheet
5a1) the button should bring up a new UI window
2nd UI window:
6)This window will populate based on data from the Calculations sheet.
6a) The data that populates needs to be dynamic; some days there may be 2 lines, other days there might be 6, 12, etc.
7) each line of data should have 4 fields
7a) the first field should be Name and should be read only
7b) the second field should be Tier % and should be read only
7c) the third field should be Number of sites and should be read only
7d) the fourth field should be Payout and should be read only
7e) At the bottom, there should be one free form text optional entry field labeled "Payout Notes"
8)Below the freeform field should be three buttons
8a) one button should be labeled Back
8a1) when clicked, it should take the user back tot the 1st UI window
8b) the second button label is TBD
8b1) when the second button is clicked, it should display a confirmation window/warning window
8b2) the window should have yes/no or ok/cancel buttons with prompt content TBD)
8b3) When "ok" or "yes" is select, the system should write the Payout Notes data to Settings(main) B43
8b4) when Cancel/No is select, the system should return the user to UI window 2.
If you review the attached sheets, I'll outline the process flow.
1) Users will run sites in the game.
2) Once finished, the users will got to the Site Log sheet and enter a numeric value for how many sites they ran.
3) The person who is going to do payouts, gathers all the in-game loot items
4) they enter the information into Loot Calculation sheets.(this piece should be automated with HTML UI window 1
5) Once the loot is entered, they review payouts on Calculations. (this piece should be automated with HTML UI window 2.
6) Once everything looks good, they send the corresponding payments to each player in game.
7) The information will get saved to another sheet for future data reporting, values on the Site log sheet and loot Calculation sheet cleared (this piece I will work on once the UI windows are complete, supplementing as needed with your help.
All code needs to have clear comments what it does.
I would prefer it follows the best practices found here: [url removed, login to view]