Friday, April 19, 2019

Administrative Internship Experiences Log Using Google Sheets and Forms

I built this system for students in the Educational Leadership program at Long Island University. Students are required to complete an administrative internship where 450 hours must be applied to 50 administrative competencies. This process demands a fair amount of logging hours and record keeping. Fortunately, it’s the perfect use case for Google Sheets and Forms. You may have found yourself to this post because you are an LIU student, or you may be looking for help creating your own logging system, either way, welcome. Be sure to leave a comment if you have any questions.

The system documented here allows students to use a Google form, from a computer or a mobile phone, to log competencies completed, time, and record detail. Once submitted, the sheet groups experiences automatically by competency, the dates are recorded and the hours tabulated. Experiences aligned the LIU “Internship Rubric” are automatically added under the appropriate strand. The number of competencies and the hours earned  are tallied. The log and rubric can be printed and added to the portfolio once the target 450 hours and 50 competencies are complete.

How do I set it up?
  1. Rename the file by clicking on the name "Copy of LIU Hudson School of Education: Administrative Field Experiences Form 2" on the top left.
  2. Open your Form.
    Look at the Google Spreadsheets menu. Click the item "Form" and then select "Go to live form."
  3. Copy the web address of your Form.
  4. In the form that opens, select and copy the entire web address from the browser address bar.
  5. Navigate to the Log page.
    On the bottom of the spreadsheet are 4 pages, Directions, Log, Rubric, and Form Responses 1. Click on "Log."
  6. Paste the form web address into cell A3. It is the orange cell near the top left of the sheet.
  7. Type the name information at the top of the "Rubric" page. Click the "Rubric" page on the bottom of the sheet and fill out the appropriate information at the top. You do not need to edit the hours completed. This is automatic.

How do I use this?
Using the Field Experiences form is easy! You must add every experience you have using the form alone. This adds all of the information that you need in the way that the system understands. You can open the form directly from link that appears in cell A4 once you complete Step 6 above. You can bookmark the form link on your browser or open it directly on your phone. As you submit experiences via the form, the hours total updates automatically in cell C1 and the competencies total updates in E1 at the top of the "Logs" page.

Can I edit an experience after I submit the form?
Yes. But ONLY on the "Form Responses 1" page below. If you manually edit the form responses, please remember that the Competencies listed in column C must use the same words and punctuation as those listed on the "Log" page. I recommend that you copy and paste.

Can I change anything on the "Log" page?
Nope. This page updates automatically when you submit a form. It is designed to be printed so that you and your mentor can sign a paper copy. Use the Form Responses page if you want to edit a date or an event.

How about the Rubric page? Can I edit that?
Yes. The only thing that can't be edited are the experiences (in the grey boxes) that are listed under each strand. These come directly from the "Log" page. Everything else is designed to be directly edited or completed after the rubric is printed.