using GAS I'm trying to pass an input value to server side function that searches google sheet values for the row where the value is and returns other values in the same row as follows
JS
function nameSearch(serial){
try {
var sheet = SpreadsheetApp.getActive().getSheetByName("sheetName");
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
if (data[i][1] == serial) {
var name = data[i][0];
var email = data[i][2];
return { name: name, email: email };
}
}
} catch(e) {
alert(e)
}
}
HTML
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<div>
<input type="text" id="Name">
<input type="text" id="serial" onkeyup="search()">
<input type="email" id="email">
</div>
</body>
<script>
function search() {
var serial = document.getElementById("serial").value;
google.script.run.withSuccessHandler(onSuccess).nameSearch(serial);
}
function onSuccess(result) {
document.getElementById("Name").value = result.name;
document.getElementById("email").value = result.email;
}
</script>
</html>
yet the above code is working but when I add another if condition to the JS function like below
if (data[i][1] == serial) {
var name = data[i][0];
var email = data[i][2];
return { name: name, email: email };
} else {
return { name: "Null!", email: "Null!" };
}
Then only the first row data in the sheet is returned and all other rows give "Null" even when the input value matches. Any help please, thank you!
If you add an else clause, the function will return prematurely, unless the first row is the one that matches the serial. To prevent that, move the second return statement outside of the for loop, like so:
function nameSearch(serial) {
try {
var sheet = SpreadsheetApp.getActive().getSheetByName("sheetName");
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
if (data[i][1] == serial) {
var name = data[i][0];
var email = data[i][2];
return { name: name, email: email };
}
}
return { name: "Null!", email: "Null!" };
} catch(e) {
alert(e)
}
//or move the return statement here if you want to have { name: "Null!", email: "Null!" } also returned, when an error occurs. Otherwise, it will just return null in case of an error.
}