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.

size(300,300);
background(0);
String items[];
String[] pieces;
PFont  fontA = loadFont("StylisticSFBold-48.vlw");
//load from Google Spreadsheet Url
items=loadStrings("http://spreadsheets.google.com/pub?key=tV-tDRoZvFAdHJ_7fbKxDxw&output=txt");
int itemCount=items.length;
println(itemCount);
for(int a=0;a<itemCount;a++){
println(items[a]);
pieces = split(items[a], '|');
//println(pieces[0]);
//println(pieces[1]);
//println(pieces[2]);
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:

<?php
$link=$_GET['link'];
$response = file_get_contents($link.'&output=txt');
echo $response
?>

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

http://www.perlitalabs.com/Google_Importer/Get_Link.php?link=http://spreadsheets.google.com/pub?key=tV-tDRoZvFAdHJ_7fbKxDxw

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





What’s New… Used, and Made?

3 08 2009

I have been heads down building a couple apps. I have launched my first one and thought I had better post something on what I came up with and how the experience was.

It was really just some refinement to my New-Used-Made idea I discussed in an earlier post.  You can see the site at: www.NewUsedMade.com.  My effort consisted of:

  1. Adding some image resizing JavaScript.
  2. Adding some JavaScript to give No Image messages.
  3. Getting a URL.
  4. Adding some Google Analytics tracking.  (the trickest part was setting up analytics on the exit links clicked)
  5. Trying to monetize (an on going process)

The last step may bother some.  I love trying to figure out technology for tech sake, but the same is true for business.  How can you build something that people will like, and use… even if it means parting with some of their hard earned cash.  With this site it is not such a direct call to action.  I am hoping the mash-up of Amazon, Ebay and Etsy shopping will help people find items that they never would have before.

new-used-made shopping image

I will keep you posted on my learnings and progress… I also have another site coming up in the near future.

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





Spezify…. gave me the idea for New-Used-Made Search

3 06 2009

The other day I came across a new search site called Spezify (http://beta.spezify.com/).  It was pulling news, images, products from a variety of sources such as flickr, Amazon.com, etc.

Spezify - Obama Search

I like the visual layout, but it got me thinking how hard could it be for a basic version… hmm..  I have already been playing around with some api’s and some visual layout.  So I went ahead and built it.  In my simplified version I just focused on Amazon, Ebay and Etsy… so I call it New-Used-Made Search.

new-used-made product search

I could use a bit more graphic design, but I am pretty happy with the basic result.  It really returns a diverse product set with some fun and weird items.

add to del.icio.us : Add to Blinkslist : add to furl : Digg it : add to ma.gnolia : Stumble It! : add to simpy : seed the vine : : : TailRank : post to facebook





YQL: Yahoo Query Language… Query the Web…

15 04 2009

As a fan of traditional Business Intelligence and a student of the Amazon Associates Web Services I was amazed when I stumbled upon the YQL page of the Yahoo Developer Center.  My quick take is that using a syntax similar to traditional SQL one can write queries to web services offered by Yahoo and as well as any other API on the Internet.  There are some limits on the number of calls per day (100k) and per hour (10k).

So now comes the question about how to use it and take advantage of the possibilities it provides.  I am going to have to think about this… 

Querying RSS... and just about anything else on the web.

Querying RSS... and just about anything else on the web.

 

 

 

 

 

 

 

 

Yahoo Developer Network:  http://developer.yahoo.com/