Allow reps to enter their own score using Google Sheets

Instead of the raw data being pasted in by an admin (eg from a report), the data is pulled in from a google form. So using Google sheets and Google forms you can allow your reps to enter in their scores for a leaderboard. 

 So reps can then fill out a form which then updates a google sheet which then updates the leaderboard.

1 ) Create the Google Form

  1. Open a form in Google Forms
  2. Add questions for the values you want your reps to enter

Recommended to use similar to the below questions to get a valid email and a number for the score. If the options to validate responses is hidden click the three dots in the bottom right of the questions card.

Google_form.png

This will make sure the user enters an email and not some random text.

image.png

 

  1. At the top of the Google form click RESPONSES
  2. Below, click More (Three dots at the top right of the responses)
  3. Click Select response destination
  4. Choose from these options:
  • Create a new spreadsheet: Creates a spreadsheet for responses in Google Sheets 
  • Select existing spreadsheet: Choose from your existing spreadsheets in Google Sheets to store responses

   5. Click Create or Select

You can find the response spreadsheet in the Google Sheets homescreen, the Google Sheets apps, or in Google Drive.

3 ) Total your response data

In your Google sheet that is now linked to your new Google form you want to add a new Sheet.
1. At the bottom left of your Google sheet click the + button

image.png

2. Add column names for the values you want on your Leaderboard. 
3. Enter in the Emails of the participants you want on the leaderboard

image.png

In this example we could create 2 leaderboards. 

  • The first is 'Number of Deals' which counts the amount of times a person has submitted the Form. 

This will count all rows in 'Form Responses' that have the same email as A2 (in this example it is all occurrences of john.doe@example.com as he is in A2)
=COUNTIF('Form Responses'! $A : $DA, A2)

image.png

So this will count all records on the 'Form Responses' sheet (which is linked to the Google Form). It will count all records that have the email the same as on its row.

  • The second is the 'Amount of Deals' which totals the values entered by the reps.

This will sum all the values in column B in Form Responses.
=SUMIF('Form Responses'! $A : $A, $A2, 'Form Responses'! B : B)

4 ) Create leaderboard

If you have not linked Google Sheets to your Spinify account yet check out How To Integrate Spinify with Google Sheets.

When creating a leaderboard on a Google Sheet choose “Google Sheet Data” under the goal option.

You will then be prompted to choose the Sheet from your Google Drive, along with the columns that relate to the Email and Score the team member has.

Further details on creating leaderboards is found in Create a Leaderboard

Was this article helpful?
0 out of 0 found this helpful

Comments

0 comments

Please sign in to leave a comment.