Show form result calculation after submit in apps script-CodePudding

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"  id="participants" name="participants" placeholder="Enter the number">

  <label for="engagment">Parents Engagment</label>
  <input type="text"  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

CodePudding user response：

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

When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

Reference:

withSuccessHandler(function)

CodePudding user response：

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.

Google Apps Script side: Code.gs

From:

To:

Javascript side: JavaScript.html

From:

To:

Note

Reference:

Google Apps Script side: `Code.gs`

Javascript side: `JavaScript.html`