CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
Memi
Memi

Reputation: 13

Add fields to this html form who add/edit data in a spreadsheet

I found the scripts below on this post, and I tried to figure out how to add a new column on the sheet and make it work with the html form the same way as the two columns already existing

But without success...

If someone can take the time to explain to me how to do it, it would be very nice 🙏 The HTML /CSS side and basic JS are understandable for me but the rest stay hard to understand by myself

Here the sheet sample

Thanks !

CODE.GS

function doGet(request) {
  return HtmlService.createTemplateFromFile('Form').evaluate();
}

/* @Include JavaScript and CSS Files */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename)
      .getContent();
}



/* Find ID*/

function getID(IDsearch){
  var url = "https://docs.google.com/spreadsheets/d/1QESrQb4rYhmr0uc7q6ptvmdmMbo0Bxp_hZrvKaobdI8/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);  
  var ws = ss.getSheetByName("Database");
  /*set cells to plain text*/
  var range = ws.getRange(1, 1, ws.getMaxRows(), ws.getMaxColumns());
  range.setNumberFormat("@");

  var data = ws.getRange(3, 1, ws.getLastRow(), 2).getValues();


  var dataInput = data.map(function(r){return r[1];}); //ID column

  var position = dataInput.indexOf(IDsearch); //index of the row where ID is
  Logger.log(position);
  var dataArray = ws.getRange(position+3, 1, 1, 2).getValues(); //array with data from searched ID
  var clientsDataString = dataArray.toString();
  var clientsDataArray = clientsDataString.split(',');


  if(position > -1){
    return clientsDataArray;
  } else {
    return position;
  }

}




function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1QESrQb4rYhmr0uc7q6ptvmdmMbo0Bxp_hZrvKaobdI8/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Database");

  var ranges = ws.getRange(4, 2, ws.getLastRow() - 3, 1).createTextFinder(formObject.ID).findAll();
  if (ranges.length > 0) {
    for (var i = 0; i < ranges.length; i++) {
      ranges[i].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);
    }
  } else {
    ws.appendRow([formObject.name, formObject.ID]);
  }
}

JavaScript.html

<script>

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


  function handleFormSubmit(formObject) {
    google.script.run.processForm(formObject);
    document.getElementById("myForm").reset();


  }


/* Search for ID */
document.getElementById("btn-procurar").addEventListener("click", onSearch);

function onSearch() {

  var IDsearch = document.getElementById("insertID").value;
  google.script.run.withSuccessHandler(populateForm).getID(IDsearch);

}


function populateForm(clientsData) {

  document.getElementById("name").value = clientsData[0];
  document.getElementById("ID").value = clientsData[1];


}


</script>

form.html

<!DOCTYPE html>
<html>

<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, shrink-to-fit=no">
    <link rel="stylesheet" href="/bootstrap/css/bootstrap.min.css">
    <link rel="stylesheet" href="/Contact-Form-Clean.css">
    <link rel="stylesheet" href="/styles.css">
    <?!= include('JavaScript'); ?>
    <?!= include('form-css'); ?>

</head>


<body>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <div class="register-photo" style="padding-top: 30px;">
        <div class="form-container" style="width: 695px;">
            <form id="myForm" onsubmit="handleFormSubmit(this)" method="post" style="width: 720px;padding: 10px;padding-right: 20px;padding-left: 25px;">
                <input class="form-control" type="text" id="name" name="name" placeholder="Name" style="width: 300px;display: inline-block;margin-bottom: 20px;">
                    <input class="form-control" type="number" id="ID" name="ID" placeholder="ID" style="width: 165px;display: inline-block;" required="">
                    <button type="submit" id="btn-submeter" onclick="return confirm('Submit?')" class="btn btn-primary btn-block" style="width: 644px;margin-bottom: 35px;">Save data</button>
                    <script>
                        document.getElementById("myForm").addEventListener("submit", myFunction);
                        function myFunction() {
                        alert("Success");
                        }
                    </script>
                    <div id="output"></div>
                    <div style="margin-bottom: 15px;padding: 5px;background-color: rgba(255,255,255,0);padding-top: 0px;border-top: double;">
                    <h6 class="text-left" style="margin-top: 15px;display: inline-block;width: 519px;margin-right: 20px;margin-bottom: 10px;">Search/Fetch ID</h6>
                    <input class="form-control" type="text" id="insertID" name="insertID" placeholder="Insert ID" style="width: 155px;display: inline-block;">
                    <button class="btn btn-primary" id="btn-procurar" onclick="onSearch()" type="button" style="width: 450px;margin: 10px 0px 25px 0px;padding: 6px;margin-top: 0px;margin-bottom: 0px;margin-left: 29px;">Search by ID</button>
                    </div>

           </form>
        </div>
    </div>
    <script src="/js/jquery-3.4.1.min.js"></script>
    <script src="/bootstrap/js/bootstrap.min.js"></script>
</body>

</html>

Upvotes: 1

Views: 456

Answers (1)

Tanaike
Tanaike

Reputation: 201398

  • You want to put the values of "name", "ID", "address", "email" and "phone" using the HTML form.
    • In your updated shared Spreadsheet, 5 input tags are put.
  • You want to achieve this by modifying your script.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • In your current script, 2 values are used like [formObject.name, formObject.ID].
    • For this, please modify to 5 values like [formObject.name, formObject.ID, formObject.address, formObject.email, formObject.phone].

modified script

When your script is modified, it becomes as follows.

From: 
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1QESrQb4rYhmr0uc7q6ptvmdmMbo0Bxp_hZrvKaobdI8/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Database");

  var ranges = ws.getRange(4, 2, ws.getLastRow() - 3, 1).createTextFinder(formObject.ID).findAll();
  if (ranges.length > 0) {
    for (var i = 0; i < ranges.length; i++) {
      ranges[i].offset(0, -1, 1, 2).setValues([[formObject.name, formObject.ID]]);
    }
  } else {
    ws.appendRow([formObject.name, formObject.ID]);
  }
}
To: 
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/1QESrQb4rYhmr0uc7q6ptvmdmMbo0Bxp_hZrvKaobdI8/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Database");

  var ranges = ws.getRange(4, 2, ws.getLastRow() - 3, 1).createTextFinder(formObject.ID).findAll();
  var v = [formObject.name, formObject.ID, formObject.address, formObject.email, formObject.phone];  // Added
  if (ranges.length > 0) {
    for (var i = 0; i < ranges.length; i++) {
      ranges[i].offset(0, -1, 1, v.length).setValues([v]);  // Modified
    }
  } else {
    ws.appendRow(v);  // Modified
  }
}

Note:

  • In your updated shared Spreadsheet, type="number" is used for the input tag of phone. In this case, for example, when the value is 01 33 33 33 33 33, an error occurs because 01 33 33 33 33 33 is not the number. If you want to show 01 33 33 33 33 33, please modify to type="string".

Upvotes: 1

Related Questions