Adding photos to a Google Sheets gradebook and creating a seating chart

One of the more frustrating aspects of Google Sheets is importing images. If you import an image with “insert image” menu, it will just “float around” rather than being attached to a certain cell. The ideal way of importing images to a gradebook would be to import a Google Drive folder containing the images (in alphabetical or numerical order) and then having them automatically assigned to the students’ names. Unfortunately that option isn’t available in Sheets, nor have I found a script which would do just that on the web.

There is an =image(URL) function which can load images from an URL. Unfortunately you can’t load images from your drive, because the URL is not directly accessible (unless you make the folder containing the images public).

The fastest workflow I have come up with is the following:

1. upload the photos to a Google+ album
2. import the images from the album (thumbnail URLs) with =image(URL)
3. use the index & match functions to move your images to where you need them (seating chart, student report, etc.)

You can speed up the import process if you get the URL from the neighbouring cell rather than pasting it into the formula each time (see here). Copying and pasting from a Google+ album doesn’t take long (about 2 minutes for 20 students if you have their photos already uploaded).

Depending on where you want to move the photos you can use a variation of the index/match formula. For creating a seating chart I create one row with a pixel height of about 100 and use the row below for the caption (using data validation is very handy here). Finally I simply have the row above find the corresponding image fromt the “import photos” sheet” using:

=iferror(INDEX( 'photo import'!$C:$C,MATCH(B3,'photo import'!$B:$B,0)))

You can then conveniently create a seating chart using the dropdown list of the students’ names instead of having to move around the images.

To see this in action just make a copy of the tutorial file.

Unfortunately the Android version of sheets doesn’t display photos (yet). So, if you want to have a copy of the seating chart on a mobile device you will have to download the sheet as a pdf file.