从HTML表格发送数据到谷歌电子表格
问题描述:
我创建了一个HTML表单,我想将这些数据存储在我的谷歌电子表格中。从HTML表格发送数据到谷歌电子表格
这是我的HTML表单
<div class="signup-header wow fadeInUp">
<h3 class="form-title text-center">GET STARTED</h3>
<form class="form-header" action="MY GOOGLE-SCRIPT URL HERE">
<div class="form-group">
<input class="form-control input-lg" name="entry.1489833646" id="name" type="text" placeholder="First Name" required>
</div>
<div class="form-group">
<input class="form-control input-lg" name="entry.990883496" id="name" type="text" placeholder="Last Name" required>
</div>
<div class="form-group">
<input class="form-control input-lg" name="entry.632678420" id="email" type="email" placeholder="Email address" required>
</div>
<div class="form-group">
<input class="form-control input-lg" name="entry.805782516" id="name" type="text" placeholder="Company" required>
</div><br>
<div class="form-group last">
<input type="submit" class="btn btn-warning btn-block btn-lg" value="SUBMIT">
</div>
<p ></p>
</form>
</div>
MY GOOGLE-SCRIPT.gs 我把这个部分从马丁hawksey
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Form Responses 1";
// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service
// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000);
try {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var headRow = e.parameter.header_row || 1;
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [];
// loop through the header columns
for (i in headers){
if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
} catch(e){
// if error return this
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
} finally { //release lock
lock.releaseLock();
}
}
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
我现在的问题是,它将我所有的新条目显示为'undefined' plz帮助
答
下面是您应该转码:
var TO_ADDRESS = ""; // change this ...
function doPost(e) {
try {
Logger.log(e); // the Google Script version of console.log see: Class Logger
MailApp.sendEmail(TO_ADDRESS, "Contact Form Submitted",
JSON.stringify(e.parameters));
record_data(e);
return ContentService // return json success results
.createTextOutput(
JSON.stringify({"result":"success",
"data": JSON.stringify(e.parameters) }))
.setMimeType(ContentService.MimeType.JSON);
} catch(error) { // if error return this
Logger.log(error);
return ContentService
.createTextOutput(JSON.stringify({"result":"error", "error": e}))
.setMimeType(ContentService.MimeType.JSON);
}
}
// new property service GLOBAL
var SCRIPT_PROP = PropertiesService.getScriptProperties();
// see: https://developers.google.com/apps-script/reference/properties/
/**
* select the sheet
*/
function setup() {
var doc = SpreadsheetApp.getActiveSpreadsheet();
SCRIPT_PROP.setProperty("key", doc.getId());
}
/**
* record_data inserts the data received from the html form submission
* e is the data received from the POST
*/
function record_data(e) {
Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
try {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName('EB Responses'); // select the responses sheet
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = [ new Date() ]; // first element in the row should always be a timestamp
// loop through the header columns
for (var i = 1; i < headers.length; i++) { // start at 1 to avoid Timestamp column
if(headers[i].length > 0) {
row.push(e.parameter[headers[i]]); // add data to row
}
}
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
}
catch(error) {
Logger.log(e);
}
finally {
return;
}
}
至于脚本,用于发送形式:
function handleFormSubmit(event) { // handles form submit withtout any jquery
event.preventDefault(); // we are submitting via xhr below
var data = getFormData(); // get the values submitted in the form
var url = event.target.action; //
var xhr = new XMLHttpRequest();
xhr.open('POST', url);
// xhr.withCredentials = true;
xhr.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
xhr.onreadystatechange = function() {
console.log(xhr.status, xhr.statusText)
console.log(xhr.responseText);
document.getElementById('gform').style.display = 'none'; // hide form
document.getElementById('thankyou_message').style.display = 'block';
$('html, body').animate({
scrollTop: 0
}, 'slow');
return;
};
// url encode form data for sending as post data
var encoded = Object.keys(data).map(function (k) {
return encodeURIComponent(k) + '=' + encodeURIComponent(data[k])
}).join('&')
// xhr.send(encoded);
console.log("encoded: " + encoded);
encoded = encoded + "&On Campus Accommodation and Meals=" + checkbox;
console.log("encoded NEW: " + encoded);
xhr.send(encoded);
}
我试着和成功测试了这个和它的工作现场也。
干杯
这是什么第二脚本,我应该在哪里把它 –
第二个脚本是'js',你需要以*解析*了'html'形式和***发送* **它 – TheDarkKnight
我应该如何在我的HTML页面/窗体中调用该脚本 –