Single Google Form for multiple Sheets

Question

Due to ongoing development versioning, plus seemingly insurmountable problems implementing user permissions workarounds, I need to capture form data linked to a sheet which is not exposd to the users. Instead I want to launch the form from a separate spreadsheet app using a custom menu. Yet despite thorough Google searches, and the tantalizingly named 'FormApp.openById' method, I can't find a way to accomplish this.

I know I'm off track here; could anyone please point me to the way back?

Solution 1

Step 1: Create Form

Normal operating procedure - create your form either through a script or using the Forms UI. Capture the ID of the form. For instance, from the URL when in the editor:

https://docs.google.com/forms/d/1-AWccGNgdJ7_5Isjer5K816UKNSaUPSlvlkY3dGJ1UQ/edit
                                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Attach a spreadsheet to capture responses. (We're not going to do anything more with that here.)

Step 2: Client Script

In the user-accessible spreadsheet, create a container-bound script (so it has access to the Spreadsheet UI). The following script produces a custom menu with a selection that launches a form in a Ui popup.

/**
 * Uses the Forms service to get a handle on an existing form, then retrieve its published URL.
 * Uses the UrlFetch Service to get a copy of the HTML for the form.
 * Uses the HtmlService to embed the form's HTML in a Spreadsheet UI.
 * ... which is finally shown using Spreadsheet.show().
 */
function launchForm() {
  var formID = '1-AWccGNgdJ7_5Isjer5K816UKNSaUPSlvlkY3dGJ1UQ';
  var form = FormApp.openById(formID);
  var formUrl = form.getPublishedUrl();

  var response = UrlFetchApp.fetch(formUrl);
  var formHtml = response.getContentText();

  var htmlApp = HtmlService
      .createHtmlOutput(formHtml)
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Ta Daaa!')
      .setWidth(500) 
      .setHeight(450);

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

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Launch Form",
    functionName : "launchForm"
  }];
  sheet.addMenu("Custom Menu", entries);
};

Demo

Here's what you see when you select "Launch Form" from the "Custom Menu". One little annoyance, though - when the form is submitted, the user is taken to another browser window or tab. In the spreadsheet, the UI remains open, and needs to be manually closed. That problem is gone with IFRAME sandboxing!

---

EDIT: Changes in the ECMA sandbox defaults were introduced recently, which require that the sandbox mode be explicitly set to NATIVE for this technique to work. Code has been updated.

EDIT Again: The newer IFRAME sandbox mode keeps the whole form experience inside the dialog.

Solution 2

Mogsdad got me on the right track, but in order for the submit button to work, I had to embed it directly in the code.

1) Create a form and in the forms menu, select "Embed form in a webpage"

2) Copy the entire block of code that appears. It should look similar to this:

<iframe src="https://docs.google.com/forms/d/e/<YOUR_ID_NUMBER_HERE/viewform?embedded=true" width="500" height="450" frameborder="0" marginheight="0" marginwidth="0">Loading…</iframe>

3) Add this function to your script page

function launchForm() {
  var embeddedHtml = '<iframe src="https://docs.google.com/forms/d/e/<YOUR_ID_NUMBER_HERE/viewform?embedded=true" width="500" height="450" frameborder="0" marginheight="0" marginwidth="0">Loading…</iframe>'
  var htmlApp = HtmlService.createHtmlOutput(embeddedHtml)
  SpreadsheetApp.getUi().showModalDialog(htmlApp, 'Title');
}

If you'd like the formatting and banner of your form to stay, remove the ?embedded=true off of the embedded link https://docs.google.com/forms/d/e/<YOUR_ID_NUMBER_HERE/viewform?embedded=true

Comments

[Tim]

Hi Mogsdad, I've only just got around to using this method, and it works very well - thanks again! One issue however, is that it takes quite a while to load. The form itself is not particularly complex, consisting of 17 fields, only 2 with lists having <30 items. When launched from the 'Form' menu in the sheet which hosts the form, it loads quickly in a new tab, as it always has. I'm thinking I need to edit the css to strip out some of the default tags created by Google, but wonder if there's something else I might have overlooked - any thoughts?

[Mogsdad]

With multiple calls to Services, this technique is bound to be slow - and my guess is that it's the service calls that will cost the most. (Check the Execution Transcript to see where the time goes.) It could be helped by using Cache Services to store the published URL, and the activeSpreadsheet, removing 4+ service calls, and saving a few seconds.

[MSD]

The button for submitting the Form does not work in the Pop up. Why?