I’ve been recently having a lot of thoughts on live data and visualisation. Tweet Splash presents data available through Twitter API. This time, I’m going to set up cheap event tracking, which you can very easily use in many different situations and review later on Google Spreadsheets.

1. Setting up and playing with Google Docs: Forms

Log into your Google Account and go to Google Docs. Press “Create New” -> Form
I’ll call my form “Blog Searches”. Let’s have only one “question” which will represent our event or query made by a user. In my case, question title is “keyword”, question type Text and question is required. Click “Done” and make sure you delete “Sample question 2”. Save the form and click the link at the bottom to see the public version of your form.

It should look more or less like this. You can try, if it works by posting a test keyword, then go back to the document list and enter the form again, this time in a Spreadsheet form.

Now it’s time for the hacky part. Go to the public version of the form and look up the source (usually right-click “View Source” ;] ).

We need to retrieve 2 values: Form Action URL – to know where the form is posted to and Input Text’s Name, to know what’s the name of out “Keyword” parameter.

Form Action URL will be here (in red, it’s the attribute value of “action” in the “form” tag):

<form action="https://spreadsheets.google.com/formResponse?formkey=dDVCMlYzMmpuWTg0VUhZUkltaXl5OEE6MQ&amp;ifq" method="POST" id="ss-form">

Input Text’s Name (in red, attribute value of “name” in the only “text” input field):

<input type="text" name="entry.0.single" value="" class="ss-q-short" id="entry_0">

Now let’s try a small trick I discovered. Usually the form data is sent through a POST request, however turns out Google doesn’t mind, if you send values over a typical GET, which means you can put them as a part of your URL! In my case I’m going to this URL in my browser (note that I ignored the “ifq” parameter):

https://spreadsheets.google.com/formResponse?formkey=dDVCMlYzMmpuWTg0VUhZUkltaXl
5OEE6MQ&entry.0.single=Testing+GET+form+submission

Basically, to the URL, you append an “ampersand symbol”, name of the field e.g. “entry.0.single”, an “equals sign” and finally a message. Remember that a text in a URL has to be properly encoded e.g. spaces are turned into a “+” or “%20”, a browser will do it for you.
If you check your spreadsheet now, you should see the message:

Now I will show two methods for mixing this up with your website or service: sever side and client side.

2a. Submitting an event – Server side

I’m going to show the server side method first, since it’s really the best way to do it. If you can you should avoid exposing tricks like this, primarily because it’s easy to recover your form URL from a piece of a publicly available JavaScript, which means somebody might play a nasty trick on you and post some trash into your spreadsheet. On the other hand, if you do it through JavaScript, the user can decide not to be tracked by turning JavaScript off. Here are some examples of a server-side post.

In PHP there are several ways of fetching a URL, this is an example of posting a search query made by user. You can put this code in your index.php and it won’t be called unless parameter “s” is passed. Change it to the parameter name you are using for search or page name, or put a different version of the fetch in different files and provide a predefined event description string.

if (isset($_GET['s']))
file_get_contents(
'https://spreadsheets.google.com/formResponse?formkey=dDVCMlYzMmpuWTg0VUhZUkltaXl5OEE6MQ'.
'&entry.0.single=' . urlencode($_GET['s']) );

Java: (works also on App Engine)


public class ExampleServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse resp)
			throws IOException {
		resp.setContentType("text/plain");
		if (request.getParameter("search") != null &&
			!request.getParameter("search").trim().equals("") )
			ExampleServlet.touchURL(
		"https://spreadsheets.google.com/formResponse?formkey="+
		"dDVCMlYzMmpuWTg0VUhZUkltaXl5OEE6MQ&entry.0.single="+
		URLEncoder.encode( request.getParameter("search") , "UTF-8") );
	}

	   public static void touchURL(String address) {
	    	  try {
	              URL url = new URL(address);
	              url.openConnection().getInputStream().close();
	          } catch (Exception e) {
	        	  e.printStackTrace();
	          }
	    }
      }

2b. Submitting an event – Client side

If you’re not lucky enough to be able to modify your backend code e.g. your using Shopify or similar, but you have access to the site’s templates and you can put in some JavaScript, you can still submit an event. Here’s how you do it in JS:


(function() {
var paramsplit = location.href.split('s=');
if (paramsplit.length > 1) {
var keyword = paramsplit[1].split('&')[0];
var ka = document.createElement('script'); ka.type = 'text/javascript';
ka.src =
'https://spreadsheets.google.com/formResponse?formkey='+
'dDVCMlYzMmpuWTg0VUhZUkltaXl5OEE6MQ&entry.0.single='
+ keyword;
var ks = document.getElementsByTagName('script')[0];
ks.parentNode.insertBefore(ka, ks);
}
})();

The script above performs a string split looking for parameter “s=”, which in my case is the search query parameter. If there is none, there will only be one element in the array – the location url itself and the rest of the script will not be executed. Otherwise, another split will separate the keyword from other parameters and the URL to the spreadsheet form will be fetched like an external script file.
This method has some pitfalls. First of all, anyone can see this code and start sending some trash or fake data. It’s also not very clean, because it causes a JavasScript parse error. The browser basically tries to execute the fetched HTML Form code as JavaScript. In order to avoid this you can deploy a simple Google App Engine application or a PHP script somewhere that fetches the URL but ignores the content and provide the URL to this application instead of the Form URL. You can use the Java example from before.
Update: you can also retrieve it as a valid response by calling the form through Yahoo Pipes! Check out my next article on how to do that. Great if you’re expecting a lot of form submissions but don’t want to host it 😉
To hide your code you can try tools like http://www.javascriptobfuscator.com/ , but currently there isn’t a 100% secure method of doing this, that’s why server-side submission is the best option.

3. Reviewing collected data

Let’s get back to the spreadsheet now. Of course, we can just observe the events here just as they are, but there is just one more trick I will reveal 🙂
Go do a different cell e.g. D2 and try a sorting formula:
=Sort(A1:B1000, A1:A1000, FALSE)
This will sort the whole data set based only on the date. The last attribute is FALSE which means it will be descending sort. This way you don’t need to scroll down to see the newest events.
You can also publish the top of the sorted data set as an RSS feed, by going to the Share menu, “Publish as a web page”. Set automatic publishing (unfortunately far from real-time ;/ ) and generate a link in the appropriate format to your sorted data set in cells e.g. D3:E15. You can use tools like http://twitterfeed.com/ to link it to your Twitter notification account!

4. Use cases

I started off with a simple use case of event logging on websites, but there are many other ways in which you can use Forms. You can do any type of logging information: from servers, mobile devices, receiving errors with stack traces, receiving other server notifications, keeping a simple backup database. First off, a lot of you might say: this is ridiculous! you don’t use forms for suff like this! You use specialised analytics and loggers etc. That’s true, but still the beauty of Forms is it’s simplicity. You can very quickly deploy any kind of logging or tracking and see, if it’s even worth going further. So, before you spend hours implementing a cool fast real time notification system for your platform, see what kind of data you might get by taking Forms for a spin.
And that’s it for now. Next time i’ll show how to deploy a dedicated real-time event tracking application.