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

Syncing My Time – Googasync

My mobile phone is a Nokia. A real Nokia, running Symbian, not one of these Windows upstarts. I love it, it does everything I want a phone to do and does it very well. I can run my life almost entirely from it (although blogging usually requires a real computer at some point).

But as I do run my life from it, it requires me to keep track of my calendar on it, and therein lies a tale.

I have a variety of Google calendars, to track various things; my own activities and family birthdays being just two. This means they can be shared among people who need to have some idea of where I am at any given time. Of course Google do provide a mobile web interface to their calendars but its interface is almost entirely unusable and it relies on having a data connection so that was never going to be sufficient.

Until recently therefore I had synced my main calendar with my phone using the ‘Mail For Exchange’ application which comes free with the phone. This was really forcing it to do something it didn’t really like and the result was that I had my main Google calendar synced to a (secondary) calendar on my phone and resorted to the web to view the others.

I might well have continued like this indefinitely had this connection not stopped working recently and curious I looked around for something else.

I found Googasync. It’s a native Symbian application and although a paid for application it does have a free fourteen day trial. So I downloaded it and had a play.

The picture on the website had tempted me with a pair of screenshots, one of several Google calendars and another of the same calendars on the phone. Could it really do that? Well I started the application, entered my username and password and went to the select calendars option. It crashed.

I opened it and tried again. It crashed again.

Slightly irritated, I decided to give it an alternative chance to prove itself. I open it and clicked on the big ‘Sync’ button. This ran me through the settings screens again but this time I refrained from trying to sync more than one calendar. Having refused its option to delete entries from my existing calendar (bear in mind that my default calendar was not used by ‘Mail For Exchange’ I let it sync. This time, there was success. Each calendar now contained all previous items from both calendars.

From this point it seemed happy to go through the process of downloading my list of calendars from Google and syncing the ones I asked. Yes, this was exactly what I had wanted all along. My next challenge was to send a support query to find out if I would need to reinstall if I purchased it and more importantly to prove there was someone at the end of the company and that going through the purchase process would result in a downloadable (and usable) application.

That done, I did indeed purchase it and really I only wish I’d discovered it sooner. It’s not complicated, it just does what I want and really that’s all any application should do.

Pros:

  • It syncs multiple Google calendars to multiple Nokia calendars (syncing the main Google calendar to the default phone calendar).
  • It automatically re-syncs at intervals if you want (or manually if you don’t).

Cons:

  • It’s a paid for application and the initial price shown is excluding VAT.
  • Support is slow. I had to wait several days for a reply to my query. I doubt I’ll get much help if it goes wrong.
  • Automatic syncing is limited to a simple interval. There’s no concept of peak times.

Linux Scores

One of the things I’ve realised recently is that although I can’t sight read music properly it does help me to have the score to sing with rather than just the words.

This week having laid one song (temporarily) to rest and worked on another we started looking towards our next challenge. So my teacher suggested a site where I could purchase and print out the music for my next song. Cue an interesting technical problem.

While there are computers which can run a wide variety of operating systems in my house my primary system runs Linux. In fact I don’t remember the last time I booted into Windows. In general I don’t miss it. I have browsers, office software and a whole load of interesting and free applications to perform a variety of tasks. So, off I went to this website, credit card in hand to buy my song.

The website itself loaded flawlessly, I could search for and find the song I wanted, all I needed to do was to purchase and print it. It was of course here that the problems started. Music is subject to copyright (fair enough) so in order to control your use of it the website allows you to print your music only once and it must be direct to a printer, not for instance to a pdf. The problem is in that control. The website uses the Scorch plugin, a plugin that runs on Windows or a Mac but not on Linux.

My first thought was whether any other online music seller did support Linux but sadly I couldn’t find this song available anywhere promising. So I did what anyone would do in the circumstance – I googled some more and found a possible solution.

The solution suggested was to install Wine and then to install the Windows version of Firefox and then to install the Scorch plugin. I’ve last used Wine several years ago and it was decidely unreliable so I held out little hope but it was that or boot into Windows so I gave it a try. The installations all worked without a hitch and I was almost ready to roll. At this point my own caution nearly undid me as I attempted to test the setup by printing directly from Firefox to ensure the printer connection would work. After all you only get one chance at printing here.

The result was a computer that thought it had printed but a printer that produced nothing. My heart sank. Finally though, in a desperate act of optimism I decided to try loading the music website and printing the sample page direct from the plugin. Amazingly that worked and I went ahead to buy my music and print it out.

Linux certainly scored well today.

Google (doesn’t) know where we are

Google seems to know everything these days. One of those things is my location. On any computer or even on my phone Google will have a fairly good idea of where I am.

My phone has GPS so finding me accurately is pretty easy, if a little slow. So Google intelligently short circuit the process by identifying the location of any wireless network I happen to be in the vicinity of.
Ignoring the slightly dubious question of where Google happened to get that data from; it all seems like a pretty good idea.

So while we’re enthusing about technology, let me enthuse also about our mobile wireless access point. Connecting to the mobile network, it provides a wireless access point that our wi-fi only devices can connect to and have internet access wherever they are. Even our phones, which also have wi-fi, can use it for data rather than use their own network connection.

…hang on. Does anyone see a problem here?

Yes, you guessed it. Google has rather arbitrarily recorded the location of that access point as somewhere in London. Yes, it was actually at that location. Once. Now however it isn’t. So whenever I try to identify my location and I am near that access point Google gets very confused and moves me alternately back and forth between my actual location and the apparent point in London which I must surely be at (after all Google says so).

‘Luckily’ Google are offering an opt out of the wi-fi location database if we are prepared to rename our access point to give it a suffix of their choice. I think they may rather be missing the point here.