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

Yael is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

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: