Google Apps Script Persistence

问题:

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();
  ...

回答1:

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');