Retrieve values from Google Sheet to Web App inputs-CodePudding

I have a code that searches for a value entered in an INPUT field, and it looks for it on a Google sheet, and if the value exists, it returns a "correct" message, if the value does not exist, it returns an "incorrect" message ". What I am looking for is that the value entered in the INPUT field is searched on a Google sheet range of values, if the value entered in the INPUT field matches a value in the Google sheet range (column A), then show the "correct" message, and populate the fallowing INPUTS that correspond to the entered value. Example: If the name "Peter" is entered, when pressing "VALIDATE", the message "correct" should appear, and in the fields: ITEM, QUANTITY and AMOUNT, the values corresponding to the name entered in the INPUT should appear, in In this case, the name "Peter" was entered so: ITEM = Item 1, QUANTITY = 25 and AMOUNT = $55.00, in the corresponding INPUT fields.
I know the line: var user_name = sheetNames .getRange("A2").getValue();, should include the search range, in this case it would be "A2:D6", and change "getValue" to "getValues", but there I'm stuck. I don't know how to request that it search for a single value in the entire range of the google sheet and how to make it extract the values from the line to which the entered name corresponds, and then pass them to the corresponding INPUTS on the Web App. All of this, using only JavaScript.

 function doGet() {
  var template = HtmlService.createTemplateFromFile("page1")
  return template.evaluate().setSandboxMode(HtmlService.SandboxMode.IFRAME);
  }

function fetchUserValues1(){
    var ss = SpreadsheetApp.openByUrl("GOOGLE SHEET URL"); 
     var sheetNames = ss.getSheetByName("Sheet 1"); 
     var user_name  =  sheetNames .getRange("A2").getValue();
    return user_name;
}

HTML

<!DOCTYPE html>
<html>

<head>
    <base target="_top">
</head>
<br>
NAME:<br>
<input type="text" id="name">

<script>
    function checkUser(user_name) {
        if (document.getElementById("name").value == user_name) {
            alert("correct");
        }
        else {
            alert("incorrect");
        }
    }

    function handleButton() {
        google.script.run.withSuccessHandler(checkUser).fetchUserValues1()
    }
</script>
<br>
<br>
<input type='button' value='VALIDATE' onclick="handleButton()">
<br>
<br>
<br>
ITEM:<br>
<input type="text" id="Item"><br>
QUANTITY:<br>
<input type="text" id="Quantity"><br>
AMOUNT:<br>
<input type="text" id="Amount">

</html>

CodePudding user response：

In your script, how about the following modification?

Google Apps Script side:

function fetchUserValues1(v) {
  var ss = SpreadsheetApp.openByUrl("GOOGLE SHEET URL"); 
  var sheetNames = ss.getSheetByName("Sheet 1"); 
  var r = sheet.getRange("A2:A"   sheet.getLastRow()).createTextFinder(v).matchEntireCell(true).findNext();
  var col = sheet.getLastColumn();
  return r ? r.offset(0, 0, 1, col).getValues()[0] : Array(col).fill("");
}

HTML & Javascript side:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
</head>

<body>
  <br>
NAME:<br>
  <input type="text" id="name">
  <br>
  <br>
  <input type='button' value='VALIDATE' onclick="handleButton()">
  <br>
  <br>
  <br>
ITEM:<br>
  <input type="text" id="Item"><br>
QUANTITY:<br>
  <input type="text" id="Quantity"><br>
AMOUNT:<br>
  <input type="text" id="Amount">
</body>

<script>
function checkUser(values) {
  document.getElementById("Item").value = values[1];
  document.getElementById("Quantity").value = values[2];
  document.getElementById("Amount").value = values[3];
  if (!values.every(e => !e)) {
    alert("correct");
  } else {
    alert("incorrect");
  }
}

function handleButton() {
  const value = document.getElementById("name").value;
  google.script.run.withSuccessHandler(checkUser).fetchUserValues1(value);
}
</script>

</html>

In this script, when the value of name is found, the values from column "B" to "D" are put into the input tags. When the value of name is not found, the empty values from column "B" to "D" are put into the input tags.

Note:

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)".

Google Apps Script side:

HTML & Javascript side:

Note:

References: