Google Chart API with Gaelyk on the Google App Engine

Note: Github available here. It works best with the current SpringSource Tool Suite.

Having had evening to spare, together with a colleague I came up with the following nano-project:

Write a prototype for creating a chart from some series of numbers, which is hosted in a cheap cloud.

So this brings me to: “Write a prototype (Groovy) for creating a chart (Google Chart API) from some series of numbers (taken from spreadsheets of Google Docs), which is hosted in a cheap cloud (Google App Engine).”
Some day, I will have to fix this Google addiction – however, Groovy is not affiliated to Google at all.

Evaluation of Frameworks

Wanting to combine Groovy and the Google App Engine, I immediately came across two different frameworks, the almighty and ubiquitous Grails and the much more pragmatic Gaelyk.
Since for this purpose Gaelyk is more than enough and because I wanted to try it, I used this for prototyping. By downloading the sources from Github, the typical Gaelyk project setup can be concluded if analyzed together with the information given in the Gaelyk tutorial – it’s quite easy.

The data (about 100-1000 random numbers between 5000 to 6000) should come from a web based, private (authorization-based) available data source – even though many possibilities exist, I chose Google Docs for two reasons:

1. Easy creation of “some” random numbers
2. Authorization based on Google Authorization
3. Access class already exists (cmp. this post)

The last important feature should be the creation of a chart. Here, really a lot compelling solutions exist. Mainly, there are three different ways to realize Chart creation:

1. Java based, eg. JFreeChart
2. Javascript based, eg. JQuery plugin flot
3. Pure Web based, eg. Google Chart API

Since I want to have it simple & easy, the third option is really great for this use case. A chart can very easily be created, compare this, which is rendered dynamically:

which is:

http://chart.apis.google.com/chart?chxl=0:||1:|&cht=lc&chxt=x,y&chs=500x150&chco=0077CC&chm=B,E6F2FA,0,0,0&chd=t:5563.0,5807.0,5096.0,5898.0,5306.0,5944.0,5932.0,5510.0,5088.0,5791.0,5039.0,5145.0,5459.0,5395.0,5997.0,5075.0,5587.0,5760.0,5561.0,5719.0,5108.0,5339.0,5321.0,5686.0,5213.0,5987.0,5228.0,5670.0,5594.0,5292.0,5909.0,5616.0,5978.0,5375.0,5504.0,5558.0,5840.0,5233.0,5947.0,5028.0,5592.0,5992.0,5020.0,5991.0,5953.0,5471.0,5243.0,5784.0,5472.0,5801.0,5652.0,5700.0,5183.0,5949.0,5809.0,5426.0,5130.0,5394.0,5919.0,5741.0,5134.0,5402.0,5801.0,5567.0,5478.0,5740.0,5563.0,5677.0,5346.0,5020.0,5243.0,5137.0,5787.0,5241.0,5351.0,5834.0,5630.0,5542.0,5238.0,5242.0,5312.0,5642.0,5424.0,5988.0,5796.0,5912.0,5599.0,5567.0,5832.0,5559.0,5760.0,5061.0,5629.0,5951.0,5095.0,5585.0,5602.0,5686.0,5562.0,5700.0,5267.0,5656.0,5757.0,5023.0,5498.0,5935.0,5650.0,5807.0,5345.0,5080.0,5919.0,5308.0,5316.0,5076.0,5154.0,5399.0,5537.0,5923.0,5968.0,5973.0,5528.0,5640.0,5097.0,5369.0,5374.0,5248.0,5955.0,5243.0,5949.0,5126.0,5659.0,5010.0,5473.0,5012.0,5536.0,5274.0,5115.0,5809.0,5212.0,5400.0,5550.0,5274.0,5931.0,5762.0,5343.0,5655.0,5434.0,5981.0,5844.0,5277.0,5823.0,5905.0,5741.0,5168.0,5057.0,5956.0,5841.0,5804.0,5421.0,5933.0,5667.0,5171.0,5083.0,5322.0,5410.0,5459.0,5092.0,5678.0,5401.0,5817.0,5526.0,5663.0,5753.0,5802.0,5289.0,5917.0,5020.0,5372.0,5993.0,5023.0,5773.0,5469.0,5681.0,5442.0,5736.0,5552.0,5678.0,5493.0,5758.0,5726.0,5713.0,5103.0,5201.0,5435.0,5583.0,5318.0,5501.0,5076.0,5004.0&chds=4000,7000

