CODEWITHSUNDEEP
google-apps-scriptgoogle-visualization
LennyZ71
LennyZ71

Reputation: 571

Charts and Object Type Error

New to the Chart Service and was hoping to elaborate more on this post Google charts object error

I'm running into the same "object type does not match column type" problem, but I've checked the rows and made sure the data was the same type. If I set the whole sheet as format plain text, the graphs execute correctly and no errors; but I loose all the filters and functions that use dates of flights or try to sum flight time, and count flight types on other sheets in the workbook.

Is there a way to have the charts string the column data before using it? (simply). I even set the data range to not include the string headers just in case that was the issue, that didn't work either.

Link to sample data sheet:Sample Data Sheet

Here is the code: 

      function doGet(){
    //Get the data from spreadsheet
    var ss = SpreadsheetApp.openById(ScriptProperties.getProperty('hoistLog'));
    var sheet = ss.getSheetByName('Flight Tracking');
    var data = sheet.getDataRange();

    //Filters
    var unitFilter = Charts.newCategoryFilter()
    .setFilterColumnIndex(22)
    .build();

    var missionTypeFilter = Charts.newCategoryFilter()
    .setFilterColumnIndex(2)
    .build();

    //Charts
    var missionTypePieChart = Charts.newPieChart()
    .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([2]))
    .setDimensions(550, 350)
    .setTitle('Mission Types')
    .set3D()
    .build();

    var unitTypePieChart = Charts.newPieChart()
    .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([22]))
    .setDimensions(550, 350)
    .setTitle('Flights By Unit')
    .set3D()
    .build();

    var tableChart = Charts.newTableChart()
    .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([0, 2, 22, 23, 24, 25,     33, 34, 35, 36, 37]))
    .setDimensions(1100, 500)
    .build();

    //Dasboard
    var dashboard = Charts.newDashboardPanel().setDataTable(data)
    .bind([unitFilter, missionTypeFilter], [missionTypePieChart, unitTypePieChart,   tableChart])
    .build();

    var app = UiApp.createApplication();
    var filterPanel = app.createHorizontalPanel();
    var chartPanel = app.createHorizontalPanel();
    var tablePanel = app.createVerticalPanel();
    filterPanel.add(unitFilter).add(missionTypeFilter).setSpacing(10);
    chartPanel.add(missionTypePieChart).add(unitTypePieChart).setSpacing(10);
    tablePanel.add(tableChart).setSpacing(10);

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

Thanks Ahead of time...

Upvotes: 0

Views: 1440

Answers (3)

M. Hoffman
M. Hoffman

Reputation: 66

If date columns contain some blanks in your sheet, open the sheet and format those columns as "Number > Plain Text" to prevent the error "object type does not match column type".

Upvotes: 0

bjbsquared
bjbsquared

Reputation: 1

I found that even on a newly created spreadsheet with data added :

var data = sheet.getDataRange();

was not working.

I instead named the range and specified it by name to limit the data range to the data entered:

var data = ss.getRangeByName("theData");

It seems to be finding something outside the intended data range that I thought .getDataRange would limit to.

Upvotes: 0

pbhd
pbhd

Reputation: 4467

The problem is at the line var dashboard = Charts.newDashboardPanel().setDataTable(data), data is a range here. You need to convert it somehow like that:

//Dasboard
var vals=data.getValues();
var dataTable=Charts.newDataTable();
for (var i in vals[0]) {
  dataTable.addColumn (Charts.ColumnType.STRING, ""+i);
}
for (var i in vals) {
  var x=vals[i];
  for (var j in x) {
    x[j]=""+x[j];
  }  
  dataTable.addRow (vals[i]);
}  
var dashboard = Charts.newDashboardPanel().setDataTable(dataTable)
.bind([unitFilter, missionTypeFilter], [missionTypePieChart, unitTypePieChart,   tableChart])
.build();

Then it will display something. However, the conversion can be adopted to fit your table more (here everything is just converted to a string, and the headers are just 0, 1, 2...)

Upvotes: 1

Related Questions