Show form result calculation after submit in apps script

user_19178191 0
Viewed 53

I'm trying to create an html form that loads data into a google sheet and after submitting and loading it to the sheet, I want to show in the html the result of calculating the fields. The calculation is in a specific cell in the google sheet according to the selected branch. How can I show the result after clicking submit?

Code.gs:

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

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function processForm(formObject){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var branch = formObject.branch;
  var participants = formObject.participants;
  var engagment = formObject.engagment;
  var predictedValue = 0;


  if (branch == "On") {sheet.getRange('B2').setValue(participants), sheet.getRange('F2').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Barak") {sheet.getRange('B3').setValue(participants), sheet.getRange('F3').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Giv'atayim") {sheet.getRange('B4').setValue(participants), sheet.getRange('F4').setValue, predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Gilad") {sheet.getRange('B5').setValue(participants), sheet.getRange('F5').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Ganim") {sheet.getRange('B6').setValue(participants), sheet.getRange('F6').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Lahav") {sheet.getRange('B7').setValue(participants), sheet.getRange('F7').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Magen") {sheet.getRange('B8').setValue(participants), sheet.getRange('F8').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Oz") {sheet.getRange('B9').setValue(participants), sheet.getRange('F9').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}
  else if (branch == "Rishonim") {sheet.getRange('B10').setValue(participants), sheet.getRange('F10').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}

}

Index.html:

    <!DOCTYPE html>
<html>
  <head>

  <?!= include('JavaScript'); ?>
  </head>
  
  <body>
    <h1>Enter your details</h1>
<form id="myForm" onsubmit="handleFormSubmit(this)">
  <label for="branch">Choose your branch:</label>
    <input list="magicHouses" id="branch" name="branch" placeholder="click here to choose" />
    <datalist id="magicHouses">
  <option value="On">
  <option value="Barak">
  <option value="Giv'atayim">
  <option value="Gilad">
  <option value="Ganim">
  <option value="Lahav">
    <option value="Magen">
  <option value="Ogen">
  <option value="Oz">
  <option value="Rishonim">
  </datalist>

  <label for="participants">Number of participants last year</label>
  <input type="text" class="form-control" id="participants" name="participants" placeholder="Enter the number">

  <label for="engagment">Parents Engagment</label>
  <input type="text" class="form-control" id="engagment" name="engagment" placeholder="Enter the number">



<button type="submit">Submit</button>

</form>


  </body>
</html>

JavaScript:

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

}



</script>

The field I'm trying to show is predictedValue. It can be a popup message etc or on another web page

google-apps-script
2 Answers

In your script, how about the following modification?

Google Apps Script side: Code.gs

From:

  else if (branch == "Rishonim") {sheet.getRange('B10').setValue(participants), sheet.getRange('F10').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}

}

To:

  else if (branch == "Rishonim") {sheet.getRange('B10').setValue(participants), sheet.getRange('F10').setValue(engagment), predictedValue = sheet.getRange('G2').getValue();}

  return predictedValue; // Added. This is important.
}

Javascript side: JavaScript.html

From:

google.script.run.processForm(formObject);

To:

google.script.run.withSuccessHandler(e => { // Here, you can use the returned value from Google Apps Script side.
  alert(e);
}).processForm(formObject);
  • In this modification, the returned value from Google Apps Script side can be retrieved by withSuccessHandler. Here, as a sample, alert is used. Please modify this for your actual situation.

Note

Reference:

user_71086530

As Tanaike said, you need to use a success handler. One potential issue with your code is that it does not handle possible multiple simultaneous form submissions. See the Web App Demo for an example of how to do that.

user_130451930
Related