Apps Script chart dashboard binding to Google Spreadsheet as data source

Question

It says in the charts dashboard tutorial that the dashboard can be bound to a Google Spreadsheet range as a data source. But this was not demonstrated in the tutorial, nor could I find relevant info in the Apps Script docs. It seems that Datatable cannot be bound to a spreadsheet range, as the methods only provide for adding rows and columns manually.

Does anyone know how to bind the dashboard to a range from Google Spreadsheet?

In my current code, I am assuming that the dashboard can be bound to a range from Google Spreadsheet like so, but the setDataTable function call is giving me a hard time.

var ss = SpreadsheetApp.openById(spreadsheetId);
var data = ss.getRangeByName("DataWithColHeadings");
var dashboard = Charts.newDashboardPanel()
  .setDataTable(data) // throws error here! I think it is expecting a DataTableBuilder
  .bind([durationFilter, lineFilter], [pieChart, tableChart])
  .build();

Answer 1

You can specify the data range using dot notation:

var datasource = ss.getSheetByName("SheetName").getRange("A1:J20");

And then use it as follows:

var dashboard = Charts.newDashboardPanel()
      .setDataTable(**datasource**)

When used this way, you can eliminate the code specifying column names and row values. Just make sure that the values in the specified range are consistent and non-empty.

Answer 2

This is the equivalent of the tutorial bound to named range in spreadsheet. Named range includes the column heads.

    function doGet() {
      var uiApp = UiApp.createApplication();
      var ssKey = "0At0FGJizRd-gdFo5bWZUUFRrUnRabENiRFdmT2o4WUE";
      var ss = SpreadsheetApp.openById(ssKey);
      var datasource = ss.getRangeByName("myRange").getValues();
      Logger.log(datasource);
      var data = Charts.newDataTable()
          .addColumn(Charts.ColumnType.STRING, datasource [0] [0])
          .addColumn(Charts.ColumnType.STRING, datasource [0] [1])
          .addColumn(Charts.ColumnType.NUMBER, datasource [0] [2])
          .addColumn(Charts.ColumnType.NUMBER, datasource [0] [3])
          for (i=1;i<=datasource.length-1;i++){
        Logger.log(datasource [i]);
        data.addRow([datasource [i] [0], datasource [i] [1], datasource [i] [2], datasource [i] [3]])
          };

      data.build();
      var ageFilter = Charts.newNumberRangeFilter()
          .setFilterColumnLabel("Age")
          .build();

      var genderFilter = Charts.newCategoryFilter()
          .setFilterColumnLabel("Gender")
          .build();

      var pieChart = Charts.newPieChart()
          .setDataViewDefinition(Charts.newDataViewDefinition()
                         .setColumns([0,3]))
          .build();

      var tableChart = Charts.newTableChart()
          .build(); 
      var dashboard = Charts.newDashboardPanel()
          .setDataTable(data)
          .bind([ageFilter, genderFilter], [pieChart, tableChart])
          .build();
      dashboard.add(uiApp.createVerticalPanel()
            .add(uiApp.createHorizontalPanel()
                 .add(ageFilter).add(genderFilter)
                 .setSpacing(70))
            .add(uiApp.createHorizontalPanel()
                 .add(pieChart).add(tableChart)
                 .setSpacing(10)));
      uiApp.add(dashboard);
      return uiApp;
    }

Answer 3

You can also use the API function of the object RANGE called .getDataTable(). The below example works on my two-column, multi-row sheet.

function doGet() {
   var uiApp = UiApp.createApplication().setTitle("My Dashboard");

   var dataRange = sheet.getDataRange();
   var dataTable = dataRange.getDataTable(true);

   var catFilter = Charts.newCategoryFilter()
       .setFilterColumnLabel("Category")
       .build();

   var chart = Charts.newPieChart()
       .build();

   var dashboard = Charts.newDashboardPanel()
       .setDataTable(dataTable)
       .bind([catFilter], [chart])
       .build();

   var panel = uiApp.createVerticalPanel()
     .add(chart)
     .add(catFilter);

   dashboard.add(panel);
   uiApp.add(dashboard);
   return uiApp;
}

Answer 4

Attached is an example where the Applescript intuitively gets a data range starting in cell A1 of the first sheet in a google spreadsheet.

This is my first stackoverflow answer, so please give feedback

I have recreated the google piechart Applescript dashboard tutorial modified for referencing a spreadsheet below:

    function doGet() {
    var uiApp = UiApp.createApplication();
    // type the SpreadsheetKey below within quotes for 
    // example "1MfXR_ELFevumQwEOjWneNL5j2M8aVLgELBOsS41LD5o"
    var ssKey = "1MfXR_ELFevumQwEOjWneNL5j2M8aVLgELBOsS41LD5o";
    var ss = SpreadsheetApp.openById(ssKey);
    var dataRange = ss.getDataRange();
    var data = dataRange.getDataTable(true);

    var ageFilter = Charts.newNumberRangeFilter()
        .setFilterColumnLabel("Age")
        .build();

    var genderFilter = Charts.newCategoryFilter()
        .setFilterColumnLabel("Gender")
        .build();

    var pieChart = Charts.newPieChart()
        .setDataViewDefinition(Charts.newDataViewDefinition()
        .setColumns([0,3]))
        .build();

    var tableChart = Charts.newTableChart()
        .build(); 

    var dashboard = Charts.newDashboardPanel()
        .setDataTable(data)
        .bind([ageFilter, genderFilter], [pieChart, tableChart])
        .build();  
    dashboard.add(uiApp.createVerticalPanel()
          .add(uiApp.createHorizontalPanel()
               .add(ageFilter).add(genderFilter)
               .setSpacing(70))
          .add(uiApp.createHorizontalPanel()
               .add(pieChart).add(tableChart)
               .setSpacing(10)));
    uiApp.add(dashboard);
    return uiApp;
    }