英文:
Retrieve values from Google Sheet to Web App inputs
问题
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 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.
GS
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>
英文:
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.
GS
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>
答案1
得分: 3
在你的脚本中，以下是可能的修改：
Google Apps Script 部分：
function fetchUserValues1(v) {
var ss = SpreadsheetApp.openByUrl("GOOGLE SHEET URL");
var sheet = 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 部分：
<!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>
在这个脚本中，当name的值被找到时，将从列"B"到"D"的值放入输入标签中。当name的值未找到时，将从列"B"到"D"的空值放入输入标签中。
英文:
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
nameis found, the values from column "B" to "D" are put into the input tags. When the value ofnameis 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)".
References:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论