javascript html google-apps-script google-sheets google-apps

How to save my table row items to my google sheet?

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

$(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));
    });
  
  
  

});

   <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}

<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>

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

Solution

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)".