Google Apps Script - Object type does not match co

问题:

I'M totally new to Apps Scripts and am trying to create a simple TableChart in Apps Script from a Google Spreadsheet. I keep getting the "Object type does not match column type" error and I cannot figure out how to get past it despite attempting all solutions that I can find on the web. The code I have is:

function doGet() {
var ss = SpreadsheetApp.openById('0AkEJIKjP5Q4LdFh1ZjJOUF9BQ0J2YUNwVm50eGEyTEE');
var sss = ss.getSheetByName('Customer Updates');
var data = sss.getDataRange();

var referenceFilter = Charts.newCategoryFilter().setFilterColumnIndex(29).build();

var tableChart1 = Charts.newTableChart()
                .setDimensions(900, 100)
                        .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([29,1,28,30,48])).build();

  var tableChart2 = Charts.newTableChart()
                .setDimensions(900, 100)                                 
                    .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([31,32,33,34,36])).build()    ;

var tableChart3 = Charts.newTableChart()
                .setDimensions(900, 60) 
                  .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([37])).build();

var tableChart4 = Charts.newTableChart()
                .setDimensions(900, 60) 
                    .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([38])).build();

  var tableChart5 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([39])).build();

var tableChart6 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([40])).build();

var tableChart7 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([41])).build();

var tableChart8 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([42])).build();

var tableChart9 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([43])).build();

var tableChart10 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([44])).build();

var tableChart11 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([45])).build();

var tableChart12 = Charts.newTableChart()
                .setDimensions(900, 60) 
                .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([46])).build();

var dashboard = Charts.newDashboardPanel().setDataTable(data)  
               .bind([referenceFilter], [tableChart1,tableChart2,tableChart3,tableChart4,tableChart5,tableChart6,tableChart7,tableChart8,tableChart9,tableChart10,tableChart11,tableChart12])
               .build();


var app = UIApp.createApplication();
var filterPanel = app.createHorizontalPanel();
var chartPanel1 = app.createHorizontalPanel();
var chartPanel2 = app.createHorizontalPanel();
var chartPanel3 = app.createHorizontalPanel();
var chartPanel4 = app.createHorizontalPanel();
var chartPanel5 = app.createHorizontalPanel();
var chartPanel6 = app.createHorizontalPanel();
var chartPanel7 = app.createHorizontalPanel();
var chartPanel8 = app.createHorizontalPanel();
var chartPanel9 = app.createHorizontalPanel();
var chartPanel10 = app.createHorizontalPanel();
var chartPanel11 = app.createHorizontalPanel();
var chartPanel12 = app.createHorizontalPanel();

filterPanel.add(referenceFilter).setSpacing(7).setHeight(10);
chartPanel1.add(tableChart1);
chartPanel2.add(tableChart2);
chartPanel3.add(tableChart3);
chartPanel4.add(tableChart4);
chartPanel5.add(tableChart5);
chartPanel6.add(tableChart6);
chartPanel7.add(tableChart7);
chartPanel8.add(tableChart8);
chartPanel9.add(tableChart9);
chartPanel10.add(tableChart10);
chartPanel11.add(tableChart11);
chartPanel12.add(tableChart12);

  dashboard.add(app.createVerticalPanel().add(filterPanel).add(chartPanel1).add(chartPanel2).    add(chartPanel3).add(chartPanel4).add(chartPanel5).add(chartPanel6).add(chartPanel7).add(ch    artPanel8).add(chartPanel9).add(chartPanel10).add(chartPanel11).add(chartPanel2));
app.add(dashboard);
return app;

}

There error is at line var dashboard = Charts.newDashboardPanel().setDataTable(data)

Any ideas?

回答1:

On your spreadsheet, you must guarantee that for each column, all values on it have the same type. You can not start with text on the first rows, than have a number somewhere else on it (or vice-verse).

You could also normalize everything on your script, e.g.

var data = sss.getDataRange().getValues();
var dataTable = Charts.newDataTable();
for( var j in data[0] )
  dataTable.addColumn(Charts.ColumnType.STRING, data[0][j]);
fpr( var i = 1; i < data.length; ++i )
  dataTable.addRow(data[i].map(String));

var dashboard = Charts.newDashboardPanel().setDataTable(dataTable);

Here I simply forced everything to string, I guess you got the idea if you need a NUMBER column.