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

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

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

  1. // 已更新至 2021 年和 ES6 标准
  2. const sheetName = 'Sheet1'
  3. const scriptProp = PropertiesService.getScriptProperties()
  4. function initialSetup () {
  5. const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  6. scriptProp.setProperty('key', activeSpreadsheet.getId())
  7. }
  8. function doPost (e) {
  9. const lock = LockService.getScriptLock()
  10. lock.tryLock(10000)
  11. try {
  12. const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
  13. const sheet = doc.getSheetByName(sheetName)
  14. const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
  15. const nextRow = sheet.getLastRow() + 1
  16. const newRow = headers.map(function(header) {
  17. return header === 'Date' ? new Date() : e.parameter[header]
  18. })
  19. sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
  20. return ContentService
  21. .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
  22. .setMimeType(ContentService.MimeType.JSON)
  23. }
  24. catch (e) {
  25. return ContentService
  26. .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
  27. .setMimeType(ContentService.MimeType.JSON)
  28. }
  29. finally {
  30. lock.releaseLock()
  31. }
  32. }

这是我的代码

  1. $(document).ready(function(){
  2. function calculateTotal(currentGroup) {
  3. var groupTotal = 0;
  4. currentGroup.parents('table').find('.rowTotal').each(function( i ){
  5. groupTotal = Number(groupTotal) + Number( $(this).text() );
  6. });
  7. currentGroup.parents('table').find('.total').text(groupTotal.toFixed(2));
  8. currentGroup.parents('table').find('.subtotal').text(groupTotal.toFixed(2));
  9. }
  10. $(".document.active").delegate( ".tdDelete", "click", function() {
  11. if ($(this).parents('tbody').children().length > 1){
  12. $(this).prev().text('0');
  13. calculateTotal($(this));
  14. $(this).parents('tr').remove();
  15. }
  16. });
  17. $(".document.active").delegate( ".trAdd", "click", function() {
  18. $(this).parents('table').find('tbody').append( $(this).parents('table').find('tbody tr:last-child').clone() );
  19. calculateTotal($(this));
  20. });
  21. $(".document.active").delegate( ".amount", "keyup", function() {
  22. calculateTotal($(this));
  23. });
  24. var tdValues = [];
  25. $(".document.active .proposedWork").delegate( "td:not(.description .unit)", "keyup", function() {
  26. tdValues.length = 0;
  27. //Paint
  28. $(this).parents('tr').find('td').each(function( i ){
  29. if(i > 4){return false}
  30. if(i == 4){$(this).text( tdValues[0]*tdValues[3] )}
  31. tdValues[i] = Number( $(this).text() );
  32. });
  33. calculateTotal($(this));
  34. });
  35. });
  36. //以下为CSS样式
  37. ...
  38. //以下为HTML输入部分
  39. ...

如果我添加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

  1. // Updated for 2021 and ES6 standards
  2. const sheetName = 'Sheet1'
  3. const scriptProp = PropertiesService.getScriptProperties()
  4. function initialSetup () {
  5. const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  6. scriptProp.setProperty('key', activeSpreadsheet.getId())
  7. }
  8. function doPost (e) {
  9. const lock = LockService.getScriptLock()
  10. lock.tryLock(10000)
  11. try {
  12. const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
  13. const sheet = doc.getSheetByName(sheetName)
  14. const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
  15. const nextRow = sheet.getLastRow() + 1
  16. const newRow = headers.map(function(header) {
  17. return header === 'Date' ? new Date() : e.parameter[header]
  18. })
  19. sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
  20. return ContentService
  21. .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
  22. .setMimeType(ContentService.MimeType.JSON)
  23. }
  24. catch (e) {
  25. return ContentService
  26. .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
  27. .setMimeType(ContentService.MimeType.JSON)
  28. }
  29. finally {
  30. lock.releaseLock()
  31. }
  32. }

Here's my code

<!-- begin snippet: js hide: false console: true babel: false -->

