Can a Google Spreadsheet Apps Script library contain a user dialog?

Question

Are there restrictions on what may be in an Apps Script library to be used by Google Spreadsheets? Specifically, can a library include an HTML dialog?

I've created a spreadsheet script that adds a menu item to present the user with a dialog box. It uses HtmlService.createHtmlOutputFromFile('mappingForm').setSandboxMode(HtmlService.SandboxMode.IFRAME)

as described in https://developers.google.com/apps-script/guides/html/communication. The HTML file includes HTML, CSS and JavaScript with jQuery. It uses google.script.run to populate the dialog with data from the spreadsheet and to submit a form to it.

This all works fine in the original spreadsheet.

I need multiple spreadsheets to use this same code, however, so I'm trying to follow the general idea of Google Spreadsheet Scripts shared across spreadsheets (not libraries) to have a master script with a spreadsheet template and multiple copies.

I followed the directions at https://developers.google.com/apps-script/guide_libraries to create a library from the original spreadsheet. When another spreadsheet uses the library, I'm able to get the dialog to appear, but all calls back to the server (either to populate the dialog or to submit a form) fail with an error caught browser-side by the google.script.run.withFailureHandler as an Error object with properties:

message: "We're sorry, a server error occurred. Please wait a bit and try again."
name: "ScriptError"

I've placed Logger calls in the apps script to see if the server-side functions are being called but none of them are being hit. The script editor's Execution Transcript shows:

[14-12-27 19:38:05:340 PST] Starting execution

[14-12-27 19:38:05:372 PST] Execution failed: We're sorry, a server error occurred. Please wait a bit and try again. [0.0 seconds total runtime]

The client is making the call, but something is failing before it reaches the spreadsheet script.

This makes me wonder whether

I need to do something differently for the code to work as a library.
Libraries can't have dialogs.
There's a server bug.

Thanks in advance for any suggestions.

Google often returns that server error, when there is nothing wrong with the server connection. I'm guessing that this might have something to do with permissions and sharing, but I'm not sure how to fix it. — Alan Wells
@Sandy Currently, both spreadsheets have the same owner, but the goal is to eventually have spreadsheets owned by others use the library. The using spreadsheet's Project Properties shows the following Scopes:2 OAuth Scopes required by the script: googleapis.com/auth/script.send_mail googleapis.com/auth/spreadsheets — Peter D
Update: Since my original post, I've found code.google.com/p/google-apps-script-issues/issues/… where others are reporting same issue. — Peter D
Thank you for the update. I should have given you that link first thing. — Alan Wells

Peter D Peter D · Accepted Answer · 2015-01-04T23:32:14

I was able to have a working library containing an HTML dialog by doing the following.

Move the script and HTML files from the original spreadsheet to a standalone script project. Take note of the library's Project key in the Info tab of File > Project properties.... It will be needed by any spreadsheet that intends to use the library.
If the standalone script project is to used by others, click its Share button to make it shareable to anyone with a link, otherwise it will silently fail for them.
If the HTML dialog needs to call back to a library function (to get or submit data), the library function must be present in the spreadsheet that uses the library, or you'll get an error message in the browser's JavaScript console.
In the spreadsheet that uses the library: Tools > Script editor... Click Resources > Libraries.... In the "Included Libraries" dialog box, enter the standalone project's key in the Find a Library text box, click Select, then choose the appropriate Version, change the Identifier, if necessary, and Save. The Identifier value creates an object of the same name for use by the spreadsheet's script to call library functions. In my case, it is SignupFormResponsesSheet.
In the same Script Editor's code editor, add wrapper functions that call library functions, including any that will be called back from the HTML dialog. My library has an onOpen() which creates two menu items to show HTML dialogs, so I added

function onOpen() {
    SignupFormResponsesSheet.onOpen();
}

function showMappingForm() {
    SignupFormResponsesSheet.showMappingForm();
}

function showSubmitForm() {
    SignupFormResponsesSheet.showSubmitForm();
}

My HTML dialog has a number of callbacks to get and submit data, so rather than writing a wrapper function for each, I added one function to cover all of them by taking advantage of the way Apps Script treats a library as an object containing functions. The first argument is a string naming the library function to call. Any additional arguments are passed to the library function.

function runSignupFormResponseFunction(funcName, varargs) {
  return SignupFormResponsesSheet[funcName].apply(this,
    Array.prototype.slice.call(arguments, 1));
}

Because of the restriction identified in step 3 above, the JavaScript in the HTML dialog uses google.script.run to call the runSignupFormResponseFunction whenever it needs to get or submit data. For example, it has two lists that are dynamically populated with server data from the library's getRangeLabels and getColumnExamples functions (and one must be populated before the other), so the code is

google.script.run
  .withFailureHandler(showError)
  .withSuccessHandler(function(ranges) {
    loadRanges(ranges);
    // once ranges are loaded, load columns
    google.script.run
      .withSuccessHandler(loadColumns)
      .withFailureHandler(showError)
      .runSignupFormResponseFunction("getColumnExamples");
  })
  .runSignupFormResponseFunction("getRangeLabels");

This worked for me today. I hope it works for others who may find this question.

Can a Google Spreadsheet Apps Script library contain a user dialog?

1 Answers