Google Apps脚本持久性 [英] Google Apps Script Persistence

问题描述

背景：我有一个谷歌网站，我一直在从包含学生的标记的谷歌电子表格中获取信息，但是我想让它更具动态性，以便他们可以请求所有当前标记的报告只要他们喜欢。在我编写的脚本中，学生将输入密码，单击一个按钮，然后生成它们的标记。

问题：根据我读过的内容，当他们点击按钮时，该按钮的处理程序会导致脚本重新运行。当前的电子表格不能存储，当我尝试访问电子表格时，它告诉我它是空的。我如何再次访问电子表格？我试过使用ScriptProperties，但我得到了相同的结果。顺便说一下，如果我不尝试将它作为webapp运行，它就会起作用。

这是doGet（）函数和被称为getPassword（）函数的一部分一旦UI上的按钮被按下。

 函数的doGet（）{
变种应用= UiApp.createApplication（） ; 
 app.add（app.loadComponent（MyGui））; 
 
 var panel = app.getElementById（VerticalPanel1）; 
 var text = app.createPasswordTextBox（）。setName（text）; 
 var handler = app.createServerHandler（getResults）。addCallbackElement（text）; 
 
 panel.add（text）; 
 panel.add（app.createButton（Get Record，handler））; 
 
 SpreadsheetApp.getActiveSpreadsheet（）。show（app）; 
} 
 
函数getResults（eventInfo）{
 var app = UiApp.createApplication（）; 
 var password = eventInfo.parameter.text; 
 
 var panel = app.getElementById（VerticalPanel1）; 
 var textArea = app.createRichTextArea（）; 
 panel.add（textArea）; 
 
 var pointsSheet = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
 var passwordCheckRange = pointsSheet.getRange（B70：C94）。getValues（）; 
 ... 
  

解决方案

当脚本作为webapp运行时，没有activeSpreadSheet
so
SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）;
失败。另一种方法是将隐藏字段中的SpreadSheet ID传递给回调。

在您的doGet函数中：

  var hidden = app.createHidden （'ssId'，'YOUR_SS_ID_HERE'）; 
 panel.add（隐藏）; 
 var handler = app.createServerHandler（getResults）。addCallbackElement（text）; 
 handler.addCallbackElement（隐藏）`
  

在您的回调函数中

  var ssID = eventInfo.parameter.ssId; 
 var pointsSheet = SpreadsheetApp.openById（ssID）.getSheetByName（'SHEET_NAME'）; 
  

Background: I have a google site and I have been pulling information from a google spreadsheet containing the marks of my students, however I'd like to make it more dynamic so that they can request a report of all of their current marks whenever they'd like. In the script that I've written, students will enter a password, click a button and then their marks will be generated.

Issue: From what I've read, when they click the button, the handler for the button causes the script to be re-run. The current spreadsheet cannot be stored and when I try to access the spreadsheet, it tells me that it is null. How can I get access to the spreadsheet again? I've tried using ScriptProperties, but I got the same result. By the way, it works if I do not try to run it as a webapp.

Here's the doGet() function and part of the getPassword() function that is called once the button on the UI is pressed.

function doGet() {
  var app = UiApp.createApplication();
  app.add(app.loadComponent("MyGui"));

  var panel = app.getElementById("VerticalPanel1");
  var text = app.createPasswordTextBox().setName("text");
  var handler = app.createServerHandler("getResults").addCallbackElement(text);

  panel.add(text);
  panel.add(app.createButton("Get Record", handler));

  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

function getResults(eventInfo) {
  var app = UiApp.createApplication();
  var password = eventInfo.parameter.text;

  var panel = app.getElementById("VerticalPanel1");
  var textArea = app.createRichTextArea();
  panel.add(textArea);

  var pointsSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var passwordCheckRange = pointsSheet.getRange("B70:C94").getValues();
  ...

解决方案

The problem probably is that when the script is run as a webapp theres no "activeSpreadSheet" so SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); fails. An alternate aproach will be to pass the SpreadSheet id in hidden field to the call back.

In your doGet function:

var hidden = app.createHidden('ssId', 'YOUR_SS_ID_HERE');
panel.add(hidden);
var handler = app.createServerHandler("getResults").addCallbackElement(text);
handler.addCallbackElement(hidden)`

In your callback function

var ssID = eventInfo.parameter.ssId;
var pointsSheet = SpreadsheetApp.openById(ssID).getSheetByName('SHEET_NAME');

这篇关于Google Apps脚本持久性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！