Now, was this easy?

Preparation/Implementation

First, the Groovy file which dispatches the request is as follows (must go to the groovy folder):

import java.net.URLConnection;

def labels = ["A", "B", "C", "D", "E"]
int ivalue = new Random().nextInt(5);

String link;
if (memcacheService.get(labels.get(ivalue)) == null) {
	def ts = new com.commons.gse.TestSheets();
	link = ts.getData(labels.get(ivalue));
	memcacheService.put(labels.get(ivalue), link)
} else {
	link = memcacheService.get(labels.get(ivalue))
}
request.setAttribute ('counter', com.commons.gse.TestSheets.counter++)
request.setAttribute ('appendix', link)
forward '/data.gtpl'

The interesting part is the usage of the predefined object memcacheService which makes it possible to just use Google App Engine’s memcache without having to deal with creation, exception handling, etc.
One of five (“A-E”) cached number series is used. If no cache for the character is found, the number is extracted from a speadsheet called data in Google Docs (see screenshots below).
After having calculated or retrieved the numerb series from the cache, the values (which is the complete link with the numbers included) are put into the ServletRequest (in this case the predefined object request). Finally, the request is dispatched to the view, a Groovy template (data.tgpl).

<html>
<head/>
<body>
  <span>counter:<%= request.getAttribute('counter') %></span>
  <form>
    <input type="button" value="Reload" onClick="window.location='/data.groovy'">
  </form>
  <img src='<%= request.getAttribute('appendix') %>'/>
</body>
</html>

A final note: if you want to use this from Github, you will have to change the Google authorization data in TestSheets.java (which is completely left out here, but is described here).

Here is the setup of the Google Docs spreadsheets:

Running

If you have installed the SpringSource Tool Suite, you can just import the Eclipse project and choose “Run as…/Web Application”. The local server should be startet. Afterwards, you can call http://localhost:8080/data.groovy.

Note: Be sure to change the username/password combination in TestSheets.java

Google App Engine, GWT, Spring 3, JPA and Google Spreadsheet

If you are interested in Spring 3 on the GAE, there is a newer post about just this topic (and some REST!).

The previous post describes the main steps, this post just extends two classes and adds a “sync function”, which allows for syncronizing with data im a Google Spreadsheet.

The github project is here.

Caveats

• There is a known problem with cookie handling, therefore the static block is used.
• The spreadsheet document must be named tabledata.

• The sample is quite stupid, to test the sync function, the usename sync must be used.

GreetingsServiceImpl.java

package com.commons.ssheet.server;

import java.io.IOException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;
import javax.persistence.PersistenceException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.jpa.JpaCallback;
import org.springframework.orm.jpa.JpaTemplate;
import org.springframework.stereotype.Component;

import com.commons.ssheet.client.GreetingService;
import com.google.gdata.client.http.GoogleGDataRequest;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.util.ServiceException;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

@SuppressWarnings("serial")
@Component
public class GreetingServiceImpl extends RemoteServiceServlet implements GreetingService {

 	static {
		System.setProperty(GoogleGDataRequest.DISABLE_COOKIE_HANDLER_PROPERTY, "true");
 	}

	private boolean first;

	@Autowired
	private EntityManager entityManager;
	@Autowired
	private SpreadsheetService spreadsheetService;
	@Autowired
	private URL metafeedUrl;

	private String username = "YOURUSERNAME";
	private String password = "YOURPASSWORD";

	public JpaTemplate getTemplate() {
		return new JpaTemplate(entityManager);
	}

	public String greetServer(String input) {
		try {
			return getData(input);
		} catch (IOException e) {
			e.printStackTrace();
		} catch (ServiceException e) {
			e.printStackTrace();
		}
		return null;
	}

