VARIABLE VALUE FECTH FROM GOOGLE SHEET CELL VALUE (GOOGLE APPS SCRIPTS) (JAVASCRIPT)-CodePudding

Good afternoon everyone, I was hoping to see if someone can help me with this question. I have a code that detects if an input has been filled with the correct data (for example: user name), validating it from the value of a variable. I wanted to see if there is a possibility that instead of validating the value from a variable, that value could be validated from a google sheet. Or see if the value of the variable, instead of setting it directly in the code, can be read from a google sheet. Hope there is a way, preferably using only Javascript. I am attaching a copy of the HTML and GS code and an image of a google sheet (pic 1), where the value to be validated is located. In this example, if the name "JHON" is captured in the input, a "correct" message will be displayed, if a different value is entered, the "incorrect" message will be displayed. The data to be validated is found in the value of a variable (var value_name = "JHON"), which is entered directly in the variable (pic 2), but I need the data to be validated from the google sheet. Hope there is a way, thanks in advance.

HTML

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

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

   <input type='button' value='VALIDATE' onclick="checkUser()" >

</html>

GS
(function "fetchUserValues1()", is the function a think is need to fecth the value from GoogleSheets, but cant get it to work)

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


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

CodePudding user response：

So looking at what you're doing and at your code, I think you've missed a few things with using the Google "HTML Service".

https://developers.google.com/apps-script/guides/html/communication

The top of the page has an excellent simple example of an index.html and it's paired code.gs.

I think given your provided code you'd probably want your checkUser to do a google.script.run.fetchUserValues1() or have the button do like google.script.run.withSuccessHandler(checkUser).fetchUserValues1()

You'd probably want the success handler to be able to pass the output of the .fetchUserValues1() into your checkUser() function.

So your HTML might look like...

<!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>

<input type='button' value='VALIDATE' onclick="handleButton()">

</html>