Update Google Docs
You will be emailed the CSV file to use in this updating process.
You login to the Google account, email@example.com to update the Google spreadsheets. The password will be emailed to you. To login, goto http://docs.ayso26.org
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.
BE ABSOLUTELY SURE NOT TO UNHIDE COLUMNS. THEY CONTAIN PERSONAL INFORMATION SUCH AS PHONE NUMBERS AND EMAIL ADDRESSES AND WOULD BE VISIBLE TO EVERYONE ON THE INTERNET.
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.
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.
- Select the, First Name Sorted, tab at the bottom
- caution, do not to delete any rows in the spreadsheet. Doing so will delete the formulas in the spreadsheet.
- delete the data (not formulas) from many rows, to be sure the new data will overwrite all the old data in the spreadsheet.
- select e7 to m636
- delete the data in those fields (but not the rows)
- Select the, Position Sorted, tab at the bottom
- delete data from the bottom rows
- select row 10 to the end of the sheet
- delete the data from the rows, but not the rows
- Go back to the, First Name Sorted, tab
- Copy and paste each csv into the, First Name Sorted, as explained below
Copying Volunteer Data to Google Spreadsheet
- Open the spreadsheet in your desktop spreadsheet program, such as Excel or Numbers
- Note that the columns are headed by, Job Title, to, Certifications.
- Select the data cells from the leftmost to the rightmost columns, skipping the header row, and for all data rows, except that you should not choose more than 200 rows. The Google spreadsheet will not permit pasting more data than that in one paste operation. Consequently, if the csv file has more than 200 rows, you have to select/paste multiple times to get all the rows.
- Copy the selected data to your clip board.
- Note that you will be pasting the cells into the Google spreadsheet starting in Column E. Although you have more columns in your CSV than appears to be in the Google spreadsheet, there are hidden columns, and they will match. Do not unhide the hidden columns. The hidden columns contain personal data, and if they are unhidden, the personal data will appear on the web site. Do not unhide them even for a moment.
- Past the clipboard using the browser paste function. It is important to use the browser paste command, because you are pasting into the spreadsheet from your desktop clipboard.
- Repeat the above Copy/Paste action until all the rows of all the csv files are in the spreadsheet.
- Repeat for each of the 3 spreadsheets.
- Then go to the panel on the right.
Finishing Up the Google Spreadsheet
- Copy from Name Sorted sheet
- Update the date in F1
- Select column H and use the menu, Data / Sort Sheet by Column H, A->Z
- Select the range of cells that have information from the entire spreadsheet, starting from A3
- Using the Google spreadsheet Edit/Copy command, copy all those cells to the Google clipboard
- Update Position Sorted sheet
- Select the tab, Position Sorted
- Select the cell A3
- Use the Google spreadsheet command, Edit / Paste Special / Paste Values Only
- Update the date in F1
- Select the column E and use the menu, Data / Sort Sheet by Column E, A->Z
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,
- Select the tab, First Name Sorted
- Select Column Q
- Delete the values in column Q with your delete key
- From the Google spreadsheet menu, select Tools / Script manager
- Select sendEmails, and click Run
- Close the pop-ups
- 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.
- 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 firstname.lastname@example.org to take a look at the problem.
- Send an email to email@example.com that you have completed the update.
You are Done !!