From Spreadsheet to Slideshow with a Raspberry Pi

As well as actually spending time playing in the theatre I also occasionally get involved in various computer related projects for one. This particular project was to help set up a rolling slideshow of “coming soon” events that we can display in the foyer of the theatre.

Of course this project lends itself to a raspberry pi so I got mine out and started work. Everybody (it seems) creates a slideshow on their raspberry pi, so there are many solutions to choose from. In this case (you’ll see why in a minute) I wanted a slideshow that looped through pictures in a directory and automatically coped with pictures being added and deleted. For now I’m using Eye of Gnome which does this admirably but this can easily be replaced in future.

So where are the pictures coming from? Well that’s where this gets interesting. The list of upcoming events is kept and managed in a Google Spreadsheet so I needed to take this information and turn it into a set of images for the slideshow. As the information in the spreadsheet is regularly updated I wanted to write a python script that could be run regularly by a cron job to pick up changes and create the new slideshow images.

Google provide an API to Google Drive that allows access to files and file information and they provide a python library to access this.
I followed the instructions here to configure a developers account and then grant access to the APIs you need (in this case Drive and Sheets). I decided to use the OAuth for Server to Server authentication to avoid the need for human interaction, so the other thing I needed to do was to make sure that the service account has access to the files it needs. I did this by sharing the relevant files and directories in Drive with the service account’s email address. I only needed to grant view access to the files as we’re only ever reading the data.

Then on the pi I needed to install a few libraries, the Google API of course, a date parsing module and Image Magick to help create the images.

sudo apt-get install python-pip
sudo pip install --upgrade google-api-python-client
sudo apt-get install python-dev
sudo pip install ciso8601
sudo apt-get install libmagickwand-dev
sudo pip install Wand

Once that’s done I could write the script to download the data. The Google API documentation is full of example code so it’s easy to get this working.
Firstly I retrieve the modified time of the spreadsheet, using the Drive API, to see if it’s changed since we last ran. If it has then I download the data from the spreadsheet and check each row to see if it’s been updated (each row has a unique identifier and a last updated time-stamp). For each updated row I delete any existing image and generate a new one.
Finally I record locally the most recently updated time of the rows so I know when to start from next time.

Note (because it surprised me) that the latest updated time of a row might actually be later than the modified time of the file as there’s sometimes a couple of minutes delay before the file modified time is updated.

That really was all there was to it. Well, okay, there’s actually a few other things going on too, but that’s for another post perhaps.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s