单个Google表单用于多个表格 [英] Single Google Form for multiple Sheets

问题描述

由于持续开发版本控制以及实现用户权限解决方法的看似不可逾越的问题，我需要捕获链接到不向用户公开的工作表的表单数据。相反，我想使用自定义菜单从单独的电子表格应用程序启动表单。然而，尽管Google搜索彻底，并且名为'FormApp.openById'的方法，我无法找到一种方法来完成这项工作。

我知道我离线这里;任何人都可以请我指点一下？ $ b

正常操作过程 - 通过脚本或使用表单UI来创建表单。捕获表单的ID。例如，从编辑器中的网址开始：

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

附上电子表格以捕捉回复。 （这里我们不会再做任何事情。）

第2步：客户端脚本

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

  / ** 
 *使用Forms服务获取现有表单的句柄，然后检索其发布的URL。 
 *使用UrlFetch服务获取表单的HTML副本。 
 *使用HtmlService将表单的HTML嵌入到电子表格UI中。 
 * ...最终使用Spreadsheet.show（）显示。 
 * / 
函数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）; 
 
 
函数onOpen（）{
 var sheet = SpreadsheetApp.getActiveSpreadsheet（）; 
 var entries = [{
 name：Launch Form，
 functionName：launchForm
}]; 
 sheet.addMenu（自定义菜单，条目）; 
}; 
  

Demo

以下是您在看到您从自定义菜单中选择启动表单。然而有一点烦恼 - 当表单被提交时，用户被带到另一个浏览器窗口或标签。在电子表格中，用户界面仍然处于打开状态，需要手动关闭。 IFRAME沙盒使问题消失了！

$ b

$ b

strong>编辑：最近引入了ECMA沙箱默认值中的更改，这要求将沙箱模式明确设置为NATIVE，以便使此技术发挥作用。代码已被更新。

再次编辑：新的IFRAME沙盒模式可以保持整个表单体验。

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?

解决方案

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.

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