我正在为用户的在线请求创建一个html表单。表单的顶部具有用于输入用户联系详细信息(例如,用户名,部门,电子邮件等)的文本输入字段,其后是一个表格,其中包含用于请求的不同项目的多行。
我想将表单数据附加到Google表格中,其中每个表格行(请求的项目)都作为表格中的单独行附加,并将用户联系方式添加到每行中,即联系方式必须针对同一用户重复。链接到工作表
尝试过Google表单-适用于单项请求-一种适用于一项的Google表单。但是对于同一用户的多个项目请求,用户必须重复输入联系方式。分支到Forms中的重复部分无效,因为其他项被添加为新列而不是新行。因此,我尝试了一个基本的html表单。
代码gs:
function addRows(valuesAll) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
sheet.getRange(sheet.getLastRow()+1,1,valuesAll.length,valuesAll[0].length).setValues(valuesAll);
}
HTML:
<!DOCTYPE html>
<html>
<head>
<?!= include('CSS_Table'); ?>
</head>
<body>
Name: <input type="text" id="Name"><br>
Phone: <input type="text" id="Phone"><br>
Email: <input type="email" id="Email"><br>
Department: <input type="text" id="Dept"><br>
<table id="tableRows">
<thead>
<tr>
<th>Item</th>
<th>Description</th>
<th>Quantity</th>
<th>Location</th>
</tr>
</thead>
<tbody>
<tr>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
</tr>
<tr>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
</tr>
<tr>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
</tr>
<tr>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
</tr>
<tr>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
<td><input class="inputCell" type="text"></td>
</tr>
<tbody>
</table>
<div class="buttonBar">
<input class="inputButton" type="button" value="Submit" onclick="buttonClick(this)">
<input class="inputButton" type="button" value="Cancel" onclick="buttonClick(this)">
</div>
<?!= include('JS_Table'); ?>
</body>
JS_Table.html:
<script>
function buttonClick(button) {
if( button.value === "Submit" ) {
var values = [];
var table = document.getElementById("tableRows");
for( var i=1; i<table.rows.length; i++ ) {
values.push([]);
var row = table.rows[i];
for( var j=0; j<row.cells.length; j++ ) {
var cell = row.cells[j].firstChild.value;
values[i-1].push(cell)
var user = ["Name","Phone","Email","Dept"];
var valuesAll = values.concat(user)
}
}
google.script.run.addRows(valuesAll);
google.script.host.close();
}
else {
if( confirm("Exit without saving?") ) google.script.host.close();
}
}
</script>
试图通过表数组简单地连接用户详细信息的ID。但谷歌脚本无法正常工作。感谢这个新手的任何建议。
执行笔录:
[19-05-25 19:42:15:580 PDT] Starting execution
[19-05-25 19:42:15:587 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds
[19-05-25 19:42:15:672 PDT] Spreadsheet.getSheetByName([Sheet1]) [0.084 seconds]
[19-05-25 19:42:15:772 PDT] Sheet.getLastRow() [0.099 seconds]
[19-05-25 19:42:15:772 PDT] Sheet.getRange([2, 1, 9, 4]) [0 seconds]
[19-05-25 19:42:15:779 PDT] Execution failed: Cannot convert Array to Object[][]. (line 17, file "Code") [0.189 seconds total runtime]
OP代码有两个问题。
1-输出未针对Google的二维范围进行调整。2-不收集姓名,电话,电子邮件和部门。
编写以下答案可以在边栏中使用(不是必需的),还可以测试表单数据是否完整-即:每行中是否有关于项目,说明,数量和位置的完整数据。OP可以轻松删除此测试。
56302393_04_gs.gs
function showSidebar04() {
var htmlOutput = HtmlService.createHtmlOutputFromFile("56302393_04_html");
htmlOutput.setSandboxMode(HtmlService.SandboxMode.IFRAME).setTitle("56302393_04 Form");
var ui = SpreadsheetApp.getUi();
ui.showSidebar(htmlOutput);
}
function addRows04(valuesAll) {
//Logger.log("DEBUG: valuesall = "+valuesAll);
var testdata = [];
var testdata = valuesAll.slice(0);
// Logger.log("DEBUG: testdata = "+testdata);
// remove the name fields
testdata.splice(0, 4);
// get the number of fields
var testdatalength = (testdata.length);
// Logger.log("DEBUG: testdata length = "+testdatalength);
// get the number of empty fields - were looking for rows that are inclonplete
var newempties = testdatalength - testdata.filter(String).length;
var adjustedlength = testdatalength-newempties;
//Logger.log("DEBUG: newempties = "+newempties+", so adjusted length = "+adjustedlength);
// calculate the number of rows
var netrows = adjustedlength/4;
// Logger.log("DEBUG: adjusted length divided by 4 = "+netrows);
// is the result an integers - get the mod
var netrowMod = adjustedlength % 4;
// Logger.log("DEBUG: residual = "+netrowMod);
// test whether newrows is an integer by testing whether mode = 0
if(netrowMod !== 0){
// Logger.log("DEBUG: netrows: "+netrows+" is not an integer");
Browser.msgBox("The data rows were not completed evenly. Code aborted");
return;
}
// setup the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "Sheet1";
var sheet = ss.getSheetByName("Sheet1");
var lastrow = sheet.getLastRow();
// get the name data
var name = valuesAll[0];
var phone = valuesAll[1];
var email = valuesAll[2];
var dept = valuesAll[3];
// set the range
var outputRange = sheet.getRange(lastrow+1,1,netrows,8);
// Logger.log("DEBUG Output range = "+range.getA1Notation());
var datastart = 4;
var alldata = [];
for (var i = 0; i<netrows;i++){
var datarow = [];
// get the row information
var item = valuesAll[(i*datastart)+4];
var desc = valuesAll[(i*datastart)+5]
var qty = valuesAll[(i*datastart)+6];
var locn = valuesAll[(i*datastart)+7];
// Logger.log("DEBUG: i="+i+", values = "+item+" "+desc+" "+qty+" "+locn);
// build the row fields
datarow.push(item);
datarow.push(desc);
datarow.push(qty);
datarow.push(locn);
datarow.push(name);
datarow.push(phone);
datarow.push(email);
datarow.push(dept);
// accumulate the rows
alldata.push(datarow);
//Logger.log("DEBUG: datarow = "+datarow)
//Logger.log("DEBUG: alldata = "+alldata)
}
// update the values of the outputrange
outputRange.setValues(alldata);
}
56302393_04_html.html
<!DOCTYPE html>
<html>
<head>
</head>
<body>
<table id="tableName">
<tbody>
<tr><td>Name:</td><td> <input type="text" name="name" value="Fred"></td></tr>
<tr><td>Phone: </td><td><input type="text" name="phone" value="0412987654"></td></tr>
<tr><td>Email: </td><td><input type="email" name="email" value="[email protected]"></td></tr>
<tr><td>Department: </td><td><input type="text" name="dept" value="Sydney"></td></tr>
</tbody>
</table>
<style>
.inputCell { float: right; width: 40%; }
.inputCell { float: left; width: 80%; }
.inputButton { float: left; width: 50%; }
</style>
<table id="tableRows">
<thead>
<tr>
<th>Item</th>
<th>Description</th>
<th>Quantity</th>
<th>Location</th>
</tr>
</thead>
<tbody>
<tr>
<td><input class="inputCell" type="text" value="1"></td>
<td><input class="inputCell" type="text" value="amd"></td>
<td><input class="inputCell" type="text" value="11"></td>
<td><input class="inputCell" type="text" value="B12"></td>
</tr>
<tr>
<td><input class="inputCell" type="text" value="2"></td>
<td><input class="inputCell" type="text" value="ibm"></td>
<td><input class="inputCell" type="text" value="22"></td>
<td><input class="inputCell" type="text" value="Z23"></td>
</tr>
<tr>
<td><input class="inputCell" type="text" value="3"></td>
<td><input class="inputCell" type="text" value="digital"></td>
<td><input class="inputCell" type="text" value="33"></td>
<td><input class="inputCell" type="text" value="A49"></td>
</tr>
<tr>
<td><input class="inputCell" type="text" value="4"></td>
<td><input class="inputCell" type="text" value="apple"></td>
<td><input class="inputCell" type="text" value="44"></td>
<td><input class="inputCell" type="text" value="K12"></td>
</tr>
<tr>
<td><input class="inputCell" type="text" value="5"></td>
<td><input class="inputCell" type="text" value="rex"></td>
<td><input class="inputCell" type="text" value="55"></td>
<td><input class="inputCell" type="text" value="ascot"></td>
</tr>
<tbody>
</table>
<div class="buttonBar">
<input class="inputButton" type="button" value="Submit" onclick="buttonClick(this)">
<input class="inputButton" type="button" value="Cancel" onclick="buttonClick(this)">
</div>
<script>
function buttonClick(button) {
if( button.value === "Submit" ) {
var valuesAll = [];
// Collect name details
var tablename = document.getElementById("tableName");
var myname = tablename.rows[0].cells[1].children[0].value
var myphone = tablename.rows[1].cells[1].children[0].value
var myemail = tablename.rows[2].cells[1].children[0].value
var mydept = tablename.rows[3].cells[1].children[0].value
valuesAll.push(myname);
valuesAll.push(myphone);
valuesAll.push(myemail);
valuesAll.push(mydept);
// collect row details
var table = document.getElementById("tableRows");
for( var i=1; i<table.rows.length; i++ ) {
var row = table.rows[i];
for( var j=0; j<row.cells.length; j++ ) {
var cell = row.cells[j].firstChild.value;
valuesAll.push(cell);
}
}
google.script.run.addRows04(valuesAll);
google.script.host.close();
}
else {
if( confirm("Exit without saving?") ) google.script.host.close();
}
}
</script>
</body>
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句