<!-- language: lang-js -->

  1. $(document).ready(function(){
  2. function calculateTotal(currentGroup) {
  3. var groupTotal = 0;
  4. currentGroup.parents(&#39;table&#39;).find(&#39;.rowTotal&#39;).each(function( i ){
  5. groupTotal = Number(groupTotal) + Number( $(this).text() );
  6. });
  7. currentGroup.parents(&#39;table&#39;).find(&#39;.total&#39;).text(groupTotal.toFixed(2));
  8. currentGroup.parents(&#39;table&#39;).find(&#39;.subtotal&#39;).text(groupTotal.toFixed(2));
  9. }
  10. $(&quot;.document.active&quot;).delegate( &quot;.tdDelete&quot;, &quot;click&quot;, function() {
  11. if ($(this).parents(&#39;tbody&#39;).children().length &gt; 1){
  12. $(this).prev().text(&#39;0&#39;);
  13. calculateTotal($(this));
  14. $(this).parents(&#39;tr&#39;).remove();
  15. }
  16. });
  17. $(&quot;.document.active&quot;).delegate( &quot;.trAdd&quot;, &quot;click&quot;, function() {
  18. $(this).parents(&#39;table&#39;).find(&#39;tbody&#39;).append( $(this).parents(&#39;table&#39;).find(&#39;tbody tr:last-child&#39;).clone() );
  19. calculateTotal($(this));
  20. });
  21. $(&quot;.document.active&quot;).delegate( &quot;.amount&quot;, &quot;keyup&quot;, function() {
  22. //console.log(&#39;test&#39;);
  23. calculateTotal($(this));
  24. });
  25. var tdValues = [];
  26. $(&quot;.document.active .proposedWork&quot;).delegate( &quot;td:not(.description .unit)&quot;, &quot;keyup&quot;, function() {
  27. tdValues.length = 0;
  28. //Paint
  29. $(this).parents(&#39;tr&#39;).find(&#39;td&#39;).each(function( i ){
  30. if(i &gt; 4){return false}
  31. if(i == 4){$(this).text( tdValues[0]*tdValues[3] )}
  32. tdValues[i] = Number( $(this).text() );
  33. });
  34. calculateTotal($(this));
  35. });
  36. });

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

  1. &lt;style type=&quot;text/css&quot;&gt;
  2. /* Housekeeping */
  3. body{
  4. font-size:12px;
  5. }
  6. .spreadSheetGroup{
  7. /*font:0.75em/1.5 sans-serif;
  8. font-size:14px;
  9. */
  10. color:#333;
  11. background-color:#fff;
  12. padding:1em;
  13. }
  14. /* Tables */
  15. .spreadSheetGroup table{
  16. width:100%;
  17. margin-bottom:1em;
  18. border-collapse: collapse;
  19. }
  20. .spreadSheetGroup .proposedWork th{
  21. background-color:#eee;
  22. }
  23. .tableBorder th{
  24. background-color:#eee;
  25. }
  26. .spreadSheetGroup th,
  27. .spreadSheetGroup tbody td{
  28. padding:0.5em;
  29. }
  30. .spreadSheetGroup tfoot td{
  31. padding:0.5em;
  32. }
  33. .spreadSheetGroup td:focus {
  34. border:1px solid #fff;
  35. -webkit-box-shadow:inset 0px 0px 0px 2px #5292F7;
  36. -moz-box-shadow:inset 0px 0px 0px 2px #5292F7;
  37. box-shadow:inset 0px 0px 0px 2px #5292F7;
  38. outline: none;
  39. }
  40. .spreadSheetGroup .spreadSheetTitle{
  41. font-weight: bold;
  42. }
  43. .spreadSheetGroup tr td{
  44. text-align:center;
  45. }
  46. /*
  47. .spreadSheetGroup tr td:nth-child(2){
  48. text-align:left;
  49. width:100%;
  50. }
  51. */
  52. /*
  53. .documentArea.active tr td.calculation{
  54. background-color:#fafafa;
  55. text-align:right;
  56. cursor: not-allowed;
  57. }
  58. */
  59. .spreadSheetGroup .calculation::before, .spreadSheetGroup .groupTotal::before{
  60. /*content: &quot;$&quot;;*/
  61. }
  62. .spreadSheetGroup .trAdd{
  63. background-color: #007bff !important;
  64. color:#fff;
  65. font-weight:800;
  66. cursor: pointer;
  67. }
  68. .spreadSheetGroup .tdDelete{
  69. background-color: #eee;
  70. color:#888;
  71. font-weight:800;
  72. cursor: pointer;
  73. }
  74. .spreadSheetGroup .tdDelete:hover{
  75. background-color: #df5640;
  76. color:#fff;
  77. border-color: #ce3118;
  78. }
  79. .documentControls{
  80. text-align:right;
  81. }
  82. .spreadSheetTitle span{
  83. padding-right:10px;
  84. }
  85. .spreadSheetTitle a{
  86. font-weight: normal;
  87. padding: 0 12px;
  88. }
  89. .spreadSheetTitle a:hover, .spreadSheetTitle a:focus, .spreadSheetTitle a:active{
  90. text-decoration:none;
  91. }
  92. .spreadSheetGroup .groupTotal{
  93. text-align:right;
  94. }
  95. table.style1 tr td:first-child{
  96. font-weight:bold;
  97. white-space:nowrap;
  98. text-align:right;
  99. }
  100. table.style1 tr td:last-child{
  101. border-bottom:1px solid #000;
  102. }
  103. table.proposedWork td,
  104. table.proposedWork th,
  105. table.exclusions td,
  106. table.exclusions th{
  107. border:1px solid #000;
  108. }
  109. table.proposedWork thead th, table.exclusions thead th{
  110. font-weight:bold;
  111. }
  112. table.proposedWork td,
  113. table.proposedWork th:first-child,
  114. table.exclusions th, table.exclusions td{
  115. text-align:left;
  116. vertical-align:top;
  117. }
  118. table.proposedWork td.description{
  119. width:80%;
  120. }
  121. table.proposedWork td.amountColumn, table.proposedWork th.amountColumn,
  122. table.proposedWork td:last-child, table.proposedWork th:last-child{
  123. text-align:center;
  124. vertical-align:top;
  125. white-space:nowrap;
  126. }
  127. .amount:before, .total:before{
  128. content: &quot;$&quot;;
  129. }
  130. table.proposedWork tfoot td:first-child{
  131. border:none;
  132. text-align:right;
  133. }
  134. table.proposedWork tfoot tr:last-child td{
  135. font-size:16px;
  136. font-weight:bold;
  137. }
  138. table.style1 tr td:last-child{
  139. width:100%;
  140. }
  141. table.style1 td:last-child{
  142. text-align:left;
  143. }
  144. td.tdDelete{
  145. width:1%;
  146. }
  147. table.coResponse td{text-align:left}
  148. table.shipToFrom td, table.shipToFrom th{text-align:left}
  149. .docEdit{border:0 !important}
  150. .tableBorder td, .tableBorder th{
  151. border:1px solid #000;
  152. }
  153. .tableBorder th, .tableBorder td{text-align:center}
  154. table.proposedWork td, table.proposedWork th{text-align:center}
  155. table.proposedWork td.description{text-align:left}

<!-- language: lang-html -->

  1. &lt;script src=&quot;https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js&quot;&gt;&lt;/script&gt;
  2. &lt;div class=&quot;document active&quot;&gt;
  3. &lt;div class=&quot;spreadSheetGroup&quot;&gt;
  4. &lt;hr style=&quot;visibility:hidden&quot;/&gt;
  5. &lt;form
  6. method=&quot;POST&quot;
  7. action=&quot;https://script.google.com/macros/s/AKfycbyV3gb0WtK83dir9R5TlpHP6zpnhpe9AJWGkMmJ3o-4b_NcroodQ5STpumi-hr-EnojwA/exec&quot;
  8. &gt;
  9. &lt;table class=&quot;proposedWork&quot; width=&quot;100%&quot; style=&quot;margin-top:20px&quot;&gt;
  10. &lt;thead&gt;
  11. &lt;th&gt;Email&lt;/th&gt;
  12. &lt;th&gt;Name&lt;/th&gt;
  13. &lt;th class=&quot;docEdit trAdd&quot;&gt;+&lt;/th&gt;
  14. &lt;/thead&gt;
  15. &lt;tbody&gt;
  16. &lt;tr&gt;
  17. &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;
  18. &lt;td contenteditable=&quot;true&quot; name=&quot;Name&quot;&gt;
  19. &lt;input name=&quot;Name&quot; type=&quot;name&quot; placeholder=&quot;Name&quot; required&gt;
  20. &lt;/td&gt;
  21. &lt;td class=&quot;docEdit tdDelete&quot;&gt;X&lt;/td&gt;
  22. &lt;/tr&gt;
  23. &lt;/tbody&gt;
  24. &lt;/table&gt;
  25. &lt;button type=&quot;submit&quot;&gt;Send&lt;/button&gt;
  26. &lt;/form&gt;
  27. &lt;/div&gt;
  28. &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 脚本。

从:

  1. const newRow = headers.map(function(header) {
  2. return header === 'Date' ? new Date() : e.parameter[header]
  3. })
  4. sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

到:

  1. const temp = headers.map(header => header === 'Date' ? new Date() : e.parameters[header]);
  2. const newRow = temp[1].map((e, i) => [temp[0], e, temp[2][i]]);
  3. sheet.getRange(nextRow, 1, newRow.length, newRow[0].length).setValues(newRow);
  • 根据你在问题中提供的结果图,我确认你的电子表格的列 "A" 到 "C" 分别是 "Date"、"Email" 和 "Name"。这在修改后的脚本中得到了体现。请注意这一点。

新增内容:

关于你接下来的新问题,

我试图添加列,但看起来只有前两列能够在我点击提交时被捕获,我如何包括其余的列?

在这种情况下,请按以下方式修改:

从:

  1. const newRow = headers.map(function(header) {
  2. return header === 'Date' ? new Date() : e.parameter[header]
  3. })
  4. sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

到:

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

  1. const newRow = headers.map(function(header) {
  2. return header === &#39;Date&#39; ? new Date() : e.parameter[header]
  3. })
  4. sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

To:

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

  1. const newRow = headers.map(function(header) {
  2. return header === &#39;Date&#39; ? new Date() : e.parameter[header]
  3. })
  4. sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

To:

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

确定