Tableau 10 includes new functions in the JavaScript API that give access to underlying data and summary data in a worksheet. As of this version, you can build dynamic web interfaces or custom visualizations using data from Tableau. I have previously showed how to integrate D3 and Tableau in a page together, but it used two different data sources. The new getData() feature solves that problem and opens up many more possibilities. This post covers what you need to know to get started.

Note: Tableau 10 beta versions are subject to change, meaning the information provided in this post may also change as we near release.

Functions and Options

The new functions and associated options match closely with what you see in the View Data window in Tableau Desktop. Here’s an overview:

New functions in Tableau 10

The functions do what you would expect based on their name. To get data rolled up to the level of detail in the worksheet, use getSummaryDataAsync(). To get every row and (optionally) every column, use getUnderlyingDataAsync(). Getting every row will be much slower, so I recommend finding ways to do what you need with summary data. In some cases, you might consider adding a 1px by 1px sheet on a dashboard just to serve data.

These two data gathering functions have identical options with one exception: You can only pull all columns when getting underlying data, not summary data. Here is what each option does, according to the API documentation:

  • ignoreAliases (boolean) – Do not use aliases specified in the data source in Tableau. Default is false.
  • ignoreSelection (boolean) – Only return data for the currently selected marks. Default is false.
  • includeAllColumns (boolean) – Return all the columns for the data source. Default is false. Note: this only works with getUnderlyingDataAsync().
  • maxRows (integer) – The number of rows of data that you want to return. Enter 0 to return all rows.

Data Structures from Tableau

Once you have the data from the viz, you will need to call other functions to get columns, rows or other information. Each one does something different, and it’s important to understand how the data is structured.

getData()

Let’s start with some simple code to see what happens when we use getData() from a sheet in the workbook: 

options = {
  maxRows: 0,
  ignoreAliases: false,
  ignoreSelection: true,
  includeAllColumns: false
};
sheet = viz.getWorkbook().getActiveSheet();
sheet.getUnderlyingDataAsync(options).then(function (t) {
  var data = t.getData();                                
});

What comes back in the data variable looks like this (one row only):

[
  [ {"value": "Jose Pierce","formattedValue": "Jose Pierce"},
    {"value": "Amelia Keller","formattedValue": "Amelia Keller"},
    {"value": "Evan Bryan","formattedValue": "Evan Bryan"},
    {"value": "Jose Pierce","formattedValue": "Jose Pierce"},
    {"value": "true","formattedValue": "True"},
    {"value": "822540","formattedValue": "822,540"}
  ]
]

While this gives us both raw values and formatted ones, it doesn’t give the field names to tell us what the values mean. For that, we need getColumns().

getColumns()

Starting from the code above, let’s add a variable to capture field names, like this:

sheet.getUnderlyingDataAsync(options).then(function (t) {
  var data = t.getData();   
  var columns = t.getColumns();  
});

This gives us an object like this stored in the columns variable:

[  {"$impl":
      {"$fieldName": "Level1", "$dataType": "string", 
        "$isReferenced": true, "$index": 0}
   },
   {"$impl":
      {"$fieldName": "Level2", "$dataType": "string",
        "$isReferenced": true, "$index": 1}
   },
   {"$impl":
      {"$fieldName": "Level3", "$dataType": "string", 
        "$isReferenced": true, "$index": 2}
   },
   {"$impl":
      {"$fieldName": "Name", "$dataType": "string", 
        "$isReferenced": true, "$index": 3}
   },
   {"$impl":
      {"$fieldName": "Param filter",  "$dataType": "boolean", 
        "$isReferenced": true, "$index": 4}
   },
   {"$impl":
      {"$fieldName": "Sales", "$dataType": "integer",
        "$isReferenced": true, "$index": 5}
   }
]

Now that we have information about field names, types and index values, we can use that to get the right objects out of the getData() function.

Metadata Functions

Two other functions you may find useful are getTotalRowCount() and getName(). You could use the row count to first get an idea of data volume, then use a test in the code to avoid unwanted situations with too much data. The getName() function simply lets you know whether you’re looking at underlying or summary data.

Making a Friendlier Data Structure

The data structures above are different than what I usually see from APIs. Instead of getting field names and values in different arrays, you would get one array with items that look like {"fieldname":"value"}. So, the Level 1 field with a value of Jose Pierce would be {"Level 1":"Jose Pierce"}. A colleague from InterWorks EU, Dave Hart, provided this bit of code to pull the columns and rows together into a friendlier format:

//the data returned from the tableau API
var columns = table.getColumns();
var data = table.getData();

//convert to field:values convention
function reduceToObjects(cols,data) {
  var fieldNameMap = $.map(cols, function(col) { return col.$impl.$fieldName; });
  var dataToReturn = $.map(data, function(d) {
    return d.reduce(function(memo, value, idx) {
      memo[fieldNameMap[idx]] = value.formattedValue; return memo;
    }, {});
  });
  return dataToReturn;
}
var niceData = reduceToObjects(columns, data);

Here is one item in the array stored in the niceData variable from the code above:

[
  {"Level1":"Johanna Beck",
   "Level2":"Rachel Zimmerman",
   "Level3":"Null",
   "Name":"Johanna Beck",
   "Param filter":"True",
   "Sales":"819,040"}
]

You may have noticed that Tableau provides null values differently than you would expect when working with JSON data. Here, since there is no one listed in "Level3", it’s either shown as "%null%" (value) or "Null" (formattedValue). These would be blank in most JSON data structures. Here, they will be treated as strings, so deal with that appropriately in your application.

A Full Example

There are plenty of ways to use this data now that you’ve formatted it to work easily with other libraries. It could go into a custom visualization, power a search box or populate a navigation menu.

Below is an example using an organizational hierarchy with three levels. The visualization only shows details for the selected level of the hierarchy to keep it more readable. When someone selects a name on the left, three things happen:

  1. The list expands to show subordinates
  2. The name of the person is passed to a Tableau Parameter
  3. The hierarchy level is passed to a Tableau Parameter

Tableau then uses those parameters in calculated fields to change the name list and filter the view.

See the Pen Tableau JS API getData() Demo by Robert Rouse (@robertrouse) on CodePen.

To see how it all comes together, look around the underlying code by clicking “HTML,” “CSS” or “JS” in the above embed or see it on CodePen for an editable version.

Helpful Links

Here are some important resources you’ll need as you begin working with these new functions:

More from the Author