英文:
How to save my table row items to my google sheet?
问题
我遵循这个指南 https://github.com/levinunnink/html-form-to-google-sheet 来将我的HTML输入保存到Google表格中。
现在我创建了一个HTML,你可以添加更多的行,但问题是只有第一个项目能够添加到Google表格中,我如何使用指南中提供的代码将所有行项目都添加到我的Google表格中?
Appscript
// 已更新至 2021 年和 ES6 标准
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()
function initialSetup () {
const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
const lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
const sheet = doc.getSheetByName(sheetName)
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1
const newRow = headers.map(function(header) {
return header === 'Date' ? 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()
}
}
这是我的代码
$(document).ready(function(){
function calculateTotal(currentGroup) {
var groupTotal = 0;
currentGroup.parents('table').find('.rowTotal').each(function( i ){
groupTotal = Number(groupTotal) + Number( $(this).text() );
});
currentGroup.parents('table').find('.total').text(groupTotal.toFixed(2));
currentGroup.parents('table').find('.subtotal').text(groupTotal.toFixed(2));
}
$(".document.active").delegate( ".tdDelete", "click", function() {
if ($(this).parents('tbody').children().length > 1){
$(this).prev().text('0');
calculateTotal($(this));
$(this).parents('tr').remove();
}
});
$(".document.active").delegate( ".trAdd", "click", function() {
$(this).parents('table').find('tbody').append( $(this).parents('table').find('tbody tr:last-child').clone() );
calculateTotal($(this));
});
$(".document.active").delegate( ".amount", "keyup", function() {
calculateTotal($(this));
});
var tdValues = [];
$(".document.active .proposedWork").delegate( "td:not(.description .unit)", "keyup", function() {
tdValues.length = 0;
//Paint
$(this).parents('tr').find('td').each(function( i ){
if(i > 4){return false}
if(i == 4){$(this).text( tdValues[0]*tdValues[3] )}
tdValues[i] = Number( $(this).text() );
});
calculateTotal($(this));
});
});
//以下为CSS样式
...
//以下为HTML输入部分
...
如果我添加5行,它会添加到5行而不是只有一行。
HTML输入
成功
结果
英文:
I follow this guide https://github.com/levinunnink/html-form-to-google-sheet to save my html input to google sheet.
Now I created a html that you are allowed to add more rows but the problem is only the first item can be added to google sheet, How can I add all the row items to my google sheet using the code provided on the guide???
Appscript
// Updated for 2021 and ES6 standards
const sheetName = 'Sheet1'
const scriptProp = PropertiesService.getScriptProperties()
function initialSetup () {
const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
const lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
const sheet = doc.getSheetByName(sheetName)
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
const nextRow = sheet.getLastRow() + 1
const newRow = headers.map(function(header) {
return header === 'Date' ? 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()
}
}
Here's my code
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-js -->
$(document).ready(function(){
function calculateTotal(currentGroup) {
var groupTotal = 0;
currentGroup.parents('table').find('.rowTotal').each(function( i ){
groupTotal = Number(groupTotal) + Number( $(this).text() );
});
currentGroup.parents('table').find('.total').text(groupTotal.toFixed(2));
currentGroup.parents('table').find('.subtotal').text(groupTotal.toFixed(2));
}
$(".document.active").delegate( ".tdDelete", "click", function() {
if ($(this).parents('tbody').children().length > 1){
$(this).prev().text('0');
calculateTotal($(this));
$(this).parents('tr').remove();
}
});
$(".document.active").delegate( ".trAdd", "click", function() {
$(this).parents('table').find('tbody').append( $(this).parents('table').find('tbody tr:last-child').clone() );
calculateTotal($(this));
});
$(".document.active").delegate( ".amount", "keyup", function() {
//console.log('test');
calculateTotal($(this));
});
var tdValues = [];
$(".document.active .proposedWork").delegate( "td:not(.description .unit)", "keyup", function() {
tdValues.length = 0;
//Paint
$(this).parents('tr').find('td').each(function( i ){
if(i > 4){return false}
if(i == 4){$(this).text( tdValues[0]*tdValues[3] )}
tdValues[i] = Number( $(this).text() );
});
calculateTotal($(this));
});
});
<!-- language: lang-css -->
<style type="text/css">
/* Housekeeping */
body{
font-size:12px;
}
.spreadSheetGroup{
/*font:0.75em/1.5 sans-serif;
font-size:14px;
*/
color:#333;
background-color:#fff;
padding:1em;
}
/* Tables */
.spreadSheetGroup table{
width:100%;
margin-bottom:1em;
border-collapse: collapse;
}
.spreadSheetGroup .proposedWork th{
background-color:#eee;
}
.tableBorder th{
background-color:#eee;
}
.spreadSheetGroup th,
.spreadSheetGroup tbody td{
padding:0.5em;
}
.spreadSheetGroup tfoot td{
padding:0.5em;
}
.spreadSheetGroup td:focus {
border:1px solid #fff;
-webkit-box-shadow:inset 0px 0px 0px 2px #5292F7;
-moz-box-shadow:inset 0px 0px 0px 2px #5292F7;
box-shadow:inset 0px 0px 0px 2px #5292F7;
outline: none;
}
.spreadSheetGroup .spreadSheetTitle{
font-weight: bold;
}
.spreadSheetGroup tr td{
text-align:center;
}
/*
.spreadSheetGroup tr td:nth-child(2){
text-align:left;
width:100%;
}
*/
/*
.documentArea.active tr td.calculation{
background-color:#fafafa;
text-align:right;
cursor: not-allowed;
}
*/
.spreadSheetGroup .calculation::before, .spreadSheetGroup .groupTotal::before{
/*content: "$";*/
}
.spreadSheetGroup .trAdd{
background-color: #007bff !important;
color:#fff;
font-weight:800;
cursor: pointer;
}
.spreadSheetGroup .tdDelete{
background-color: #eee;
color:#888;
font-weight:800;
cursor: pointer;
}
.spreadSheetGroup .tdDelete:hover{
background-color: #df5640;
color:#fff;
border-color: #ce3118;
}
.documentControls{
text-align:right;
}
.spreadSheetTitle span{
padding-right:10px;
}
.spreadSheetTitle a{
font-weight: normal;
padding: 0 12px;
}
.spreadSheetTitle a:hover, .spreadSheetTitle a:focus, .spreadSheetTitle a:active{
text-decoration:none;
}
.spreadSheetGroup .groupTotal{
text-align:right;
}
table.style1 tr td:first-child{
font-weight:bold;
white-space:nowrap;
text-align:right;
}
table.style1 tr td:last-child{
border-bottom:1px solid #000;
}
table.proposedWork td,
table.proposedWork th,
table.exclusions td,
table.exclusions th{
border:1px solid #000;
}
table.proposedWork thead th, table.exclusions thead th{
font-weight:bold;
}
table.proposedWork td,
table.proposedWork th:first-child,
table.exclusions th, table.exclusions td{
text-align:left;
vertical-align:top;
}
table.proposedWork td.description{
width:80%;
}
table.proposedWork td.amountColumn, table.proposedWork th.amountColumn,
table.proposedWork td:last-child, table.proposedWork th:last-child{
text-align:center;
vertical-align:top;
white-space:nowrap;
}
.amount:before, .total:before{
content: "$";
}
table.proposedWork tfoot td:first-child{
border:none;
text-align:right;
}
table.proposedWork tfoot tr:last-child td{
font-size:16px;
font-weight:bold;
}
table.style1 tr td:last-child{
width:100%;
}
table.style1 td:last-child{
text-align:left;
}
td.tdDelete{
width:1%;
}
table.coResponse td{text-align:left}
table.shipToFrom td, table.shipToFrom th{text-align:left}
.docEdit{border:0 !important}
.tableBorder td, .tableBorder th{
border:1px solid #000;
}
.tableBorder th, .tableBorder td{text-align:center}
table.proposedWork td, table.proposedWork th{text-align:center}
table.proposedWork td.description{text-align:left}
<!-- language: lang-html -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div class="document active">
<div class="spreadSheetGroup">
<hr style="visibility:hidden"/>
<form
method="POST"
action="https://script.google.com/macros/s/AKfycbyV3gb0WtK83dir9R5TlpHP6zpnhpe9AJWGkMmJ3o-4b_NcroodQ5STpumi-hr-EnojwA/exec"
>
<table class="proposedWork" width="100%" style="margin-top:20px">
<thead>
<th>Email</th>
<th>Name</th>
<th class="docEdit trAdd">+</th>
</thead>
<tbody>
<tr>
<td contenteditable="true" name="Email"> <input name="Email" type="email" placeholder="Email" required></td>
<td contenteditable="true" name="Name">
<input name="Name" type="name" placeholder="Name" required>
</td>
<td class="docEdit tdDelete">X</td>
</tr>
</tbody>
</table>
<button type="submit">Send</button>
</form>
</div>
</div>
<!-- end snippet -->
If I add 5 rows it will add to 5 rows instead of one(1)
HTML INPUT
enter image description here
Success
enter image description here
Result
enter image description here
答案1
得分: 0
我相信你的目标如下:
- 你希望将输入表格中的多行数值显示在 Web 应用程序上。
不幸的是,e.parameter
的值只有一个。我认为这可能是你目前问题的原因。
在这种情况下,我看到你展示的脚本时,认为需要修改你的 Google Apps 脚本。请按以下方式修改你的 Google Apps 脚本。
从:
const newRow = headers.map(function(header) {
return header === 'Date' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
到:
const temp = headers.map(header => header === 'Date' ? new Date() : e.parameters[header]);
const newRow = temp[1].map((e, i) => [temp[0], e, temp[2][i]]);
sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues(newRow);
- 根据你在问题中提供的结果图,我确认你的电子表格的列 "A" 到 "C" 分别是 "Date"、"Email" 和 "Name"。这在修改后的脚本中得到了体现。请注意这一点。
新增内容:
关于你接下来的新问题,
我试图添加列,但看起来只有前两列能够在我点击提交时被捕获,我如何包括其余的列?
在这种情况下,请按以下方式修改:
从:
const newRow = headers.map(function(header) {
return header === 'Date' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
到:
const temp = headers.map(header => header === 'Date' ? new Date() : e.parameters[header]);
const newRow = temp[1].map((e, i) => [temp[0], e, ...temp.slice(2).map(f => f[i])]);
sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues(newRow);
- 但在这种情况下,假设你的 HTML 和电子表格已经针对
我试图添加列
进行了修改。请注意这一点。
注意:
-
这种修改假设你的电子表格的标题行适用于使用你的 HTML 表单。请注意这一点。
-
当你修改了 Web 应用程序的 Google Apps 脚本时,请将部署设置为新版本。通过这样做,修改后的脚本将在 Web 应用程序中得到体现。请注意这一点。
-
你可以在我的报告 "在新 IDE 中重新部署 Web 应用程序而不更改 Web 应用程序的 URL(作者:我)" 中查看详情。
英文:
I believe your goal is as follows.
- You want to put the multiple row values from the inputted table on the Web Apps.
Unfortunately, the value of e.parameter
has only one value. I thought that this might be the reason for your current issue.
In this case, I thought that when I saw your showing script, it is required to modify your Google Apps Script. Please modify your Google Apps Script as follows.
From:
const newRow = headers.map(function(header) {
return header === 'Date' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
To:
const temp = headers.map(header => header === 'Date' ? new Date() : e.parameters[header]);
const newRow = temp[1].map((e, i) => [temp[0], e, temp[2][i]]);
sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues(newRow);
- From your result image in your question, I confirmed that columns "A" to "C" of your Spreadsheet are "Date", "Email" and "Name", respectively. This is reflected in the modified script. Please be careful about this.
Added:
About your following new question,
> I tried to add columns it looks like only the first 2 column can capture when I click submit, how can I include the rest of the columns?
In this case, please modify as follows.
From:
const newRow = headers.map(function(header) {
return header === 'Date' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
To:
const temp = headers.map(header => header === 'Date' ? new Date() : e.parameters[header]);
const newRow = temp[1].map((e, i) => [temp[0], e, ...temp.slice(2).map(f => f[i])]);
sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues(newRow);
- But, in this case, it supposes that both your HTML and your Spreadsheet have already been modified for
I tried to add columns
. Please be careful about this.
Note:
-
This modification supposes that the header row of your Spreadsheet is valid for using your HTML form. Please be careful about this.
-
When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.
-
You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论