How do i use more than 1 data source for my google charts dashboard with apps scripts?

1
votes

I'm trying to build a google charts dashboard with apps scripts.
Essentially, I have 3 data sources across 3 google spreadsheets, and wish to create a single dsahboard showing 3 seperate charts (each chart sourced by its own data sheet).

My current script is beneath where I refer to only one data source: var ss = SpreadsheetApp.openById('0AgQXvGX4FXy6dGRrOEZ3djY0M3dIUTNoQkV4TS00MEE')

Ultimately, i'd like to use one source for the tableChart, another for the ColumnChart and one LineChart.

How can this be achieved?

   function doGet() {

   var ss = SpreadsheetApp.openById('0AgQXvGX4FXy6dGRrOEZ3djY0M3dIUTNoQkV4TS00MEE')
   var data = ss.getDataRange();


   var InstallDateFilter = Charts.newCategoryFilter()
  .setFilterColumnIndex(0)
  .setAllowMultiple(false)
  .build();

   var PlatformFilter = Charts.newCategoryFilter()
  .setFilterColumnIndex(1)
  .build();


   var tableChart = Charts.newTableChart()
  .setDimensions(600, 1000)
  .enablePaging(30)
  .setDataViewDefinition(Charts.newDataViewDefinition()
  .setColumns([0,1,2,3,4]))
  .build();


   var ColumnChart = Charts.newColumnChart()
  .setDimensions(600, 600)
  .setColors(["green", "red"]) 
  .setTitle('Installs')
  .setXAxisTitle('Platform')
  .setDataViewDefinition(Charts.newDataViewDefinition()
  .setColumns([1,4]))
  .setLegendPosition(Charts.Position.BOTTOM)
  .build();


   var LineChart = Charts.newLineChart()
  .setDimensions(1000, 600)
  .setTitle('ARPI')
  .setXAxisTitle('InstallDate')
  .setDataViewDefinition(Charts.newDataViewDefinition()
  .setColumns([0,2]))
  .setCurveStyle(Charts.CurveStyle.SMOOTH)
  .setPointStyle(Charts.PointStyle.MEDIUM)
  .setLegendPosition(Charts.Position.BOTTOM)
  .build();


   var dashboard = Charts.newDashboardPanel()
  .setDataTable(data)
  .bind ( [InstallDateFilter, PlatformFilter], [tableChart] )
  .bind ( [InstallDateFilter, PlatformFilter], [ColumnChart] )
  .bind ( [InstallDateFilter, PlatformFilter], [LineChart] )
  .build();


  var app = UiApp.createApplication();
  var filterPanel = app.createVerticalPanel();
  var chartPanel = app.createHorizontalPanel();
  filterPanel.add(InstallDateFilter) .add(PlatformFilter).setSpacing(10);
  chartPanel .add(tableChart) .add(ColumnChart) .add(LineChart)  .setSpacing(10);

  dashboard.add(app.createHorizontalPanel()
          .add(filterPanel).add(chartPanel));

  app.add(dashboard);
  return app; 

  }
2
google-apps-scriptgoogle-visualization
Dashboards can only use a single data source. When using the javascript implementation of the Visualization API, it is possible to work around this limitation, but not when using the Apps Script implementation. – asgallant
Thanks for the heads up, do you have a link to the documentation of using the javascript implementation of the Visualization API? Or better a basic template of code? – user2550621
Documentation here; there are a ton of examples in the docs. – asgallant
The the documentation only seems to show how to draw a basic chart and not how to use an external data source i.e. google spreadsheet. function drawChart() { var data = google.visualization.arrayToDataTable([ ['Year', 'Sales', 'Expenses'], ['2004', 1000, 400], ['2005', 1170, 460], ['2006', 660, 1120], ['2007', 1030, 540] ]); – user2550621

2 Answers

1
votes

Doesnt make sense to have a dashboard with charts that have different base data. Dashboards are for showing the same data in different ways. Make 3 dashboards if you want filters for each chart or dont use dashboards and populate each chart data, lots of examples for doing that.

0
votes

You can create different dashboards with different data sets.

Below is the code example : 

function doGet() {

  var data1 = getFirstDataSet();
  var genderFilter = Charts.newCategoryFilter().setFilterColumnLabel("Gender").build();
  var tableChart = Charts.newTableChart().setDataViewDefinition(Charts.newDataViewDefinition().setColumns([0, 1, 2])).build();
  var dashboard1 = Charts.newDashboardPanel().setDataTable(data1).bind(genderFilter, tableChart).build();

  var data2 = getSecondDataSet();
  var ageFilter = Charts.newNumberRangeFilter().setFilterColumnLabel("Age").build();
  var pieChart = Charts.newPieChart().setDataViewDefinition(Charts.newDataViewDefinition().setColumns([0, 2])).build();
  var dashboard2 = Charts.newDashboardPanel().setDataTable(data2).bind(ageFilter, pieChart).build();

  var uiApp = UiApp.createApplication();
  dashboard1.add(uiApp.createHorizontalPanel().setSpacing(70).add(genderFilter).add(tableChart));
  dashboard2.add(uiApp.createHorizontalPanel().setSpacing(70).add(ageFilter).add(pieChart));
  uiApp.add(dashboard1);
  uiApp.add(dashboard2);
  return uiApp;
}

function getFirstDataSet() {
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, "Name")
      .addColumn(Charts.ColumnType.STRING, "Gender")
      .addColumn(Charts.ColumnType.NUMBER, "Donuts eaten")
      .addRow(["Michael", "Male", 5])
      .addRow(["Elisa", "Female", 7])
      .addRow(["Robert", "Male", 3])
      .addRow(["John", "Male", 2])
      .addRow(["Jessica", "Female", 6])
      .addRow(["Aaron", "Male", 1])
      .addRow(["Margareth", "Female", 8])
      .addRow(["Miranda", "Female", 6])
      .build();
   return data;
}

function getSecondDataSet() {
  var data = Charts.newDataTable()
      .addColumn(Charts.ColumnType.STRING, "Name")
      .addColumn(Charts.ColumnType.NUMBER, "Age")
      .addColumn(Charts.ColumnType.NUMBER, "Donuts eaten")
      .addRow(["Michael", 12, 5])
      .addRow(["Elisa", 20, 7])
      .addRow(["Robert", 7, 3])
      .addRow(["John", 54, 2])
      .addRow(["Jessica", 22, 6])
      .addRow(["Aaron", 3, 1])
      .addRow(["Margareth", 42, 8])
      .addRow(["Miranda", 33, 6])
      .build();
  return data;
}