Html Google 表单：将数据发送到电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21102684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Google Forms: Send data to spreadsheet
提问by user3096434
How can I send the data from a webform to a google spreadsheet? I made a form with Google Drive, but to get custom CSS running, I need to copy the form tag.
如何将数据从网络表单发送到谷歌电子表格？我使用 Google Drive 制作了一个表单，但要运行自定义 CSS，我需要复制表单标签。
In my case, that is what google generated for the send button behavior
就我而言，这就是谷歌为发送按钮行为生成的
<form action="https://docs.google.com/forms/d/113H_71nd98TWE0bByjHYNpnC-
oVA6OBDWtppU30rBrU/formResponse" method="POST" id="ss-form" target="_self" onsubmit="">
However, I want to post data from my own designed form to the above Google Form Response spreadsheet. Here is my form code (using Bootstrap 3):
但是，我想将我自己设计的表单中的数据发布到上述 Google 表单响应电子表格中。这是我的表单代码（使用 Bootstrap 3）：
<form class="form-horizontal" role="form" id="ftk-contact">
<h4>Get in touch with us now</h4>
<div class="form-group">
<label for="inputType" class="col-lg-2 control-label">Type of Inquiry</label>
<div class="col-lg-10">
<select class="form-control" id="inputType">
<option>Request a Quotation</option>
<option>Request a Bluebook</option>
<option>General Inquiry</option>
</select>
</div>
</div>
<div class="form-group">
<label for="inputName" class="col-lg-2 control-label">Name *</label>
<div class="col-lg-10">
<input type="text" class="form-control" id="inputName" placeholder="Your Name">
</div>
</div>
<div class="form-group">
<label for="inputEmail" class="col-lg-2 control-label">Email *</label>
<div class="col-lg-10">
<input type="email" class="form-control" id="inputEmail" placeholder="Your Email">
</div>
</div>
<div class="form-group">
<label for="inputCompany" class="col-lg-2 control-label">Company</label>
<div class="col-lg-10">
<input type="text" class="form-control" id="inputCompany" placeholder="Your Company Name">
</div>
</div>
<div class="form-group">
<label for="message" class="col-lg-2 control-label">Message *</label>
<div class="col-lg-10">
<textarea class="form-control" rows="5" placeholder="Your Message"></textarea>
</div>
</div>
<div class="form-group">
<label for="inputPhone" class="col-lg-2 control-label">Phone</label>
<div class="col-lg-10">
<input type="tel" class="form-control" id="inputPhone" placeholder="Your Phone Number">
</div>
</div>
<div class="form-group">
<label for="inputWeb" class="col-lg-2 control-label">URL</label>
<div class="col-lg-10">
<input type="url" class="form-control" id="inputWeb" placeholder="Your Website URL">
</div>
</div>
<div class="form-group">
<div class="col-lg-offset-2 col-lg-10">
<button type="submit" class="btn btn-primary btn-lg">Send your Inquiry now</button>
</div>
</div>
</form>
When using the above Google form action=... I am taken to the original Google Form when pressing send, instead of the form entries being copied to the spreadsheet.
当使用上面的 Google 表单 action=... 按发送时，我被带到原始的 Google 表单，而不是将表单条目复制到电子表格。
If the above approach wont work, how else can I send the form data to email or Google Drive?
如果上述方法不起作用，我还能如何将表单数据发送到电子邮件或 Google Drive？
回答by Sujay DSa
Here's what worked for me:
以下是对我有用的内容：
- Create your custom form
- Create your Google form and view the source after clicking view live form
- Copy the html code starting from <form> till </form>
- Each of the input fields in the Google form have name and id attributes which need to be copied to your personal form
- Use the URL in the form action of Google forms in your form.
- Make sure that even the submit button has the ID and name attributes matching with the Google form source.
- If you make a submit now, it will take you to the Google form response page. You can avoid this by making an ajax form submit.
- 创建您的自定义表单
- 单击查看实时表单后，创建您的 Google 表单并查看源代码
- 复制从 <form> 开始到 </form> 的 html 代码
- Google 表单中的每个输入字段都有 name 和 id 属性，需要将它们复制到您的个人表单中
- 在您的表单中使用 Google 表单的表单操作中的 URL。
- 确保即使提交按钮也具有与 Google 表单源匹配的 ID 和名称属性。
- 如果您现在提交，它会将您带到 Google 表单响应页面。您可以通过提交 ajax 表单来避免这种情况。
If your custom form does not validate a Google form mandatory element, then you will again be redirected to the Google form page.
如果您的自定义表单未验证 Google 表单必需元素，那么您将再次被重定向到 Google 表单页面。
回答by nelsonic
CompleteStep-by-stepInstructions
完成分步说明
After reading Martin Hawskey's goodintroduction (to sending data from an HTML form to a Google Spreadsheet) and seeing a few gaps/assumptions, we decided to write a detailed/comprehensive tutorial with step-by-step instructionswhich a fewpeople have found useful:
在阅读了Martin Hawskey的精彩介绍（将数据从 HTML 表单发送到 Google 电子表格）并看到一些差距/假设后，我们决定编写一个详细/全面的教程，其中包含一些人拥有的分步说明发现有用：
https://github.com/dwyl/html-form-send-email-via-google-script-without-server
https://github.com/dwyl/ html-form- send-email-via- google-script-without-server
The script saves any data sent via HTTP POSTin the Google Spreadsheet, and optionallyforwards the content to an email address. (useful if you want to be notified of new data)
该脚本将通过HTTP POSTGoogle 电子表格发送的任何数据保存，并可选择将内容转发到电子邮件地址。（如果您想收到新数据的通知，则很有用）
HTML Form:
HTML 表单：
Result (row in sheet):
结果（工作表中的行）：
We commented the Google Script so hopefully it's clear, but if you have anyquestions, don't hesitate to ask! :-)
我们对 Google Script 进行了评论，因此希望它很清楚，但如果您有任何问题，请随时提出！:-)
回答by BigKangu
It seems that recently Google has updated its way to create forms, so now the names of the fields are in hidden inputs below the normal ones (https://github.com/heaversm/google-custom-form).
似乎最近谷歌更新了创建表单的方式，所以现在字段的名称隐藏在普通输入之下（https://github.com/heaversm/google-custom-form）。
I have also tried the @nelsonic approach, and it emails the answers in JSON format properly, but it doesn't load them into the Google Spreadsheet, at least for me. That's why I wanted to combine two methods to always save the users data in case of more obfuscation changes are taken in the future by Google.
我也尝试过 @nelsonic 方法，它以 JSON 格式正确地通过电子邮件发送答案，但它没有将它们加载到 Google 电子表格中，至少对我来说是这样。这就是为什么我想结合两种方法来始终保存用户数据，以防 Google 将来进行更多混淆更改。
So I recommend you to have a webpage with a iframeinside of it. This iframe would contain your own custom form, from another webpage or –as in my example for convenience reasons– from itself using the srcdocattribute.
所以我建议你有一个网页，iframe里面有它。这个 iframe 将包含您自己的自定义表单，来自另一个网页，或者 - 在我的示例中为方便起见 - 来自使用该srcdoc属性的自身。
Then, you copy the namesof those hidden inputsas well as the actionform url from your Google Form Preview Pageand paste them into the custom form.
然后，您从Google 表单预览页面复制names这些隐藏输入以及action表单 URL ，并将它们粘贴到自定义表单中。
And finally, with Ajax we can easily send one copy of the form data to the normal Google Spreadsheet, and the other to our mail with the @nelsonic script solution.
最后，使用 Ajax，我们可以轻松地将表单数据的一份副本发送到普通的 Google 电子表格，使用@nelsonic 脚本解决方案将另一份发送到我们的邮件。
This way, with the iframe and the Ajax, we are avoiding the url redirection to the 'Response registered' page when the form is submitted to Google, but we can hide the iframe from the parent view to be 100% sure.
这样，使用 iframe 和 Ajax，我们可以避免在表单提交给 Google 时将 url 重定向到“已注册的响应”页面，但我们可以从父视图中隐藏 iframe 以确保 100% 的确定性。
I post here all my code for you to test it:
我在这里发布我所有的代码供您测试：
Custom Form:
自定义表格：
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>Custom Form to Google SpreadSheets</title>
</head>
<body>
<script type="text/javascript">
function hideIframeAndShowThankYouMessage(){
document.getElementById('iframe').style.display = "none";
document.getElementById('thankyou').style.display = "block";
}
</script>
<iframe id="iframe" width="760" height="500" frameborder="0" marginheight="0" marginwidth="0" srcdoc="<html><head></head><body>
<script src='https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js'></script>
<script type='text/javascript'>
$(document).ready(function(){
$('#form').submit(function(e) {
// You could also here, for example, valid an email address input
// It shouldn't appear, but just in case the iframe loads the Google Forms 'Answer registered' page, we hide the iframe from parent js:
window.top.hideIframeAndShowThankYouMessage();
// Now we send the same form to two different locations: the first is the ordinary Google Spreadsheet, the second is the Google Apps Script which allows us to receive an email with the form info in JSON format
var url_sheet = 'https://docs.google.com/forms/d/e/1FAIpQLSdxucfxPO2TgTh4DOKTty6VCykJ6v4RX0nbKjsz1Pc5fLR9gA/formResponse';
var url_script = 'https://script.google.com/macros/s/AKfycby2xOphkRsr8Uf3mD44-H68yC0U3bUqvKV0bxXrTISTQ7QKDxw/exec';
$.ajax({
type: 'POST',
url: url_sheet,
data: $('#form').serialize(),
success: function(data){}
});
$.ajax({
type: 'POST',
url: url_script,
data: $('#form').serialize(),
success: function(data){}
});
e.preventDefault();
});
});
</script>
<!--
*** TO-DO!! ***
1. Copy your form ACTION url from your Google Form Preview Page
2. Replace value of var url_sheet with that form ACTION url in line 31
3. Copy your Spreadsheet WEB APP url from Google Script Editor
4. Replace value of url_script with that WEB APP url in line 33
3. Look into the source of your Google Form Preview Page and search for the names of the HIDDEN fields which are each one close to the normal input type (... <input type='hidden' name='entry.314619096' jsname='L9xHkb'> ...). We don't need the jsname, only the name
4. Replace the NAMES fields of every field of the custom form below
-->
<form id='form' method='POST'>
<label for='name'>Name: </label>
<input type='text' name='entry.314619096' placeholder='This is easy!' />
<br/>
<label for='message'>Message:</label>
<input type='text' name='entry.2039301116' placeholder='Tell me something! ;)'/>
<br/>
<input type='submit' value='Submit'>
</form></body></html>">Loading...</iframe>
<h1 id="thankyou" style="display: none">Thank you!</h1>
</body>
</html>
Hope it could help someone!
希望它可以帮助某人！
回答by Ted Benson
I made utility called Magic Formsto add some extra features beyond what Google's endpoints offer. Create a magic form project and it will give you an HTTP endpoint where you can POST forms to (regular HTTP or Ajax), set custom redirects on success/error, and also auto-create columns based on whatever data gets posted, which allows for some pretty flexible use cases.
我制作了一个名为Magic Forms 的实用程序，以添加一些超出 Google 端点提供的额外功能。创建一个神奇的表单项目，它将为您提供一个 HTTP 端点，您可以在其中发布表单（常规 HTTP 或 Ajax），设置成功/错误时的自定义重定向，还可以根据发布的任何数据自动创建列，这允许一些非常灵活的用例。
回答by user10901
You can copy the HTML of the Google generated form, and include it on you custom HTML page. This way you can redesign the appearance of the google form as you wish, and using jQuery or similar techniques you can add you own logic to the form (if needed).
您可以复制 Google 生成表单的 HTML，并将其包含在您的自定义 HTML 页面中。通过这种方式，您可以根据需要重新设计 google 表单的外观，并使用 jQuery 或类似技术将您自己的逻辑添加到表单中（如果需要）。
HEre you have an example: http://www.immersionmedia.com/blog/customizing-and-styling-google-forms/
这里有一个例子：http: //www.immersionmedia.com/blog/customizing-and-styling-google-forms/
回答by Bhautik Nada
It's very simple.
这很简单。
Get your form id from FORM tag "action" property, you can get form tag at source of the Google Form web page
Get your field ids Ex. "entry.1472837636"
从 FORM 标签“action”属性中获取您的表单 ID，您可以在 Google 表单网页的源代码处获取表单标签
获取您的字段 ID 例如。“条目.1472837636”
Please refer below example now.
现在请参考下面的例子。
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>CUSTOM GOOGLE FORM</title>
</head>
<body>
<script src="http://code.jquery.com/jquery-latest.min.js" type="text/javascript"></script>
<form id="input-form" action="" method="POST" target="no-target">
<table>
<tr>
<td>Rate this help note</td>
<td><input type="radio" value="1" name="rating" class="rating" checked="">1</td>
<td><input type="radio" value="2" name="rating" class="rating">2</td>
<td><input type="radio" value="3" name="rating" class="rating">3</td>
<td><input type="radio" value="4" name="rating" class="rating">4</td>
<td><input type="radio" value="5" name="rating" class="rating">5</td>
<td><input type="radio" value="6" name="rating" class="rating">6</td>
<td><input type="radio" value="7" name="rating" class="rating">7</td>
<td><input type="radio" value="8" name="rating" class="rating">8</td>
<td><input type="radio" value="9" name="rating" class="rating">9</td>
<td><input type="radio" value="10" name="rating" class="rating">10</td>
</tr>
<tr>
<td>Are you satisfied from this help module ?</td>
<td><input type="radio" value="Yes" name="sat" class="sat" checked="">Yes</td>
<td><input type="radio" value="No" name="sat" class="sat">No</td>
</tr>
<tr><td>comments</td><td><input id="comments" name="comments"><td></tr>
<tr><td><button id="form-submit" type="submit">SUBMIT</button></td></tr>
</table>
</form>
<p id="input-feedback"></p>
<iframe src="#" id="no-target" name="no-target" style="visibility:hidden"></iframe>
<script>
jQuery('#input-form').one('submit', function() {
var rating = encodeURIComponent(jQuery('input[name=rating]:checked').val());
var sat = encodeURIComponent(jQuery('input[name=sat]:checked').val());
var comments = encodeURIComponent(jQuery('#comments').val());
var q1ID = "your-field-id";
var q2ID = "your-field-id";
var q3ID = "your-field-id";
var baseURL = 'https://docs.google.com/forms/d/e/your-form-id/formResponse?';
var submitRef = '&submit=Submit';
var submitURL = (baseURL + q1ID + "=" + sat + "&" + q2ID + "=" + rating + "&" + q3ID + "=" + comments + submitRef);
console.log(submitURL);
jQuery(this)[0].action = submitURL;
jQuery('#input-feedback').text('Thank You!');
});
</script>
</body>
</html>
回答by ZiaUllahZia
I was struggling with that from last few days, nothing was working. At the end, I found a very good solution.
过去几天我一直在努力解决这个问题，没有任何效果。最后，我找到了一个非常好的解决方案。
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
function intialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
