Hoopla Helper - our nifty Google Sheets add-on - helps you set up daily recurring contests based on Metrics within your spreadsheet. The daily contest calculator will calculate scores within the time window you specify, then "freeze" that score when the contest ends. You can upload the contest scores to a Metric in Hoopla, and then display those scores on Leaderboards or use them in Metric Newsflashes.
How do I set up a daily contest?
- First, set up the Google Sheets integration in Hoopla.
If you've already set up the integration, click the "Authenticate with OAuth" button to grant Hoopla the permissions it needs to run this new contest script within your Google account.
- Determine which Metrics you want to use in your contest, and map them from Hoopla to Google.
- In your Google Sheet, add the Hoopla Helper add-on.
- From the Add-ons menu in your Sheet, select Set up a daily contest.
- In the pop-up that opens, select one or more Metrics to use in your contest. To multi-select, hold the control (Windows) or command (Mac) key while clicking on your Metrics.
- Specify the Start Time and End Time for your contest, and specify whether or not you want the scores to be calculated on weekends.
- The contest will run daily, within the time window you specify. Scores will not be calculated before the start time, nor after the end time.
- Click Create Contest.
Verify that the formula in the "Contest Score" column (usually the right-most column) is how you want to calculate each person's total score. By default, we will sum the "scores" from each included Metric.
- Example: Torrey's competing in a contest for daily calls and demos scheduled; she's made 16 calls and scheduled 4 demos since the contest began. Therefore, her total contest score is 20.
- If you want to make changes to that default formula, go for it! Just ensure that you have your changes copied properly down the entire "Contest Score" column. Don't alter the formulas in the other columns, as they will get overwritten.
In Hoopla, map your Contest Score column into a Hoopla Metric. Once it's synced into a Metric, you can add the Metric to a Leaderboard or Metric Newsflash, to be displayed on your Channel.
- When you set up your Hoopla-Google mappings within our app, make sure your spreadsheet is syncing with Hoopla frequently to ensure up-to-date contest scores.
- Contest start and end times should differ, and you should never use midnight.
- Ensure that the timezone in your spreadsheet is set appropriately. (File > Spreadsheet settings). Please note that this add-on will not work with a spreadsheet set to a German locale.
- By default, your contest will recur daily, but you can choose when to include the associated Leaderboard on your Channel(s).
- Avoid editing the Daily Contest Setup tab. If you do need to make edits, only the Start Time, End Time, and Weekends fields should be edited. Treat the other fields as read-only.
- Note - your contest scoring will only start once every 18 hours. So, if you change your Start Time after the contest has begun, then your change will take affect "tomorrow". To get around this, you can clear the "Last Started" cell in the Daily Contest Setup tab.
- During the contest window, you can manually run the scoring calculation if you don't want to wait for the next scheduled sync. (Add-ons > Hoopla Helper > Manually run contest score)
- Do NOT edit the formula in any column except for the one in "Contest Score".
Do I need all of the columns created by the Hoopla Helper?
Yes, you do! The Hoopla Helper needs the information in each of these columns to properly calculate the final Contest Scores. The only one that is safe for you to edit is "Contest Score".
Here's a breakdown of what each column represents for each person in the contest:
- (Metric Name) Starting Value - the value of a Metric at the beginning of the contest (determined by the Start Time you specified during setup)
- (Metric Name) Current Difference - the difference between the current value of the Metric, and its value at the start of the contest
- (Metric Name) Score - the "score" for a given Metric in the contest, calculated during the contest window. During the contest window, the "Current Difference" and the "Score" will match, but outside of the contest Start and End times, these columns will likely hold different values.
- Contest Score - the TOTAL score for a each player, based on the score from each Metric included in the contest. By default, the Contest Score is the sum of the individual Metric scores.