Well, moving forward on this project. I now have the script getting all the values from the case manager submitted form and then looking up successfully the course schedule for the student from a second spreadsheet of schedule data that I pulled from the Aeries student database. At some point that could be a live lookup, but for now it's dependent on a human to keep it accurate and up-to-date.
Next, it pre-fills the course info and IEP meeting information on a form for the General Education teacher to fill out and emails the teacher with a link. When they click the link, it brings them to the feedback form already in progress with almost half the information already filled in. No longer do they have to fill in their own class and double check the IEP date and time. (of course, at this point the IEP could get rescheduled, but that should be a separate thing on their calendar for another project at a later time!)
One item on my list is to make this Gen Ed version of the google form look slick and professional by using the school colors and logos now that Google allows us to customize their forms in that way. I'll get this pretty good looking before I show it to my Admin., but all that is cosmetic and the guts of the program currently work how their supposed to.
The next step is an Autocrat PDF creation off of my template feedback form that I created about a year ago for VHS. I have 3 or 4 tweaks left on it at this point, until the Admin. sees it and offers changes.
At this point I want to record for posterity the methods I used to dump my spreadsheet data. I had to go into MS Outlook and find my campus's "All Staff" email list. Then I added all the names on that list to my contacts in Outlook. This was in the Outlook program, not webmail like a lot of people like to use for outlook. When all the names are in your contacts, you can export your contacts as a .csv file that will convert nicely into google sheets or excel, etc. It has loads of empty columns that I don't use, but it has about 3 that I needed to get. These were the account names of all the teachers and staff on campus.
All our district has the domain @vacavilleusd.org but their account names are different. Usually it's the first name and the last initial (which I could make up with a spreadsheet function if it worked all the time). However, sometimes there is already an "AndrewH" account and they do "AHyland" as the account. So now I have that list from which to pull email addresses whenever I need. I made a named range to look up in and concatenated the account name with the vacavilleusd.org domain.
I did some similar data pulls for schedule from Aeries (teacher, course, period) and special ed info (like testing accommodations and behavior plan status, dates for next IEP, etc.) from SEIS--our Special Ed Information System. I can detail how I pulled those later so that I can replicate them next time and for any other campus and/or district that might use this idea.
Links:
Link to Part 1
Link to Previous Part
Link to Next Part
No comments:
Post a Comment