I am a student who learn to code Apps Script. I want to create a form with 2 number input, let say A & B, with a button. When user submit the form, the script will search column A & B in active Google Spreadsheet sheet that match with 2 input and query a result in the column C on the same row. Finally, the result C will appear below the form.
The problem is that, when the form appear, I input 2 values but the result don't work.

I wrote two file code like this in Apps Script:

1. The HTML file

<form onsubmit="handleFormSubmit(event)">
  <label for="inputA">height:</label><br>
  <input type="number" id="inputA" name="inputA"><br>
  <label for="inputB">weight:</label><br>
  <input type="number" id="inputB" name="inputB"><br>
  <input type="submit" value="Submit">
</form><br>
<script>
  function handleFormSubmit(event) {
    // Prevent the form from refreshing the page
    event.preventDefault();

    // Get the input values from the form
    var inputA = document.getElementById("inputA").value;
    var inputB = document.getElementById("inputB").value;

    // Search the Google Sheet for a matching row
    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getDataRange().getValues();
    var result = "";
    for (var i = 0; i < data.length; i  ) {
      if (data[i][0] == inputA && data[i][1] == inputB) {
        result = data[i][2];
        break;
      }
    }

    // Display the result on the page
    var resultContainer = document.getElementById("result");
    resultContainer.innerHTML = result;
  }
</script>
<label for="resultA">result is:</label><div id="result"></div>
  <!-- Result will be added here -->

2. The script file:

function doGet(e) {
  return HtmlService.createTemplateFromFile('searchForm.html')
    .evaluate();
}

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

The sheet link is here: https://docs.google.com/spreadsheets/d/1DqjBbU4b0uDTtjYsBFVFDuvA9fthR1N3KUCtkvwRowc/edit?usp=sharing

I think the problem is the code in script tag but I don't quite sure.

CodePudding user response：

Modification points:

• In your script, it seems that you are using Google Apps Script in the Javascript on the HTML side. Google Apps Script can be used on the server side. So, in this case, google.script.run is used.

When these points are reflected in your script, how about the following modification?

Modified script: searchForm.html

HTML & Javascript:

<form>
  <label for="inputA">Input A:</label><br>
  <input type="number" id="inputA" name="inputA"><br>
  <label for="inputB">Input B:</label><br>
  <input type="number" id="inputB" name="inputB"><br>
  <input type="submit" value="Submit" onclick="handleFormSubmit(event)">
</form> 
<div id="result">
</div>
<script>
function handleFormSubmit(event) {
  event.preventDefault();
  var inputA = document.getElementById("inputA").value;
  var inputB = document.getElementById("inputB").value;
  google.script.run.withSuccessHandler(result => {
    var resultContainer = document.getElementById("result");
    resultContainer.innerHTML = result;
  }).sample(inputA, inputB);
}
</script>

Google Apps Script: code.gs

function doGet(e) {
  return HtmlService.createTemplateFromFile('searchForm.html').evaluate();
}

function sample(inputA, inputB) {
  var sheet = SpreadsheetApp.getActiveSheet(); // or var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var data = sheet.getDataRange().getValues();
  var result = "";
  for (var i = 0; i < data.length; i  ) {
    if (data[i][0] == inputA && data[i][1] == inputB) {
      result = data[i][2];
      break;
    }
  }
  return result;
}

Note:

• As an important point of this modification, google.script.run is run with the asynchronous process. Please be careful about this.

• When you modified the Google Apps Script of Web Apps, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful about this.

• You can see the detail of this in my report "Redeploying Web Apps without Changing URL of Web Apps for new IDE (Author: me)".

• Thit is a sample modification. So, please modify this for your actual situation. If you want to know about google.script.run more, you can also see various sample scripts at Stackoverflow.

Reference:

• Class google.script.run (Client-side API)