Edit a cell value with a Form (Appscript in Gsheet)-CodePudding

Good morning,

I am building a form to handle a database in appscript.

I got some script from a similar past project so I might lack references but I can't find any ... (sorry)

The functions I already coded are:

Create a line in the database
Search a line in the database
Select and open a form with the prefilled data of the line

The function I am trying to code is :

Save the edited field in the original column

I am having troubles making this work, I always end up with nothing, no error message, just nothing !

Data looks like that :

ID	Field 1	Field 2	Field 3	Field 4
01	01-1	02-1	03-1	04-1
02	01-2	02-2	03-2	04-2
03	01-3	02-3	03-3	04-3
04	01-4	02-4	03-4	04-4

What I want to see if I EDIT line ID 03 :

ID	Field 1	Field 2	Field 3	Field 4
01	01-1	02-1	03-1	04-1
02	01-2	02-2	03-2	04-2
03	[EDIT 01-3]	[EDIT 02-3]	[EDIT 03-3]	[EDIT 04-3]
04	01-4	02-4	03-4	04-4

And here is my code (simplified version for lisibility):

In the HTML Form used for edition

<!DOCTYPE html>
<html> 

  <body>
    <section>
    
    <div>
      <button id="edit" onclick="Edit();Fill()">Edit</button>
    </div>

    <form id="myForm" onsubmit="handleFormSubmit2(this)">

    <section>

      <div>
        <label for="Field1">Field 1 :</label>
        <textarea type="text" id="Field1Edit" name="Field1Edit"></textarea>
      </div>    
      <div>
        <label for="Field2">Field 2:</label>
        <textarea type="text" id="Field2Edit" name="Field2Edit"></textarea>
      </div>    
      <div>
        <label for="Field3">Field 3:</label>
        <textarea type="text" id="Field3Edit" name="Field3Edit"></textarea>
      </div>
      <div>
        <label for="Field4">Field 4 :</label>
        <textarea type="text" id="Field4Edit" name="Field4Edit"></textarea>
      </div>    

    </section>
        
    <div>
      <button id="Submit" onclick="EditVal()">Submit</button>
    </div>

    </form>
    
    <div id="output"></div>

  <script>

  

  /* Trigger form */
  function Edit() {
    document.getElementById('myForm').style.display='block';
  }

  /* Prefill the fields with their previous values to allow the user to add information in succession */
  function Fill() {    

    var listData2 = JSON.parse(localStorage["Data"]); // Get stored listData

    var Field1 = listData2[1];
    var Field2 = listData2[2];
    var Field3 = listData2[3];
    var Field4 = listData2[4];
    
    document.getElementById("Field1Edit").value = Field1;
    document.getElementById("Field2Edit").value = Field2;
    document.getElementById("Field3Edit").value = Field3;
    document.getElementById("Field4Edit").value = Field4;
    
  }
       
  /* Prevent Form from submitting */
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i  ) {
      forms[i].addEventListener('Submit', function(event) {
        event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);    
  

  /* Edit Values */   
  function EditVal() {

    document.getElementById("Field1").value = formObject.Field1Edit;
    document.getElementById("Field2").value = formObject.Field2Edit;
    document.getElementById("Field3").value = formObject.Field3Edit;
    document.getElementById("Field4").value = formObject.Field4Edit;

  }
  



  /* Call Process Form 2 and reset the form */  
  function handleFormSubmit2(formObject) {

    google.script.run.processForm2(formObject);
    document.getElementById('myForm').reset();
  }
  
  </script> 
  </body>
</html>

And this is the script for "ProcessForm2" :

/* Process Form and replace data in the Sheet */
function processForm2(formObject) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracker");

  var mylist = [formObject.Field1,formObject.Field2,formObject.Field3,formObject.Field4];
  var cell = 0; /* Start of the range of modified cells */
  
  // Add data after the opening ones in the corresponding row
  for (var i=0; i<mylist.length; i  ){
    if ((mylist[i]).length > 0){sheet.getRange(formObject.num,cell).setValue(mylist[i])}
    cell  ;
  }  

}

When I submitted the form I just got my fields to reset and nothing happened

*In my opinion, the issue is located in EditVal() or the order it is trigger.

I tried to trigger it that way too, not working*

    <form id="myForm"  onsubmit="EditVal(); handleFormSubmit2(this)">

I'd appreciate some help,

Thank you in advance !

CodePudding user response：

It might be easier

if you add a field to the database that is called has_been_edited and is true or false, then you can just right to that field and test for it to be true. Hope this can help I am not familiar with appscript.

CodePudding user response：

Hello,

Thank you @TheMaster for the answer,

Indeed, I forgot

<input type="hidden" id="num" name="num">

In my form.

Thank you @Emel and @Wayne too for your help !

Here is the modified code :

<!DOCTYPE html>
<html> 

  <body>
    <section>
    
    <div>
      <button id="edit" onclick="Edit();Fill()">Edit</button>
    </div>

    <form id="myForm" onsubmit="handleFormSubmit2(this)">

    <section>

      <div>
        <label for="Field1">Field 1 :</label>
        <textarea type="text" id="Field1Edit" name="Field1Edit"></textarea>
      </div>    
      <div>
        <label for="Field2">Field 2:</label>
        <textarea type="text" id="Field2Edit" name="Field2Edit"></textarea>
      </div>    
      <div>
        <label for="Field3">Field 3:</label>
        <textarea type="text" id="Field3Edit" name="Field3Edit"></textarea>
      </div>
      <div>
        <label for="Field4">Field 4 :</label>
        <textarea type="text" id="Field4Edit" name="Field4Edit"></textarea>
      </div>
    
      <input type="hidden" id="num" name="num">

    </section>
        
    <div>
      <button id="Submit" onclick="alert('Your modification(s) have been taken into account !');">Submit</button>
    </div>

    </form>
    
    <div id="output"></div>

  <script>

  

  /* Trigger form */
  function Edit() {
    document.getElementById('myForm').style.display='block';
  }

  /* Prefill the fields with their previous values to allow the user to add information in succession */
  function Fill() {    

    var listData2 = JSON.parse(localStorage["Data"]); // Get stored listData

    var Field1 = listData2[1];
    var Field2 = listData2[2];
    var Field3 = listData2[3];
    var Field4 = listData2[4];
    
    document.getElementById("Field1Edit").value = Field1;
    document.getElementById("Field2Edit").value = Field2;
    document.getElementById("Field3Edit").value = Field3;
    document.getElementById("Field4Edit").value = Field4;
    
  }
       
  /* Prevent Form from submitting */
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i  ) {
      forms[i].addEventListener('Submit', function(event) {
        event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);    

  /* Call Process Form 2 and reset the form */  
  function handleFormSubmit2(formObject) {

    google.script.run.processForm2(formObject);
    document.getElementById('myForm').reset();
  }
  
  </script> 
  </body>
</html>

And

/* Process Form and replace data in the Sheet */
function processForm2(formObject) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tracker");

  var mylist = [formObject.Field1,formObject.Field2,formObject.Field3,formObject.Field4];
  var cell = 0; /* Start of the range of modified cells */
  
  // Add data after the opening ones in the corresponding row
  for (var i=0; i<mylist.length; i  ){
    if ((mylist[i]).length > 0){sheet.getRange(formObject.num,cell).setValue(mylist[i])}
    cell  ;
  }  

}