Referee Volunteer Information Update

The purpose is to ensure that volunteer referee information is up to date in our system, and posted online in our Google Docs.

The process is performed once a week during registration, and once a month during the season, always on the weekend, preferably early Saturday.
  1. Review the volunteers on the general volunteering page and ensure they are on the RRA page
  2. Receive the updated Referee csv by email
  3. Copy/Paste the Referee csv to the Google Docs
  4. Run the notification script

Update System Referee Volunteers
General:  Go through the list of +Referee volunteers and if they are not on the RRA page, add them to the RRA page, and delete them from the general volunteer page.

  1. Open a separate browser window.  
  2. Login using that window, ensuring you are in the correct season for registration, using
  3. Click on Admin/Gen Volunteers
  4. Click on the word, Show, next to the +Referee label
  5. Click on the number next to the word, Currently:, unless it is zero.  If it is zero then there are no general volunteers to transfer to the referee page.  If the number is non-zero, it will open a dialog box with the list of general volunteers wanting to referee.  If there are no new referees, you still need to keep going in this process, to update the web site and run the notification script, in case referees changed their status since the last notification.
  6. Open the RRA page and click on the link, Promote a Volunteer to Referee.
  7. Promote the persons in the general volunteer list to referees.   Leaving your general volunteer list on the screen in a browser window, in another browser window, for each name in the list:
    1. Enter the last name of the person
    2. Click, Search
    3. Select the correct name
    4. Confirm, linking the children
    5. Click Submit
  8. Let the know that you have updated the list, and the RC will send you the Referee csv file, to be used with the instructions on the right hand side of this page.
Update Google Docs & Run the Notification Script
You will be emailed the CSV file to use in this updating process.

You login to the Google account, to update the Google spreadsheets.  The password will be emailed to you.  To login, goto and enter the account and password information.  Using this account gives you the correct editing rights for the spreadsheets, and when you run the notification script, the emails will be sent from this account, rather than from your personal account.

The Google Spreadsheet to be updated is RRA-List .  This spreadsheet is a combination of formulas, conditional statements for notification scripts, conditional formatting, and pasted values.  You must be careful and follow the directions for pasting the values exactly, or you may damage the formulas.  

If you make a mistake that damages the spreadsheet, use the Edit/Undo in the Google spreadsheet.  Do not confuse this with your browser button or command, be sure to use the Google spreadsheet command.

Check the date in B1 on the RRA-List.  If the date on the sheet is equal to or later than the email, then someone has already updated it, and you can just close the files.  You are done.

In general, you will now copy the Coach csv information that is mailed to you each week, and paste it into the Google spreadsheet.  Here are the details that must be followed carefully.

  1. Open the Referee csv file
  2. In a browser window, open the Google spreadsheet, and select the tab at the bottom titled, Active Referees.  Select the cell range, D5 to K748, and use the Google spreadsheet command Edit / Delete Values.
  3. Select Column P, and again use the Edit / Delete Values to delete all values in the column.
  4. You can only move 200 entries at a time from the CSV to the Google spreadsheet.
  5. From the Referee csv, select the first name, scroll down to cell row 200 and with the spacebar held down, click the cell H200. 
  6. Using the browsers command line, do an Edit / Copy.  Note, do not use a right click copy with the clipboard, it is unreliable at transferring this information.
  7. Go to the Google spreadsheet, RCA-List and select the cell D2.
  8. Use the browser command menu to Paste into this cell.  Note, do not use the Google spreadsheet paste command, the results are not reliable
  9. Without selecting a cell, scroll down to see where the paste ended
  10. Repeat the copy/paste procedure, to copy 200 rows at a time from the CSV file, and paste it into the Google spreadsheet in the D column, but at the row immediately after the pasted area you already did.
  11. Update the date in cell D2 to the date of the email that you received.

Run the Notification Script
This step runs a script that uses the data and calculated values to compose an email to each volunteer to tell them what they need to do next to complete their registration and training.

The first time you do this, have the results of your spreadsheet update checked to avoid possibly sending hundreds of alerts to the wrong people or with the wrong information.  

When you are ready,
  1. Select the tab, First Name Sorted
  2. Select Column Q
  3. Delete the values in column Q with your delete key
  4. From the Google spreadsheet menu, select Tools / Script manager
  5. Select sendEmails, and click Run
  6. Close the pop-ups
  7. You should see the Column Q populating with values indicating whether a notification was sent.  Notifications are not sent do volunteers that are complete registered and certified.
  8. Wait for a couple of minutes to be sure the script did not error.  If it stops, such as complaining about an email address, email to take a look at the problem.
  9. Send an email to that you have completed the update.

You are Done !!