Importing Google Spreadsheet Data into Processing

17 02 2010

It is really powerful to be able to connect your Processing applet to external data sources.  This enable art infused with data and other great mash-ups.  I have done a couple of different projects that connect to Amazon Web Services, YQL and Google Spreadsheets.  The Google solution is perhaps the most flexible, because if the data does not reside in your spreadsheet you can import it via the spreadsheet functions.

Writing the Processing code to connect is pretty straight forward.  You can use functions like loadStrings to reference URLs that have your data.

String items[];
String[] pieces;
PFont  fontA = loadFont("StylisticSFBold-48.vlw");
//load from Google Spreadsheet Url
int itemCount=items.length;
for(int a=0;a<itemCount;a++){
pieces = split(items[a], '|');
textFont(fontA, 32);
text(pieces[0], 50, 25+a*(height/itemCount));

This works really well in the IDE… but when you upload it you will see that it will not work.  The problem is that the Java applet’s security does not allow it to access urls and content that are not on the same server.  If it could there could be viruses that downloaded malicous content to your machine.

Standard Approach – Sign the Applet

The standard option is to sign your applet and create a certificate for the user to approve.  To do this you must make sure you have the Java KeyTool and Jarsigner downloaded.  Then you need to issue the follow commands in ms-dos or the terminal on a Mac:

  • “C:\Program Files\Java\jre6\bin\keytool” -selfcert -keystore pKeyStore -alias yourKeyName
  • “C:\Program Files\Java\jre6\bin\jarsigner” -keystore pKeyStore GoogleImporter2.jar yourKeyName
  • “C:\Program Files\Java\jdk1.6.0_06\bin\jarsigner” -keystore pKeyStore GoogleImporter2.jar yourKeyName

You will need to replace the keytool and jarsigner location with the right ones on your machine, and replace “GoogleImporter2.jar” with your applets name.  Finally make sure to use your own key name.

If all goes well you will have successfully signed your app.  While attempting this for the first time I found the following two links to be very useful:

Alternative Approach – Redirect to a Local File

As an alternative you can use an intermediate script file to bring the external sites data to your server.  This is the approach I took in building my Flickr Kaleidoscope.  To create the same result as above, but without the signing the app I created a small php file that I can reference from Processing:

$response = file_get_contents($link.'&output=txt');
echo $response

To call a Google spreadsheet from this you simple call a url like the following:

There is an additional advantage to this approach is that if the data needs to be further processed and “massaged.”  In this case you can use the PHP functions to parse the data, parse xml, create\sort arrays and much more.

Whichever approach is pursued their is a lot of power in connecting Processing with data from the web.  The Processing visuals can be driven from data and have a dynamic element that comes from refreshable data.

Add to DeliciousAdd to DiggAdd to FaceBookAdd to Google BookmarkAdd to RedditAdd to StumbleUponAdd to TechnoratiAdd to Twitter



Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: