How to get Google Sheets sidebar input to server

问题:

I am creating a dialog to show and edit document properties in a sidebar of Google Spreadsheets.

I managed to show the property values in the sidebar and even create text input fields to interact with via the sidebar. But finally I'm not able to get the values back into the server-side script using a serverHandler.

How are interactive dialogs with a sidebar are supported in Google Sheets?

回答1:

You don't say, but it sounds like you're using UiApp for your sidebar.

A serverHandler will receive the value of the element that the handler was attached to. For instance:

var button = uiInstance.createSubmitButton("Submit");
var servHandler = uiInstance.createServerHandler("myServerHandler");
button.addClickHandler(servHandler);

When myServerHandler() gets invoked due to a button press, it will receive the value of button in the event object.

{"parameter":
  {"clientY":"13",
   "clientX":"40",
   "eventType":"click",    <<<<<<<<<<<<<<<<<<<<<
   "ctrl":"false",
   "meta":"false",
   "source":"u503258948978",
   "button":"1",           <<<<<<<<<<<<<<<<<<<<<
   "alt":"false",
   "screenY":"207",
   "screenX":"1020",
   "shift":"false",
   "y":"13",
   "x":"40"}
}

To pass other values to the serverHandler, use addCallbackElement() to attach them to the handler.

var clientInput = uiInstance.createTextBox().setName("client-input");
...
servHandler.addCallbackElement(clientInput);

Now we will also received the input of the text box clientInput, as an attribute named parameter["client-input"].

{"parameter":
  {"clientY":"13",
   "clientX":"40",
   "eventType":"click",
   "ctrl":"false",
   "client-input":"hello",    <<<<<<<<<<<<<<<
   "meta":"false",
   "source":"u503258948978",
   "button":"1",
   "alt":"false",
   "screenY":"207",
   "screenX":"1020",
   "shift":"false",
   "y":"13",
   "x":"40"}
}

Example

This simple example of a spreadsheet-contained script gets one piece of input from the user, passes it to a serverHandler, which then updates the sidebar to display a computational result.

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .createMenu('Custom Menu')
      .addItem('Show sidebar', 'showSidebar')
      .addToUi();
}

/**
 * Display a sidebar with a button, an input box, and a
 * label. The label is initially hidden.
 */
function showSidebar() {
  var uiInstance = UiApp.createApplication()
      .setTitle('My custom sidebar')
      .setWidth(300);

  // Create the widgets to be used on sidebar
  var clientInput = uiInstance.createTextBox().setName("client-input");
  var serverOutput = uiInstance.createLabel().setId("server-response").setVisible(false);
  var button = uiInstance.createSubmitButton("Submit");

  // Set up server handler, add callbackElement(s), and attach to button
  var servHandler = uiInstance.createServerHandler("myServerHandler");
  servHandler.addCallbackElement(clientInput);
  button.addClickHandler(servHandler);

  // Place our widgets on the sidebar
  uiInstance.add(button).add(clientInput).add(serverOutput);

  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
      .showSidebar(uiInstance);
}

/**
 * Server handler - get the value of "client-input",
 * convert it to upper case, then display in the
 * Label in the UI.
 */
function myServerHandler(e) {
  Logger.log(JSON.stringify(e, undefined, 2)); // Log the event object
  var app = UiApp.getActiveApplication();
  var text = e.parameter["client-input"];
  var upperText = text.toUpperCase();
  app.getElementById("server-response").setVisible(true).setText(upperText);
  return(app);
}