Google charts object error

Question

I am trying to create a graphical representation of a simple spreadsheet, but I am receiving an error that says 'no object type matches the type of column'.

function doGet() {

  var ss = SpreadsheetApp.openById("xyz");

  var data = ss.getDataRange();

  var segFilter = Charts.newStringFilter().setFilterColumnIndex(6).build();
  var execFilter = Charts.newCategoryFilter().setFilterColumnIndex(7).build();

  var tableChart = Charts.newTableChart()
    .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([1,2,3,4,5,6,7,8,9,10,11,12,13]))
    .build();

  var dashboard = Charts.newDashboardPanel().setDataTable(data)
    .bind([segFilter, execFilter], [tableChart])
    .build();

  var app = UiApp.createApplication();
  var filterPanel = app.createVerticalPanel();
  var chartPanel = app.createHorizontalPanel();
  filterPanel.add(segFilter).add(execFilter).setSpacing(10);
  chartPanel.add(tableChart).setSpacing(10);

  dashboard.add(app.createVerticalPanel().add(filterPanel).add(chartPanel));
  app.add(dashboard);
  return app;
}

How can I fix this?

Mogsdad Mogsdad · Accepted Answer · 2013-01-03T02:53:22

From the Google Visualization API Reference:

Each column is assigned a data type... All data in each column must have the same data type.

The problem here is the leading blanks in your data table. When you create a DataTable, the type for the column is surmised from the first row of data, and it's then assumed that all the following rows are the same type. In your spreadsheet, G2 is blank, and that results in the column type being string. However, the remaining values in column G are of type number.

Illustration of original spreadsheet

In this example, there would still be problems after removing the blank row 2. See that A3 is type string, yet A4 is type number - any visualization trying to use this range will complain about the type mismatch.

So make sure you don't start any column with blank values, and make sure your types are consistent within the column, and you should be fine.

Additional tips from comments:

You can change the data type for cells to be Plain Text, either using format controls in the spreadsheet, or programmatically using setNumberFormat("@STRING@"). This would be fine in columns that you do not intend to visualize as numbers - for example, your column L that contains a mixture of text and numbers. However, if you are thinking about a column that you want to chart (for example in a line chart), then conversion to Plain Text would be a bad idea - it would stop you from performing the visualization.

Google charts object error

1 Answers