需要检查登录用户名和密码，然后从以前的条目中获取数据 [英] Need to check for login username and password, and then bring data from previous entries
问题描述
Following this question I had asked previously (Need to Query Google Sheet for specific data from Google Apps Script).
我创建了一个网络应用程序来跟踪公司员工的工作时间，该应用程序很简单，它首先要求他们提供用户名和密码，然后允许他们注册其进入公司的时间退出时间.现在，我需要找到一种方法来检查用户名和密码(在数据库中)是否匹配，如果是这样，则将有关该雇员的最后一次提交的信息带到Web应用程序中，该最后一次提交的数据可以在另一个应用程序上找到从Web应用程序接收数据的表格.
I have created a web app to track the working hours of employees at my company, the web app is simple, it first asks them to provide their username and a password, and then allows them to register their time of entry o their exit time. Now, I need to find a way to check for a match between username and password (in a data base), and if this is true, bring information about that employee's last submission to the web app, this last submission data is found on another sheet that receives the data from the web app.
这里是一个最小的可重现示例，在该示例中，它只要求输入名称和密码，如果正确，则显示另一个显示，并将该用户最后一次提交的时间戳记带到Web应用程序中.
Here is a minimal reproducible example, where its just asks for a name and a password, and if correct, show another display, where it brings the timestamp of that user's last submission to the web app.
var name="";
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Form');
}
function AddRecord(Name) {
// get spreadsheet details
var url = 'https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0';
//Paste URL of GOOGLE SHEET
var ss1= SpreadsheetApp.openByUrl(url);
var webAppSheet1 = ss1.getActiveSheet();
const Lrow = webAppSheet1.getLastRow();
const data = [Name, new Date ()];
webAppSheet1.getRange(Lrow+1,1, 1, data.length).setValues([data])
}
function checklogin(Name,Password) {
var url = 'https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0'; //Paste URL of GOOGLE SHEET
var ss2= SpreadsheetApp.openByUrl(url);
var webAppSheet2 = ss2.getSheetByName("DataBase");
var checkuser = webAppSheet2.getRange(2, 1, webAppSheet2.getLastRow(), 1).createTextFinder(Name).matchEntireCell(true).findNext();
var obj = {checkuser: checkuser ? 'TRUE' : 'FALSE'};
var sheet = ss2.getSheetByName("ReceivedData");
var ranges = sheet.getRange(2, 1, sheet.getLastRow(), 1).createTextFinder(Name).matchEntireCell(true).findAll();
if (ranges.length > 0) {
obj.lastTimestamp = ranges.pop().offset(0, 1, 1, 1).getDisplayValue();
return obj;
}
return obj;
}<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
#LastR{
display: inline-block;
}
</style>
<script>
function AddRow()
{
var Name = document.getElementById("Name").value;
google.script.run.AddRecord(Name);
document.getElementById("Name").value = '';
}
function LoginUser() {
var Name = document.getElementById("Name").value;
var Password = document.getElementById("Password").value;
google.script.run.withSuccessHandler(function({checkuser, lastTimestamp}) {
if(checkuser == 'TRUE') {
document.getElementById("loginDisplay").style.display = "none";
document.getElementById("dataDisplay").style.display = "block";
document.getElementById("lastTimestamp").innerHTML = lastTimestamp;
} else if(checkuser == 'FALSE') {
document.getElementById("errorMessage").innerHTML = "Name not found";
}
}).checklogin(Name,Password);
}
</script>
</head>
<body>
<div id="loginDisplay">
<div>
<label>Name</label><br>
<input type="text" id="Name" />
</div>
<div>
<label>Password</label><br>
<input type="text" id="Password" />
</div>
<div class="btn">
<input value="Login" onclick="LoginUser()"
type="button">
<span id="errorMessage"></span>
</div>
</div>
<div style="display:none" id="dataDisplay">
<div>
<label>Last Registration</label>
<br><br>
<div class="LastR" id="lastTimestamp"></div>
<button type="button" value="Add" onclick="AddRow()">Send</button>
</div>
</div>
</body>
</html>这是一张工作表，您可以在其中处理或复制信息. https://docs.google.com/spreadsheets/d/1CJoPuq3edit#gid = 0
And here is a sheet where you can work from or copy the information. https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0
推荐答案
我相信您的目标如下.
- 您要从电子表格的
Database表中搜索<code> Name 和Password的输入值. - 您要通过从
DataBase工作表中搜索输入的值来显示从ReceivedData工作表中检索到的最后一个时间戳.
- You want to search the inputted values of
NameandPasswordfromDataBasesheet in the Spreadsheet. - You want to show the last timestamp retrieved from
ReceivedDatasheet by searching the inputted values fromDataBasesheet.
在这种情况下，我想建议修改 var obj = {checkuser:checkuser?用于实现您的目标，如下所示. checklogin 函数的'TRUE':'FALSE'};
In this case, I would like to propose to modify var obj = {checkuser: checkuser ? 'TRUE' : 'FALSE'}; of the function checklogin for achieving your goal as follows.
var obj = {checkuser: checkuser ? 'TRUE' : 'FALSE'};
收件人:
var obj = {checkuser: checkuser && checkuser.offset(0, 1).getValue() == Password ? 'TRUE' : 'FALSE'};
- 在此修改中，从第一列中搜索输入的
Name值，然后从第二列中与搜索到的第一列相同的行检查Password柱子.并且，当两个值相同时，返回TRUE. - In this modification, the inputted
Namevalue is searched from the 1st column, and then,Passwordis checked from the 2nd column with the same row of the searched 1st column. And, when the both values are same,TRUEis returned.
这篇关于需要检查登录用户名和密码，然后从以前的条目中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
