Update Google Docs
Here is the link the Google Spreadsheet to be updated. 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. To open the spreadsheet you will need to login to your Regional eMail account.
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 or curved back arrow in the Google spreadsheet. Do not confuse this with your browser button or command, be sure to use the Google spreadsheet command.
Notice that there are two tabs at the bottom of the spreadsheet, "FIrst Name Sorted", and "Position Sorted". These two spreadsheets have the same information, just sorted on a different column.
In general, you will now copy the volunteer data and paste it into the Google spreadsheet, but 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.
- 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 due to running out of rows, that is ok. If it stops for other reasons, such as complaining about an email address, email firstname.lastname@example.org to take a look at the problem.
- Check your inbox after a few hours. The most queries from volunteers come after the notifications. ;-)
You are Done !!