***Update for 2018-19 School Year***
Change occurs all the time, this year, the Community Based Instruction (CBI) program is no longer managing the school’s lost & found, so I had to shut it down (un-publish the site).
I was asked by one of my district’s Special Ed Supervisors to assist with the creation of a means for managing the school’s lost & found in our CBI program. We have a life skills room where the students are going to utilize the lost & found items to practice how to wash (dish washer or laundry) then catalog the items for return to the proper owner.
We came up with the idea of using a Google Form where the students would be able to select some basic answers as well as upload an image of the item.
This data would collect on a Google Sheet where I have setup a bunch of formulas (I’ll break this down later in the post) and a couple of tabs.
The Google Sheet would be embedded on a Google Site that parents and students could go to find their missing item(s). There they would also find the email address & procedure for notifying the life skills students.
Screenshot of CMS Lost & Found Website
The initial tab in the Google Sheet (FormResponses1[hidden from view]) had several formulas established & maintained using the Google Sheets Add-on, CopyDown. The following are the formulas established:
- Item number – automatically created item number established when a form is submitted.
- =ROW(B1)
- Item number hyperlinked – pulling the item number generated in the previous column to now hyperlink as a mail-to with formatted subject and body. This was an added challenge asked by one of my colleagues, “Wouldn’t it be neat if…” [Kim Lowden]. It actually required me to learn a new formula, ‘concatenate’, from my supervisor, Allan Johnson.
- =HYPERLINK(CONCATENATE(“mailto:cms-lostfound@wwprsd.org?body=To%20Whom%20It%20May%20Concern,%0A%0AI%20have%20identified%20an%20item%20listed%20on%20your%20site%20which%20belongs%20to%20us.%0A%0AMy%20child’s%20name:%20%0AHomeroom:%20%0AGrade:%20%0AItem%20number:%20%0A%0ASincerely,%0A&subject=CMS%20Lost%20and%20Found%20Item%20Number:%20”,B2),B2)
- Google Image ID – when the students submit the image as a file in the Google Form, a URL is generated. This URL will not allow the image to be displayed (formula to come). The ID number will be needed for a later formula.
- =right(F2,LEN(F2)–33)
- New Image URL – using the Google Image ID number collected from a previous column and combining it with a URL recognized for generating an image.
- =“https://drive.google.com/uc?export=download&id=” & G2
- Image – displaying the actual image in the cell of the Sheet (pulling from url in New Image URL column).
- =IMAGE(H2,4,300,300)
Now the biggest problem that I had was with displaying an image at a particular size. Even though the formula for the image was set to 300×300 pixels, when a new row is inserted with Form data, the cell size overwrites any heights to the default. I needed to create a whole new tab (Lost&Found) in the Sheet and hide the FormResponses1 tab. I formatted the first 1,000 cells to the size I want then utilized a formula (=FormResponses1!C3) to pull in this information for each column I wanted to display on the website.
***Update*** Less than a week after posting, Alice Keeler released a post about a script which would change the row height after a Form submit.
The last tab I created was to help with the organizing of the items for the students. I made an ‘Item # / QR Code’ tab. In this sheet, I pull in the generated item number and the image from the previous tab. I create a column where they could type in the student’s name and homeroom where the item should be returned. Finally, I have a QR Code generated based on the item number which the students can print out and attach to the item. The students can scan it when they need to find the correct item to return. I used the formula (=image(“https://chart.googleapis.com/chart?chs=300×300&cht=qr&chl=”&A2)) to auto-generate a QR Code.
Now whenever an item is claimed, they can go into the Sheet and hide the row for that item in the tabs: ‘Lost&Found’ and ‘Item # / QR Code’.
***Update #2*** We are no longer using email as the means to “claim” an item. I removed the hyperlink formula from item numbers because we are using an embedded Google Form below the spreadsheet on the site. This way the students have to check the Sheet of submissions to see who, what, and when. We have data validation setup to make sure submitters are inputting the item number and an email address. I have formMule setup to send a “receipt” to the submitter letting them know that we received their form submission. This has made the process easier to manage.