FOR BI PROS Tableau
Google Spreadsheet with Tableau Web Data Connector
April 4, 2015
1
, , , , , , , ,

This is a miraculous moment; this is the first time when I write a Tableau Web Data connector. As you might know, Tableau 9 will include (some when) the Tableau Web Data Connector API. This API allows developers to integrate their web application with Tableau using Javascript codes (or anything else as we will see later).

Since there is a huge need in this community for Google Spreadsheets driver I just made one today based on the example bundled with the API. In the first section I will show how it works, while in the second part we will go into the dirty part: how was it made, what does it do and how simple it is.

Using Google Spreadsheets from Tableau

To try out everything first of all you need a working Tableau 9 beta plus a Tableau 9 Web Data Connector API. Without these goodies you will not see the web connector part in Desktop. Fortunately I have everything, so let’s go.

For first try I will try to load Guardian’s “The greatest films of all time” list, which is published here as a public Google Spreadsheet. If you open it you can see that the URL is like this:

Please note that it has a key in the URL, we will need it later.

After starting the new Tableau Desktop, first click on the “More Servers…” button, then on Web Data Connector.

api1
After clicking on “Web Data Connector” you should specify the connection provider which is a reusable regular web page containing the Connector javascript code. In this example I will use my own code, located at http://cdn.starschema.net/databoss/GoogleSpreadsheetEspresso.html . After passing this URL to Web Data Connector config dialog my configuration screen will pop up which will ask for the Spreadsheet key and the sheet name to work with. Use key from the URL above ( 0AonYZs4MzlZbdEIycWhwSnNBSTBTUDl3WWdDSE1VbHc ).
(* I know it is not over designed, but c’mon, I am a BI consultant not a web designer.)
api2
For sheet names you can use the default “default” value which will go to the default sheet, but you can add sheet names or sheet indexes (like 1, 2,..).

Clicking on the Get spreadsheet it will create an extract on the fly and go directly to the usual designer environment with all of the spreadsheet data (you do not even have to specify the extract and data source name).
api3

That’s it, simple, clean and works like a charm!

Preparing our own spreadsheet

This one is not Tableau specific, but I think it will help you with your first steps with the connector. Open your spreadsheet and click on “File” then “Publish to the web…”:
api4

 

Then publish entire document:

api5

Copy paste the URL and cut out your document key:

api6

Paste the key to the Spreadsheet Connector:

api7

It was a matter of three clicks at Spreadsheet side.

How to build the connector

First of all let me start with a confession: I truly and honestly hate javascript and I am not really good in it. It’s ugly, dirty, filthy, fully loaded with unnecessary characters – the whole programming language is a mess. That is the reason why I prefer CoffeeScript or ClojureScript when I need to write something in node.js or for the web (which is extremely rare) as these languages are nicer and resulting javascript codes. This howto part will be written CoffeeScript as ClojureScript is not so straightforward with jQuery.

From concepts perspective for me the Web Data Connector API is more like an extract generator web page. It is like a real web application: takes data from the users, process it with additional web service calls and client side calculations, then emits back to tableau as an extractable data set.

The complete Google Spreadsheet connector is only 60 lines (!!) of code, thus we can check it carefully, line by line.

Starting from requirements and interfaces: the Web Data connector requires implementing four functions:

  1. init: callback to initialize our code
  2. shutdown: same, but for cleanup
  3. getColumnHeaders: returns a map column names and their types
  4. getData: return with the data frame (with pagination possibility)

In addition we need a HTML form which will be shown in Tableau Desktop, this will provide the input parameters for our functions. Quickly starting with the form:

With jQuery we can bind our function to pick up this form’s values and pass it to Tableau:

Now we can start with the API callbacks:

This was the generic part, now we have move to the google spreadsheet specific parts. Every published spreadsheet can be accessed thru a REST API (with key and sheet name) which returns either CSV or JSON. Obviously JSON is what we want so the URL should be something like:

https://spreadsheets.google.com/feeds/list/<key>/<sheet_id>/public/values?alt=json

To make it easier I moved this logic to a function:

From this JSON we can easily get the data for each column. All column keys start with “gsx$” while all data is located in its t$ key.

What happens here, is

  • telling to Tableau that we do not need pagination (lines 3-5)
  • Build URL based on key and sheet parameters (line 7)
  • Call the REST URL for getting the JSON data of the sheet (lines 7-9)
  • Iterate on entries (data.feed.entry), build an array of rows, where each row is a map of column pointing to value (lines 12-19)
  • Pass back this array to tableau.dataCallback (line 20)

For building the headers we use similar logic, but we take only the first row from the spreadsheet and guessing the datatypes:

 

The complete code is available here: https://gist.github.com/tfoldi/1a617c1bab095469e2cd, the built web page is here: http://cdn.starschema.net/databoss/GoogleSpreadsheetEspresso.html

Summary

Even inexperienced developers can build Tableau Web Data Connectors relatively quickly. This will lead to a community boom: people will start publishing these small web snippets allowing thousands of tableau users to access open data hubs and proprietary data silos with a single point and click experience. Connecting to Eurostat, Google data providers, geotaggers, social networks will be even more convenient than now.

Tamás Földi

Tamás Földi

Director of IT Development at Starschema
Decades of experience with data processing and state of the art programming. From nuclear bomb explosion simulation to distributed file systems. ethical hacking, real time stream processing practically I always had a great fun with those geeky ones and zeros.
Tamás Földi
  • Daniel Cedeño

    Hello Tamás! Amazing work! Can you help us to develop a similar WDC to integrate Tableau with Surveygizmo?

Related items

/ You may check this items as well

Pasted image at 2018_01_09 04_59 PM

Python Experiments in Tableau 1. – Add live currency conversion to Tableau Dashboards using TabPy

This is a miraculous moment; this is the first tim...

Read more
Tableau Docker

HOWTO: Tableau Server Linux in Docker Container

This is a miraculous moment; this is the first tim...

Read more
Tableau Consistency Checker

Tableau Filestore Consistency Checker – How Repository Maps to Filestore

This is a miraculous moment; this is the first tim...

Read more