	private String getData(String input) throws IOException, ServiceException {
		String all = "";
		if (input.equals("sync")) {
			first = false;
		}
		for (Customer customer : getCustomers()) {
			if (!first) {
				removeCustomer(customer);
			}
		}
		first = true;
		persistCustomer(createCustomer(input));
		if (input.equals("sync")) {
			for (Customer customer : syncWithSpredsheet()) {
				persistCustomer(customer);
			}
		}
		for (Customer customer : getCustomers()) {
			all += "id: " + customer.getId() + " - firstname: " + customer.getFirstName() + " - name:" + customer.getLastName() + "<br>";
		}
		return all;
	}

	private void removeCustomer(Customer customer) {
		EntityTransaction trx = entityManager.getTransaction();
		trx.begin();
		entityManager.remove(customer);
		trx.commit();
	}

	private Customer createCustomer(String input) {
		Customer newCustomer = new Customer();
		newCustomer.setFirstName(input + System.currentTimeMillis());
		newCustomer.setLastName(input + System.currentTimeMillis());
		return newCustomer;
	}

	private void persistCustomer(Customer customer) {
		EntityTransaction trx = entityManager.getTransaction();
		trx.begin();
		entityManager.persist(customer);
		trx.commit();
	}

	private Collection<Customer> getCustomers() {
		return getTemplate().execute(new JpaCallback<Collection<Customer>>() {
			@Override
			public Collection<Customer> doInJpa(EntityManager arg0) throws PersistenceException {
				return (Collection<Customer>) entityManager.createQuery("SELECT cust FROM com.commons.ssheet.server.Customer cust").getResultList();
			}
		});
	}

	private List<Customer> syncWithSpredsheet() throws IOException, ServiceException {
		spreadsheetService.setUserCredentials(username, password);
		List<Customer> result = new ArrayList<Customer>();
		SpreadsheetFeed feed = (SpreadsheetFeed) spreadsheetService.getFeed(metafeedUrl, SpreadsheetFeed.class);
		List<SpreadsheetEntry> spreadsheets = feed.getEntries();
		for (int i = 0; i < spreadsheets.size(); i++) {
			SpreadsheetEntry entry = spreadsheets.get(i);
			if (entry.getTitle().getPlainText().trim().equals("tabledata")) {
				List<WorksheetEntry> worksheets = entry.getWorksheets();
				for (int j = 0; j < worksheets.size(); j++) {
					WorksheetEntry worksheet = worksheets.get(j);
					URL cellFeedUrl = worksheet.getCellFeedUrl();
					CellFeed cfeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class);
					Customer customer = new Customer();
					boolean wasAdded = true;
					for (CellEntry cell : cfeed.getEntries()) {
						String value = cell.getCell().getInputValue();
						if (cell.getTitle().getPlainText().startsWith("A")) {
							if (!wasAdded) {
								result.add(customer);
								customer = new Customer();
							}
							customer.setFirstName(value);
						} else if (cell.getTitle().getPlainText().startsWith("B")) {
							customer.setLastName(value);
							wasAdded = false;
						}
					}
					result.add(customer);
				}
			}
		}
		return result;
	}

}

dispatcher-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">

	<context:component-scan base-package="com.commons" />

	<bean id="spreadsheetService" class="com.google.gdata.client.spreadsheet.SpreadsheetService">
		<constructor-arg><value>appname</value></constructor-arg>
	</bean>

	<bean id="metafeedUrl" class="java.net.URL">
		<constructor-arg><value>http://spreadsheets.google.com/feeds/spreadsheets/private/full</value></constructor-arg>
	</bean>

	<bean id="entityManager" factory-bean="EMF" factory-method="entityManager" />

	<bean class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
		<property name="mappings">
			<value>
				/googlespreadsheet/greet=gwtController
			</value>
		</property>
	</bean>

  	<bean name="gwtController" class="com.commons.ssheet.server.GWTController">
    	<property name="remoteService" ref="greetingServiceImpl"/>
  	</bean>	

</beans>