Last night at our church, one of the parent volunteers helping out with middle school and high school mid-week activities was taking photos of students with an iPad Mini. I asked him if he had an app for organizing and sharing the photos he was taking, and he said no: He’d tried unsuccessfully to put the images into Excel. He was just transferring the images to his PC, and then creating a PDF file by hand with the images and the student names / grade / school info. I suggested maybe we could put the photos and info into a Google Spreadsheet, since that way anyone with the link could have an updated version… plus the webpage would be “mobilized” so it could be viewed on a smartphone as well as a standard laptop/desktop computer. I explained we could keep the photos and document “unlisted” so only people we send it to could have access.

He agreed this might be worth trying, and emailed me the PDF he’d created this afternoon. I’ve blurred the student photos and associated info, but you can get an idea of where I started with this project. He currently has 60 photos of students in the PDF.

I figured out tonight that to get an image into an individual Google Spreadsheet cell, you need to post it online somewhere and use a formula to insert it. The Google tutorial “Insert images into a spreadsheet” explains how. It’s a basic formula:

=image(“http://direct-link-to-image.jpg”)

It’s important to get the syntax exactly correct of course… including the quotation marks.

The Google support article, “Album visibility,” explains how to change the privacy setting of a Picasa web album so only people with the link can view the photos in it. This is analogous to making a YouTube video “unlisted.” It can be publicly viewed and displayed on other websites (like a Google Spreadsheet) but no one can browse to the image on the public web, since the addresses of photos in it are hidden.

Tonight I used Skitch to take quick screenshots of each small image from the PDF file, named them for each student, uploaded the images to my “unlisted” Picasa web album, and then used the Google Spreadsheet “image” formula to embed them in a new spreadsheet I started. I added the student info in other columns, so the information can be sorted by each one: last name, first name, grade, and school. This is what the spreadsheet looks like on my iPhone in Safari. (running iOS 7)

You might keep these techniques in mind for your own rosters and directories. It’s a bit tedious since images have to be uploaded to a different website other than Google Spreadsheets for the photos to be truly “embedded” in individual cells which are sortable, but I think the work is worth it. Hopefully (if we decide to update it with remaining students in our church youth program) this will be a useful tool to help adult volunteers as well as church staff better know the names and faces of students participating in our youth programs. It might turn out to be something we share with students too, to help build community and face/name recognition.