用于多个工作表的单个 Google 表单 [英] Single Google Form for multiple Sheets

问题描述

由于正在进行的开发版本控制，加上实现用户权限变通办法看似无法克服的问题，我需要捕获链接到未向用户公开的工作表的表单数据.相反，我想使用自定义菜单从单独的电子表格应用程序启动表单.然而，尽管在 Google 上进行了彻底的搜索，并且使用了名称诱人的FormApp.openById"方法，但我还是找不到实现此目的的方法.

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?

推荐答案

第一步:创建表单

正常操作过程 - 通过脚本或使用表单 UI 创建表单.捕获表单的 ID.例如，来自编辑器中的 URL:

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

在用户可访问的电子表格中，创建一个容器绑定脚本(因此它可以访问电子表格 UI).以下脚本生成一个自定义菜单，其中包含在 Ui 弹出窗口中启动表单的选项.

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

演示

以下是您从自定义菜单"中选择启动表单"时看到的内容.不过有一点小麻烦——提交表单时，用户会被带到另一个浏览器窗口或选项卡.在电子表格中，UI 保持打开状态，需要手动关闭. IFRAME 沙盒解决了这个问题！

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!

最近引入了 ECMA 沙箱默认值的更改，这要求将沙箱模式显式设置为 NATIVE 才能使该技术工作.代码已更新.

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.

再次较新的 IFRAME 沙盒模式将整个表单体验保留在对话框内.

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

这篇关于用于多个工作表的单个 Google 表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！