我有一个GAS,它显示了Google电子表格中的排行榜(Google Chart)。
我喜欢这样做,以便每次电子表格更新时,图表都会刷新并显示最新分数。
每次有新分数时,电子表格都会计算新分数并更新列表。 (此部分已使用GAS正常工作)
似乎没有用的是webApp不会重新加载/重绘图表。
logChange()函数由电子表格触发器onChange()调用,当电子表格发生更改并成功记录其进程的开始和结束时(成功调用redrawUI()函数),它会redrawUI() 。
webApp代码如下所示。 每当电子表格发生变化时,任何人都可以帮助我让这个应用程序重绘图表吗?
function doGet(e) { var app = drawUI(); return app; } function logChange(e) { Logger.log("Changed at: " + new Date()); redrawUI(); Logger.log("after redrawUI recall " + new Date()); return app; } function redrawUI() { var app = drawUI(); return app; } function drawChart() { var ss = SpreadsheetApp.openById('txS....w'); //Spreadsheet Key var hs = ss.getSheetByName('All Scores'); var data = hs.getRange(1, 1, 11, 3); var barChart = Charts.newBarChart() .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([1,2])) .setDataTable(data) .setOption('axisTitlesPosition', 'in') .setOption('hAxis.minorGridlines.count', 9) .setOption('theme', 'maximized') .setOption('colors', ['#45818e']) .setLegendPosition(Charts.Position.NONE) .setYAxisTextStyle(Charts.newTextStyle().setFontName('Courier New').setFontSize(48)) .setDimensions(1000, 800) .build(); return barChart; } function drawUI() { var barChart = drawChart(); var app = UiApp.createApplication(); var mainPanel = app.createHorizontalPanel(); var hsLabel = app.createLabel("THE Geek Game 2013") .setStyleAttribute("font-size", "5em") .setStyleAttribute("font-weight", "bold") .setStyleAttribute("line-height", "2em"); var subLabel = app.createLabel("Leader Board") .setStyleAttribute("font-size", "3em") .setStyleAttribute("font-weight", "bold") var vPanel = app.createVerticalPanel().setId("vPanel"); mainPanel.add(vPanel).setSize("100%", "100%") .setCellHorizontalAlignment(vPanel, (UiApp.HorizontalAlignment.CENTER)); vPanel.add(hsLabel).add(subLabel); vPanel.add(barChart).setSize("500", "100%"); vPanel.setCellHorizontalAlignment(hsLabel, (UiApp.HorizontalAlignment.CENTER)); vPanel.setCellHorizontalAlignment(subLabel, (UiApp.HorizontalAlignment.CENTER)); app.add(mainPanel); app.createServerHandler("redrawUI"); return app; }如果您想查看当前应用的显示方式,请看以下链接: https : //script.google.com/macros/s/AKfycbykZZYCXd9SCJL0pnoNrR7L5ekdFQb8CzRsut0vLunKwq4jg8o/exec
I have a GAS which displays a leader board (Google Chart) from a Google Spreadsheet.
I like to make it so that each time there is an update to the spreadsheet, the chart would refresh and show the latest scores.
Every time there is a new score, the spreadsheet calculates the new scores and updates the list. (This part is already working correctly using GAS)
what does NOT seem to work is that the webApp does not reload/redraw the chart.
the logChange() function is called by a Spreadsheet Trigger onChange() which fires when the spreadsheet is changed and successfully logs the beginning and end of its process (successfully calls the redrawUI() function).
The webApp code is provided below. Can anyone help me make this app redraw the chart each time there is a change in the spreadsheet?
function doGet(e) { var app = drawUI(); return app; } function logChange(e) { Logger.log("Changed at: " + new Date()); redrawUI(); Logger.log("after redrawUI recall " + new Date()); return app; } function redrawUI() { var app = drawUI(); return app; } function drawChart() { var ss = SpreadsheetApp.openById('txS....w'); //Spreadsheet Key var hs = ss.getSheetByName('All Scores'); var data = hs.getRange(1, 1, 11, 3); var barChart = Charts.newBarChart() .setDataViewDefinition(Charts.newDataViewDefinition().setColumns([1,2])) .setDataTable(data) .setOption('axisTitlesPosition', 'in') .setOption('hAxis.minorGridlines.count', 9) .setOption('theme', 'maximized') .setOption('colors', ['#45818e']) .setLegendPosition(Charts.Position.NONE) .setYAxisTextStyle(Charts.newTextStyle().setFontName('Courier New').setFontSize(48)) .setDimensions(1000, 800) .build(); return barChart; } function drawUI() { var barChart = drawChart(); var app = UiApp.createApplication(); var mainPanel = app.createHorizontalPanel(); var hsLabel = app.createLabel("THE Geek Game 2013") .setStyleAttribute("font-size", "5em") .setStyleAttribute("font-weight", "bold") .setStyleAttribute("line-height", "2em"); var subLabel = app.createLabel("Leader Board") .setStyleAttribute("font-size", "3em") .setStyleAttribute("font-weight", "bold") var vPanel = app.createVerticalPanel().setId("vPanel"); mainPanel.add(vPanel).setSize("100%", "100%") .setCellHorizontalAlignment(vPanel, (UiApp.HorizontalAlignment.CENTER)); vPanel.add(hsLabel).add(subLabel); vPanel.add(barChart).setSize("500", "100%"); vPanel.setCellHorizontalAlignment(hsLabel, (UiApp.HorizontalAlignment.CENTER)); vPanel.setCellHorizontalAlignment(subLabel, (UiApp.HorizontalAlignment.CENTER)); app.add(mainPanel); app.createServerHandler("redrawUI"); return app; }If you like to see how the current app displays you see this link: https://script.google.com/macros/s/AKfycbykZZYCXd9SCJL0pnoNrR7L5ekdFQb8CzRsut0vLunKwq4jg8o/exec
最满意答案
除非在webapp本身中使用“timer *”,否则AFAIK无法在没有用户操作的情况下刷新webApp Ui。
我建议让电子表格有界脚本中的onChange触发器在userProperties中设置一个“标志值”,并使用计时器*轮询该值并在发现更改时进行更新。 您可以将刷新限制为一天中的某些小时,以避免配额限制。
*注意:有关此类计时器的示例, 请使用checkBox作为处理程序触发器查看此帖子 。
AFAIK there is no way to refresh a webApp Ui without user action except using a "timer*" in the webapp itself.
What I'd suggest is to let the onChange trigger in the spreadsheet bounded script set a 'flag value' in your userProperties and use a timer* to poll that value and update when you notice a change. You could limit the refresh to certain hours of the day to avoid quota limitations.
*note : for an example of such a timer see this post using the checkBox as a handler trigger.
更多推荐
发布评论