Google Apps Script Published Dashboard doesn't load data unless spreadsheet is open

Question

I have a set of published dashboards that are linked to from a google site. The dashboards are all pulling data from a google spreadsheet, they are fairly basic - filterable datatables mostly. It is a pretty large spreadsheet with a lot of vlookups and a few importrange, a few filter formulas, some query formulas...there is a lot going on...often you get the "some formulas are taking a while to process message"

The dashboards work great if you have the spreadsheet open. Or if someone else has it open. But, if you close out of the spreadsheet, and wait a while, and try and run the dashboards when the spreadsheet is closed, often the dashboards don't pull in any data.

I'm suspicious that I need to allow the dashboards to wait for the data to load a little longer, or somehow open the spreadsheet in the "background" if that is possible. Any thoughts? Here's an example dashboard I have:

    function doGet() {

  var ss = SpreadsheetApp.openById("0Aq2VphSIvT4NdE1IRTlzV0NyWF9XZ1hsX2hXZG0xdHc");
  var lastrow = ss.getSheetByName('CIC Student Aggregate').getLastRow();
  var data = ss.getSheetByName('CIC Student Aggregate').getRange(1, 1, lastrow, 13);

  var StudentFilter = Charts.newStringFilter()
        .setFilterColumnLabel("Student Name")
        .build();
  var TotalsFilter = Charts.newNumberRangeFilter()
      .setFilterColumnLabel("Total Interactions")
      .build();

  var tablechart = Charts.newTableChart()
      .setDimensions(680, 550)
      .setDataViewDefinition(Charts.newDataViewDefinition()
                         .setColumns([0,1,7,8,9,10,11,12]))
    .build();   

    var piechart = Charts.newPieChart()
      .setDimensions(330, 350)
      .setDataViewDefinition(Charts.newDataViewDefinition()
                         .setColumns([0,12]))
      .build();  

  var dashboard = Charts.newDashboardPanel()
      .setDataTable(data)
      .bind([StudentFilter,TotalsFilter], [tablechart,piechart])
      .build();


  var uiApp = UiApp.createApplication()
      .setTitle("Interactions Totals")
      .setWidth(980)
      .setHeight(600);

 dashboard.add(uiApp.createVerticalPanel()
               .add(StudentFilter).add(TotalsFilter)
               .setSpacing(1)
    .add(uiApp.createHorizontalPanel()
        .add(uiApp.createVerticalPanel())
              .add(tablechart).add(piechart) 
        ));
  uiApp.add(dashboard);
  return uiApp;
}

Does your spreadsheet rely on a lot of formulas? I've heard of some cases where formula values aren't accessible right away. Perhaps try doing a Range.getValues() early on in the script, so that the formulas are forced to calculate. — Eric Koleda
It does - it's essentially a spreadsheet that is bringing in lots of data from other sheets so that we can generate dashboards w/ multiple sources of data. I'm calling "var data = ss.getSheetByName('CIC Student Aggregate').getRange(1, 1, lastrow, 13);" as the 3rd variable before putting the dashboards together - are you thinking something different than this might speed it up? — eod
getRange() doesn't actually load the values from that range, and so isn't causing the sheet to recalculate itself. Trying calling getValues() on that range at some point. — Eric Koleda
Oh sorry - was reading too quickly. Thanks, I'll give this a try. — eod

Jérôme Laude Jérôme Laude · Accepted Answer · 2013-02-28T16:31:43

do you still have your problem ?

If it is the case, do you have some 'importrange' in your dashboard ?

If you read data with appscript on a range where the data is generated via importrange, it will return nothing if the spreadsheet is closed.

In fact, 'importrange' is triggered only when a user open a spreadsheet. Appscript doesn't trigger the 'importrange' on Spreadsheet opening.

Google Apps Script Published Dashboard doesn't load data unless spreadsheet is open

1 Answers