Enhance wins sheets to support selection for homepage randomizer

[macho-catt]

Dependency

• Google Apps Script: Fix data errors in spreadsheet used for wins page #2901
• Create wiki page "Hfla Google Apps Script Development/Review Process" #4134
• Add latest version of Google Apps Scripts to GitHub #3641

Overview

We need enhancements to Wins-form (Responses) to help PMs in identifying which wins entries should be displayed in the website homepage randomizer.

Action Items

• Follow sections 1-3 in the Wins Apps Script Development Process¹. Note that in this issue you will only be modifying the Apps Script wins-form-responses so all commands should be run from /google-apps-script/wins-form-responses
• As described in section 3a and 3b, you will provide a dev lead with the google account you wish to use for testing, and request the dev lead to:
  • share the Wins Form Admin Guide² as VIEWER
  • share the Wins-form (Response)³ sheet as VIEWER
  • share the Wins-form⁴
    • Note to lead: make a copy of Wins-form, including the issue # in the name, then share that copy with the developer as EDITOR.
• Continue following instructions in 3c and d. As described in 3d, you will have to make modifications to certain documents in the test folder.
  • Update settings in Wins-form so that responses are stored in your test version of Wins-form (Response)
  • In Wins-form (Response), add trigger so that insertLatestFormSubmitIntoReviewSheet runs when the test form is submitted.
• Continue following instructions through section 6 "Editing". As described in this section, editing can be done locally in an IDE or in the Google Drive cloud editor, and the clasp utility can be used to transfer code back and forth between the local worksation and the Google Drive as needed.
• Copy columns H through R from the "Homepage Summary" sheet to the "Review" sheet.
• Create a new sheet "Log". Label the fist column "Wins ID" and copy columns F through H from the "Homepage Summary" sheet to the "Log" sheet, to the right of the "Wins ID" column
• Setup a formula so that column "# of times appeared on website" on the Review sheet, is sourced from the "Log" sheet, based on matching the "Wins ID" columns on the "Review" and "Log" sheets.
• On the Review sheet, add a new column "Person ID" to the right of "Wins ID"
• Create a new tab/sheet "Persons" with columns "Person ID", "email", "# of wins IDs"
• Add code so that when a new wins submission is processed, a "Person ID" is assigned, and the "# of wins IDs" is populated, based on the assumption that each unique email address represents a unique "person".
• Test the code to confirm that both for new form submissions and for Review sheet updates, both the Review, Log, and Persons sheets are updated appropriately.
• You will not have to run main() for this issue, so if there are errors related to not having access to gh-requests, you can remove the gh-requests library and if necessary "comment out" main()
• Follow all steps through section 8 "Completing an issue". For this issue, only a single file google-apps-script/wins-form-responses/Code.js should be committed. Contact the dev lead via Slack to arrange the review.

Merge Team/dev Lead

• ensure that all access to Wins Form Admin Guide², the Wins-form (Response) Sheet³, and the Wins-form⁴ have been revoked.

Resources/Instructions

• Google Apps Script
• _wins-data file
• wins page JS

Footnotes

1. Wins Apps Script Development Process ↩

2. Wins Form Admin Guide ↩ ↩²

3. Wins-form (Response) Sheet ↩ ↩²

4. Wins-form ↩ ↩²

[ExperimentsInHonesty]

RW and I met, and we are going to do the following

• combine the content on the Homepage summary prototype tab with the Review tab so that there is no risk of the incorrect one being updated.
• add wins id Google Apps Script: Add unique IDs as object keys for each wins entry #2385
• create a new tab called Log, which should be manually updated every time a new win is manually added to the homepage randomizer. This way we can see if someone has already been featured and when
• in the column "# of times appeared on website" it should pull that data from the Log tab by user id
• add person id
• create a new tab called person
• This should have a list of all people and their ids, and the number of wins
  • schema: id, name, email, # of wins ids

[roslynwythe]

@ExperimentsInHonesty the issue has been updated as per your comments in the previous comment.