I have a set of published dashboards that are linked to google. All dashboards pull data from google spreadsheets, basically these are basic filters. This is a rather large table with a lot of vlookup and several importrange, several filter formulas, some query formulas ... a lot happens ... often you get "some formulas take time to process a message"
The toolbar works great if you have a spreadsheet. Or if someone else opens it. But if you close the spreadsheet and wait a while, and try to start the dashboards when the table is closed, often the dashboards do not retrieve any data.
I'm suspicious that I need to allow the panels to wait for the data to load a bit longer, or somehow open the spreadsheet in the "background", if possible. Any thoughts? Here is an example toolbar:
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; }
source share