如何将我的表格行项目保存到我的Google表格?

huangapple go评论90阅读模式
英文:

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(&#39;table&#39;).find(&#39;.rowTotal&#39;).each(function( i ){
groupTotal = Number(groupTotal) + Number( $(this).text() );
});
currentGroup.parents(&#39;table&#39;).find(&#39;.total&#39;).text(groupTotal.toFixed(2));
currentGroup.parents(&#39;table&#39;).find(&#39;.subtotal&#39;).text(groupTotal.toFixed(2));
}
$(&quot;.document.active&quot;).delegate( &quot;.tdDelete&quot;, &quot;click&quot;, function() {
if ($(this).parents(&#39;tbody&#39;).children().length &gt; 1){
$(this).prev().text(&#39;0&#39;);
calculateTotal($(this));
$(this).parents(&#39;tr&#39;).remove();
}
});
$(&quot;.document.active&quot;).delegate( &quot;.trAdd&quot;, &quot;click&quot;, function() {
$(this).parents(&#39;table&#39;).find(&#39;tbody&#39;).append( $(this).parents(&#39;table&#39;).find(&#39;tbody tr:last-child&#39;).clone() );
calculateTotal($(this));
});
$(&quot;.document.active&quot;).delegate( &quot;.amount&quot;, &quot;keyup&quot;, function() {
//console.log(&#39;test&#39;);
calculateTotal($(this));
});
var tdValues = [];
$(&quot;.document.active .proposedWork&quot;).delegate( &quot;td:not(.description .unit)&quot;, &quot;keyup&quot;, function() {
tdValues.length = 0;
//Paint
$(this).parents(&#39;tr&#39;).find(&#39;td&#39;).each(function( i ){
if(i &gt; 4){return false}
if(i == 4){$(this).text( tdValues[0]*tdValues[3] )}
tdValues[i] = Number( $(this).text() );
});
calculateTotal($(this));
});
});

<!-- language: lang-css -->

   &lt;style type=&quot;text/css&quot;&gt;
/* 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: &quot;$&quot;;*/
}
.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: &quot;$&quot;;
}
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 -->

&lt;script src=&quot;https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js&quot;&gt;&lt;/script&gt;
&lt;div class=&quot;document active&quot;&gt;
&lt;div class=&quot;spreadSheetGroup&quot;&gt;
&lt;hr style=&quot;visibility:hidden&quot;/&gt;
&lt;form 
method=&quot;POST&quot; 
action=&quot;https://script.google.com/macros/s/AKfycbyV3gb0WtK83dir9R5TlpHP6zpnhpe9AJWGkMmJ3o-4b_NcroodQ5STpumi-hr-EnojwA/exec&quot;
&gt;
&lt;table class=&quot;proposedWork&quot; width=&quot;100%&quot; style=&quot;margin-top:20px&quot;&gt;
&lt;thead&gt;
&lt;th&gt;Email&lt;/th&gt;
&lt;th&gt;Name&lt;/th&gt;
&lt;th class=&quot;docEdit trAdd&quot;&gt;+&lt;/th&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td contenteditable=&quot;true&quot; name=&quot;Email&quot;&gt;  &lt;input name=&quot;Email&quot; type=&quot;email&quot; placeholder=&quot;Email&quot; required&gt;&lt;/td&gt;
&lt;td contenteditable=&quot;true&quot; name=&quot;Name&quot;&gt;
&lt;input name=&quot;Name&quot; type=&quot;name&quot; placeholder=&quot;Name&quot; required&gt;
&lt;/td&gt;
&lt;td class=&quot;docEdit tdDelete&quot;&gt;X&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;
&lt;button type=&quot;submit&quot;&gt;Send&lt;/button&gt;
&lt;/form&gt;
&lt;/div&gt;
&lt;/div&gt;

<!-- 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 和电子表格已经针对 我试图添加列 进行了修改。请注意这一点。

注意:

英文:

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 === &#39;Date&#39; ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

To:

const temp = headers.map(header =&gt; header === &#39;Date&#39; ? new Date() : e.parameters[header]);
const newRow = temp[1].map((e, i) =&gt; [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 === &#39;Date&#39; ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

To:

const temp = headers.map(header =&gt; header === &#39;Date&#39; ? new Date() : e.parameters[header]);
const newRow = temp[1].map((e, i) =&gt; [temp[0], e, ...temp.slice(2).map(f =&gt; 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)".

huangapple
  • 本文由 发表于 2023年6月22日 11:31:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76528447.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定