是否可以使用谷歌电子表格中的数据“预填"谷歌表单? [英] Is it possible to 'prefill' a google form using data from a google spreadsheet?
问题描述
我正在寻找一种使用 Google 电子表格中的特定数据预先填写"Google 表单的方法.该表格对每个人都有相同的标准"问题，但前两个问题中的数据将使用现有谷歌电子表格中的独特数据预先填充".根据他们在现有电子表格中的电子邮件地址，数据将是唯一的.
I'm looking for a way to 'pre fill' a google form with specific data from a google spreadsheet. The form will have the same 'standard' questions for everyone, but the data in the first two question will be 'prefilled' with unique data from an existing google spreadsheet. The data will be unique based on their email address in the existing spreadsheet.
源电子表格示例
Col 1 Col 2 Col 3
email name birthday
@mike Mike Jones May 9th 1975
@jim Jim Smith April 19th 1985
<小时>
表格示例一
问题 1 - 预先填充了来自谷歌电子表格的数据(Mike Jones).
Question 1 - prefilled with data (Mike Jones) from a google spreadsheet.
问题 2 - 预先填充了来自 Google 电子表格的数据(1975 年 5 月 9 日).
Question 2 - prefilled with data (May 9th 1975) from a google spreadsheet.
问题 3 - 空白(等待用户回复)
Question 3 - blank (awaiting user response)
问题 4 - 空白(等待用户回复)
Question 4 - blank (awaiting user response)
表格示例二
问题 1 - 预先填充了来自谷歌电子表格的数据(Jim Smith).
Question 1 - prefilled with data (Jim Smith) from a google spreadsheet.
问题 2 - 预先填充了来自 Google 电子表格的数据(1985 年 4 月 19 日).
Question 2 - prefilled with data (April 19th 1985) from a google spreadsheet.
问题 3 - 空白(等待用户回复)
Question 3 - blank (awaiting user response)
问题 4 - 空白(等待用户回复)
Question 4 - blank (awaiting user response)
有谁知道这是否可以做到?如果是，任何帮助或指导将不胜感激.
Does anyone know if this can be done? If yes, any help or direction will be GREATLY appreciated.
提前致谢！
托德
Thank you in advance!
Todd
推荐答案
您可以从表单编辑器中创建预填表单 URL，如 云端硬盘表单文档.您最终会得到这样的 URL，例如:
You can create a pre-filled form URL from within the Form Editor, as described in the documentation for Drive Forms. You'll end up with a URL like this, for example:
https://docs.google.com/forms/d/--form-id--/viewform?entry.726721210=Mike+Jones&entry.787184751=1975-05-09&entry.1381372492&entry.960923899
buildUrls()
在此示例中，问题 1姓名"的 ID 为 726721210，而问题 2生日"的 ID 为 787184751.第 3 题和第 4 题为空白.
buildUrls()
In this example, question 1, "Name", has an ID of 726721210, while question 2, "Birthday" is 787184751. Questions 3 and 4 are blank.
您可以通过将 UI 提供的 URL 修改为模板来生成预填充的 URL，如下所示:
You could generate the pre-filled URL by adapting the one provided through the UI to be a template, like this:
function buildUrls() {
var template = "https://docs.google.com/forms/d/--form-id--/viewform?entry.726721210=##Name##&entry.787184751=##Birthday##&entry.1381372492&entry.960923899";
var ss = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Email, Name, Birthday
var data = ss.getDataRange().getValues();
// Skip headers, then build URLs for each row in Sheet1.
for (var i = 1; i < data.length; i++ ) {
var url = template.replace('##Name##',escape(data[i][1]))
.replace('##Birthday##',data[i][2].yyyymmdd()); // see yyyymmdd below
Logger.log(url); // You could do something more useful here.
}
};
这已经足够有效了 - 您可以将预先填写的 URL 通过电子邮件发送给每个人，他们已经填写了一些问题.
This is effective enough - you could email the pre-filled URL to each person, and they'd have some questions already filled in.
我们可以以编程方式将其拼凑在一起，而不是使用蛮力创建模板.这样做的好处是我们可以重复使用代码，而无需记住更改模板.
Instead of creating our template using brute force, we can piece it together programmatically. This will have the advantage that we can re-use the code without needing to remember to change the template.
表单中的每个问题都是一个项目.对于此示例，我们假设表单只有 4 个问题，正如您所描述的那样.项目 [0] 是姓名"，[1] 是生日"，依此类推.
Each question in a form is an item. For this example, let's assume the form has only 4 questions, as you've described them. Item [0] is "Name", [1] is "Birthday", and so on.
我们可以创建一个不会提交的表单回复 - 相反，我们将部分填写表单，只获取预先填写的表单 URL.由于Forms API 了解每个item 的数据类型，因此我们可以避免操作日期等类型的字符串格式，这在一定程度上简化了我们的代码.
We can create a form response, which we won't submit - instead, we'll partially complete the form, only to get the pre-filled form URL. Since the Forms API understands the data types of each item, we can avoid manipulating the string format of dates and other types, which simplifies our code somewhat.
/**
* Use Form API to generate pre-filled form URLs
*/
function betterBuildUrls() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Sheet1");
var data = ss.getDataRange().getValues(); // Data for pre-fill
var formUrl = ss.getFormUrl(); // Use form attached to sheet
var form = FormApp.openByUrl(formUrl);
var items = form.getItems();
// Skip headers, then build URLs for each row in Sheet1.
for (var i = 1; i < data.length; i++ ) {
// Create a form response object, and prefill it
var formResponse = form.createResponse();
// Prefill Name
var formItem = items[0].asTextItem();
var response = formItem.createResponse(data[i][1]);
formResponse.withItemResponse(response);
// Prefill Birthday
formItem = items[1].asDateItem();
response = formItem.createResponse(data[i][2]);
formResponse.withItemResponse(response);
// Get prefilled form URL
var url = formResponse.toPrefilledUrl();
Logger.log(url); // You could do something more useful here.
}
};
<小时>
yymmdd 函数
预填表单 URL 中的任何日期项目都应采用以下格式:yyyy-mm-dd.此辅助函数使用新方法扩展 Date 对象以处理转换.
yymmdd Function
Any date item in the pre-filled form URL is expected to be in this format: yyyy-mm-dd. This helper function extends the Date object with a new method to handle the conversion.
从电子表格中读取日期时，您最终会得到一个 javascript Date 对象，只要数据的格式可以识别为日期.(您的示例无法识别，因此您可以使用 5/9/1975 代替 May 9th 1975.)
When reading dates from a spreadsheet, you'll end up with a javascript Date object, as long as the format of the data is recognizable as a date. (Your example is not recognizable, so instead of May 9th 1975 you could use 5/9/1975.)
// From http://blog.justin.kelly.org.au/simple-javascript-function-to-format-the-date-as-yyyy-mm-dd/
Date.prototype.yyyymmdd = function() {
var yyyy = this.getFullYear().toString();
var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based
var dd = this.getDate().toString();
return yyyy + '-' + (mm[1]?mm:"0"+mm[0]) + '-' + (dd[1]?dd:"0"+dd[0]);
};
这篇关于是否可以使用谷歌电子表格中的数据“预填"谷歌表单?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
