How to pull the data from Google Sheets to Webapp after submitting a value from form?

user_11823363 0
Viewed 118

I managed to work on a form that submits data to google sheets in the A column, After submitting the form in Column A, the formula does the calcuations in Columns B,C,D, I want to pull the data in Column B, Column C, and Column D results and display under the submit button.

What modification should be done for the following could be done?

My Google Sheets enter image description here

My Form enter image description here

Code.gs

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

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



/* @Process Form */
function processForm(formObject) {
  var url = "https://docs.google.com/spreadsheets/d/12G9g4aCbW41Js5wS33-7gTEO1r0SPh9lYauIJL53Jjk/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  
  ws.appendRow([formObject.number_data]);
}

Index.html

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
        <?!= include('JavaScript'); ?>
    </head>
    <body>
        <div class="container">
            <div class="row">
                <div class="col-6">
                    <form id="myForm" onsubmit="handleFormSubmit(this)">
                        <p class="h4 mb-4 text-center">Values</p>

                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <label for="number_data">Enter a Value</label>
                                <input type="text" class="form-control" id="number_data" name="number_data" placeholder="Enter Number">
                            </div>
                          

                        <button type="submit" class="btn btn-primary btn-block">Submit</button>
                    </form>
                    <div id="output"></div>
                </div>
            </div>      
        </div>
    </body>
</html>

JavaScript.html

<script>
  // Prevent forms 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);    
      
      
  function handleFormSubmit(formObject) {
    google.script.run.processForm(formObject);
    document.getElementById("myForm").reset();
  }
</script>
google-apps-script javascript
1 Answers

In your processForm() function, Add a statement that will get the Col B, C, D of your newly added data and add a return statement which the callback function will use to write the data.

Try this:

Code.gs

/* @Process Form */
function processForm(formObject) {
  var url = "Sheet url";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");
  ws.appendRow([formObject.number_data]);
  var data = ws.getRange(ws.getLastRow(), 2, 1, 3).getValues();
  return data;
}

JavaScript.html

<script>
  // Prevent forms 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);    
      
      
  function handleFormSubmit(formObject) {
    google.script.run.withSuccessHandler(writeData).processForm(formObject);
    document.getElementById("myForm").reset();
  }

  function writeData(data){
    document.getElementById("output1").innerHTML = data[0][0];
    document.getElementById("output2").innerHTML = data[0][1];
    document.getElementById("output3").innerHTML = data[0][2] ; 
  }

</script>

Index.html

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
        <?!= include('JavaScript'); ?>
    </head>
    <body>
        <div class="container">
            <div class="row">
                <div class="col-6">
                    <form id="myForm" onsubmit="handleFormSubmit(this)">
                        <p class="h4 mb-4 text-center">Values</p>

                        <div class="form-row">
                            <div class="form-group col-md-6">
                                <label for="number_data">Enter a Value</label>
                                <input type="text" class="form-control" id="number_data" name="number_data" placeholder="Enter Number">
                            </div>
                          

                        <button type="submit" class="btn btn-primary btn-block">Submit</button>
                    </form>
                    <div>
                      <div class="row">
                        <label>Data A : </label>
                        <div id="output1"></div>
                      </div>
                      <div class="row">
                        <label>Data B : </label>
                        <div id="output2"></div>
                      </div>
                      <div class="row">
                        <label>Data C : </label>
                        <div id="output3"></div>
                      </div>
                    </div>
                </div>
            </div>      
        </div>
    </body>
</html>

Sheet:

enter image description here

Output:

enter image description here

References:

user_146060520
Related