除非电子表格已打开，否则Google Apps脚本已发布仪表板不会加载数据 [英] Google Apps Script Published Dashboard doesn't load data unless spreadsheet is open

问题描述

我有一组已从google网站连结的已发布资讯主页。仪表板都从谷歌电子表格中提取数据，它们是相当基本的 - 主要是可过滤的数据表。它是一个相当大的电子表格，有很多vlookups和几个importrange，一些过滤器公式，一些查询公式...有很多事情...经常你得到的一些公式需要一段时间来处理消息

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;
}

推荐答案

？

如果是这样，您的仪表板中是否有一些importrange？

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

如果您在通过importrange生成数据的范围内读取带有appscript的数据，则在电子表格关闭时不会返回任何内容。

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.

事实上，importrange只有在用户打开电子表格时才会触发。 Appscript不会在电子表格打开时触发importrange。

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

这篇关于除非电子表格已打开，否则Google Apps脚本已发布仪表板不会加载数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！