Coach Volunteer Information Update

The purpose is to ensure that volunteer coach 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 Saturday.
  1. Review the volunteers on the general volunteering page and ensure they are on the coach page
  2. Receive the updated Coach cdv by email
  3. Copy/Paste the coach/RCA pages to the Google Doc
  4. Run the notification script

Update System Coach Volunteers
General:  Go through the list of +Coach and +Asst Coach volunteers and if they are not on RCA page, add them to the coach page, then 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 +Asst Coach label
  5. For each division, 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 coach page.  If the number is non-zero, it will open a dialog box with the list of general volunteers wanting to coach.
  6. Quick procedure to remove coaches that are redundantly listed.  Some volunteers that are already in the system, sign us as new, redundantly.
    1. open another browser window and login to the correct season
    2. open the RCA page by clicking on Coach / RCA
    3. click on Coach List
    4. look at the list of general volunteers and for each name, see if the name is already listed on the RCA page.  If it is, click on the name in the general volunteer list and click the, Delete Volunteer from task, command. 
    5. When done with that step, click on the Approved check box near the top of the page, underneath the title, Coach Status, unchecking.  This will then display the coaches that are not yet approved
    6. Repeat the process of going down the list of coaches in the general volunteer list and deleting the ones that are already in the RCA page
    7. NOTE:  Some people enter their name into the system with strange capitalization.  This may cause them to appear alphabetically at the bottom of the page, out of order
  7. Now, we have to add the new coaches to the RCA page.  This is done by opening the division and promoting the parent of the player to coach.   Leaving your general volunteer list on the screen in the other browser window, in another browser window, for each name remaining in the general volunteer list:
    1. For each division in the +Coach or Asst Coach list, in the other window, click Div Coord / Select Division, and click the division in the dialog box
    2. This will open the division menu, click on the Coaches link in the division menu
    3. Click on Promote Volunteer to Coach, enter the last name and find the volunteer. Click their name. 
    4. Click the position Coach or Assistant as appropriate next to the parent's name that is volunteering
  8. Repeat the above procedure for each division starting at step 4, clicking on the item in the general volunteer window for +Coach
Update Google Docs
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 RCA-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 cell B1.  If it is equal to or later than the date of the email sent to you, then it has already been updated, and you are done.  You may close the files.

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 Coach CSV file
  2. In a browser window, open the Google spreadsheet, and select the tab at the bottom titled, Name Sorted.  Select the cell range, E10 to M444, and use the Google spreadsheet command Edit / Delete Values.
  3. Select Column Q, and use the Edit / Delete Values to delete all the values in the column.
  4. You can only move 200 entries at a time from the CSV to the Google spreadsheet.
  5. From the Coach CSV, select the range from name in A2, to the cell I200. 
  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 into Google.
  7. Go to the Google spreadsheet, RCA-List and select the cell E2.
  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 E column, but at the row immediately after the pasted area you already did.
  11. When done, select column E of the Google spreadsheet and sort it from A to Z.  Do this by using the Data / Sort sheet by column E, A to Z
    1. Update the date in cell C1 to today's date
    2. Select the tab at the bottom, Division Sorted.
    3. Select the range A2 to M446, and use the Google spreadsheet menu, Edit / Delete Values
    4. Select the tab at the bottom, Name Sorted.
    5. Then select A1 to the last cell in column M, and use the Google spreadsheet menu Edit / Copy.
    6. Select the tab, Division Sorted at the bottom
    7. Select the cell A2, and use the Google spreadsheet menu item, Edit / Paste Special / Paste Values Only
    8. select Column I, and sort Z to A (backwards), with the Google spreadsheet commend, Data / Sort Sheet by Column I, z to a
    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 at the bottom, 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-up window
    7. You should see the Column Q populating with values indicating whether a notification was sent.  Notifications are not sent to volunteers that are completely registered and certified.
    8. Wait for a couple of minutes to be sure the script did not error.  If it stops early, 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 !!