检查登录用户名和密码，然后从以前的每个条目中获取数据 [英] Check for login username and password, and then bring data from every previous entry
问题描述
Similar to a question I had asked previously (Need to check for login username and password, and then bring data from previous entries).
我创建了一个网络应用程序来跟踪公司员工的工作时间，该应用程序很简单，它首先要求他们提供用户名和密码，然后允许他们注册其进入公司的时间退出时间.现在，我需要找到一种方法来检查用户名和密码(在数据库中)是否匹配，如果是这样，请携带有关该员工先前向Web应用程序提交的所有先前提交信息的信息，以及这些先前输入的信息.在从网络应用程序接收数据的Google工作表中找到.
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 all of the previous submissions that employee has made to the web app, these previous entries are found on a google sheet that receives the data from the web app.
这是一个最小的可复制示例，在该示例中，它仅询问名称和密码，如果为true，则显示另一个显示，在该显示中首先询问输入日期和时间，然后仅将该员工的最后一次提交带到Web应用程序，但现在我需要带出该员工以前所做的所有输入.
Here is a minimal reproducible example, where its just asks for a name and a password, and if true, show another display, where it first asks for a date and time of entry and then it only brings that employee's last submission to the web app, but I now need for to bring every previous entry that employee has made.
var name="";
function doGet(e) {
return HtmlService.createHtmlOutputFromFile('Form');
}
function AddRecord(Name, Date, Time) {
// 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, Date, Time];
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 && checkuser.offset(0, 1).getValue() == Password ? '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) {
var last = ranges.pop();
obj.lastDate = last.offset(0, 1, 1, 1).getDisplayValue();
obj.lastTime = last.offset(0, 2, 1, 1).getDisplayValue();
return obj;
}
return obj;
}<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
</style>
<script>
function AddRow()
{
var Name = document.getElementById("Name").value;
var Date = document.getElementById("Date").value;
var Time = document.getElementById("Time").value;
google.script.run.AddRecord(Name, Date, Time);
document.getElementById("Name").value = '';
document.getElementById("Date").value = '';
document.getElementById("Time").value = '';
}
function LoginUser() {
var Name = document.getElementById("Name").value;
var Password = document.getElementById("Password").value;
google.script.run.withSuccessHandler(function({checkuser, lastTime, lastDate}) {
if(checkuser == 'TRUE') {
document.getElementById("loginDisplay").style.display = "none";
document.getElementById("dataDisplay").style.display = "block";
document.getElementById("lastTime").innerHTML = lastTime;
document.getElementById("lastDate").innerHTML = lastDate;
} 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>
<input value="Login" onclick="LoginUser()"type="button">
<span id="errorMessage"></span>
</div>
</div>
<div style="display:none" id="dataDisplay">
<div>
<label>Date</label><br>
<input type="date" id="Date" />
</div>
<div>
<label>Time</label><br>
<input type="time" id="Time" />
</div>
<div>
<button type="button" value="Add" onclick="AddRow()">Send</button>
</div>
<div>
<br><label>Last Registration</label><br>
<span id="lastTime"></span> || <span id="lastDate"></span>
</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
推荐答案
我相信您的目标如下.
- 为
checklogin函数赋予Name和Password时，您要检索所有匹配的值.
- When
NameandPasswordare given to the function ofchecklogin, you want to retrieve all matched values.
-
在这种情况下，我认为您的Google Apps脚本端的以下部分需要修改.
In this case, I thought that the following part in your Google Apps Script side is required to be modified.
var last = ranges.pop();
obj.lastDate = last.offset(0, 1, 1, 1).getDisplayValue();
obj.lastTime = last.offset(0, 2, 1, 1).getDisplayValue();
在这种情况下，使用 offset(0，1，1，2)，一次调用即可检索2列.
And, in this case, offset(0, 1, 1, 2) is used, 2 columns can be retrieved by one call.
当您要显示时间和日期时，例如 time ||日期，则需要修改您的HTML和Javascript.
When you want to display the time and date like time || date, it is required to modify your HTML and Javascript.
当以上几点反映到您的脚本中时，它如下所示.
When above points are reflected to your script, it becomes as follows.
请如下修改功能 checklogin .
var last = ranges.pop();
obj.lastDate = last.offset(0, 1, 1, 1).getDisplayValue();
obj.lastTime = last.offset(0, 2, 1, 1).getDisplayValue();
至:
obj.dateTime = ranges.flatMap(r => r.offset(0, 1, 1, 2).getDisplayValues());
HTML和Javascript端.
从:google.script.run.withSuccessHandler(function({checkuser, lastTime, lastDate}) {
if(checkuser == 'TRUE') {
document.getElementById("loginDisplay").style.display = "none";
document.getElementById("dataDisplay").style.display = "block";
document.getElementById("lastTime").innerHTML = lastTime;
document.getElementById("lastDate").innerHTML = lastDate;
} else if(checkuser == 'FALSE') {
document.getElementById("errorMessage").innerHTML = "Name not found";
}
}).checklogin(Name,Password);
至:
google.script.run.withSuccessHandler(function({checkuser, dateTime}) {
if(checkuser == 'TRUE') {
document.getElementById("loginDisplay").style.display = "none";
document.getElementById("dataDisplay").style.display = "block";
document.getElementById("dateTime").innerHTML = dateTime.map(([a, b]) => `${b} || ${a}`).join("<br>");
} else if(checkuser == 'FALSE') {
document.getElementById("errorMessage").innerHTML = "Name not found";
}
}).checklogin(Name,Password);
还有
<span id="lastTime"></span> || <span id="lastDate"></span>
至:
<span id="dateTime"></span>
注意:
- 很遗憾，根据您的问题，我无法理解您实际目标的情况.因此，在上面修改的脚本中，我遵循了您当前的显示.因此，请根据您的实际目标进行修改.
这篇关于检查登录用户名和密码，然后从以前的每个条目中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
