Thursday, May 9, 2019

Student Placement with a Google Sheet and STAR

I’ve been steadily making improvements to my Student Placement system and thought others might find it useful. It is a juiced up Google Sheet that integrates with the STAR RTI screener. We have been using it for years and the time spent on student placement has been cut in half. You will find two sheets linked below - a blank template and a demo file with some fake data added to provide context.

The system calculates student data automatically, both from data recorded with the STAR RTI screener and from teacher reporting. Data can be exported from STAR and easily added to the sheet. Then teachers add their own feedback - filling in services, behavior, attention to task, reading, math, and writing observations. The placement team organizes students into different classes for the upcoming year. Classes can be analyzed using different data points which can be changed dynamically using the controls all the way on the right of the class roster sheet sheet.

The system has several dashboards and can record two sets of placement, just in case a grade level is close to splitting or contracting. The dashboards include a class roster view, a chart view, an in focus page, and a numbers dashboard. We most often use the class roster and chart views during placement. The in-focus page can feed photos automatically, too, if you have a photo DVD from your photographer that uses student ID to name files.

I created an instructions page for administrators and teachers to help make this easier to set up and use. You can take a look at the posts written for the previous versions, too. They work in a similar way and some of the posts went into greater detail. Let me know if you try it. You can ask me questions or provide feedback here or on Twitter. Thanks!

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.