Input value search on a variable of range of values from Google Sheets

Question

I have a code that searches in a google sheet cell a value entered in an input.

I would like to see the possibility of that value being looked up in a range of cells in a google sheet (instead of a single cell), and check if the data captured in the input matches any of the values in the range of cells of the google sheet.

I can't find how to identify the individual value entered in the input, but I have seen that if I enter in the input all the values in the range of the Google sheet and they are separated by a comma (example: Peter,George,Sophie,Neil,Carl), the code runs fine. The point is that only one value should be captured in the input field.

How do I enter a single value in the input field and it can be searched and check in the range of values of the google sheet only using javascript?

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 runCheck() {
        google.script.run.withSuccessHandler(checkUser).fetchUserValues1()
    }
</script>

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

</html>

GS

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

function fetchUserValues1(){
    var ss = SpreadsheetApp.openByUrl("Google Sheet URL"); 
     var sheetNames = ss.getSheetByName("Sheet 1"); 
  var user_name  =  sheetNames .getRange("A2:A6").getValues();
    return user_name;
}

Tanaike · Accepted Answer · 2023-01-07 23:54:27Z

2

In your script, how about modifying it as follows?

From:

if (document.getElementById("name").value == user_name) {
    alert("correct");
}
else {
    alert("incorrect");
}

To:

user_name = user_name.flat();
const ar = document.getElementById("name").value.split(",").filter(e => user_name.includes(e.trim()));
console.log(ar); // Here, you can check the matched values in the log.
if (ar.length > 0) {
  alert("correct");
} else {
  alert("incorrect");
}

or, I think that you can also modify as follows.

const v = document.getElementById("name").value.split(",").map(e => e.trim());
const ar = user_name.filter(([e]) => v.includes(e));
console.log(ar); // Here, you can check the matched values in the log.
if (ar.length > 0) {
  alert("correct");
} else {
  alert("incorrect");
}

By this modification, when one of the inputted values is matched in user_name, alert("correct") is run.

edited Jan 7, 2023 at 23:54

answered Jan 7, 2023 at 23:40

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ONTIVER Over a year ago

This solve the issue. Thanks for your time and effort. Thanks a lot.

Collectives™ on Stack Overflow

Input value search on a variable of range of values from Google Sheets

1 Answer 1

From:

To:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related