我遵循Steven的教程编写HTML表单数据到一个谷歌表。具体来说，我使用这个应用程序脚本
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";
// 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 doGet(e){
return handleResponse(e);
}
function doPost(e){
return handleResponse(e);
}
function handleResponse(e) {
// shortly after my original solution Google announced the LockService[1]
// this prevents concurrent access overwritting data
// [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
// we want a public lock, one that locks for all invocations
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
/* // If you are passing JSON in the body of the request uncomment this block
var jsonString = e.postData.getDataAsString();
e.parameter = JSON.parse(jsonString);
*/
try {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
// we'll assume header is in row 1 but you can override with header_row in GET/POST data
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());
}
和下面的HTML表单
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>GS DB Playground</title>
</head>
<body>
<form id="example-form">
<label for="name">Name</label>
<input id="name" name="Name" type="text" value="" />
<input type="submit" value="Send" />
</form>
<!-- JQuery POST request. -->
<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script>
$("#example-form").submit(function(event) {
var $form = $(this);
request = $.ajax({
url: "https://script.google.com/macros/s/AKfycbxDGzL3aHSguXkoNtuck3FdoQMpUlJx9S1bs9Zj4ujpiRcXw9Qn/exec",
type: "post",
dataType: 'json',
data: $form.serialize(),
});
request.done(function (response, textStatus, jqXHR){
alert(JSON.stringify(response));
});
request.fail(function (response, textStatus, jqXHR){
alert(JSON.stringify(response));
});
event.preventDefault();
});
</script>
</body>
</html>
当我运行它时，脚本工作完美，但是当我想从"Sheet1"更改SHEET_NAME时，会发生一些奇怪的事情。";Sheet2"它存在于我的谷歌电子表格中。不会向Sheet2写入数据，但会向Sheet1写入数据。当我将Sheet1的名称更改为其他名称时，脚本不写入任何数据并抛出错误。
这可能与权限有关。当我创建一个新的部署，同时将SHEET_NAME设置为Sheet2时，数据将实际写入该工作表。
我正在寻找一个解决方案，能够通过改变SHEET_NAME变量并单击"保存"来改变脚本在飞行中写入的表。
Production (/exec):
如果你正在使用一个生产URL (/exec)，你必须更新部署，如果你想脚本的变化得到反映在web应用程序，如Tanaike解释。
需要注意的是，实际上不需要进行新的部署，您可以使用新版本编辑现有的部署:
为此，在更改脚本后，单击Manage deployments，在当前部署时，单击铅笔图标，在Version上，选择New version，以使用当前代码编辑部署。
如果你使用的是/devURL(可通过Test deployments访问)，你不需要这样做，并且在更改后保存脚本就足够了(参见测试web应用程序部署):
此URL以
/dev结尾，只能由具有脚本编辑权限的用户访问。这个应用实例总是运行最近保存的代码，只用于开发期间的测试。