Check for login username and password, and then bring data from every previous entry

user_14901440 0
Viewed 172

Similar to a question I had asked previously (Need to check for login username and password, and then bring data from previous entries).

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.

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>

And here is a sheet where you can work from or copy the information. https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0

google-apps-script google-sheets html javascript
1 Answers

I believe your goal as follows.

  • When Name and Password are given to the function of checklogin, you want to retrieve all matched values.

Modification points:

  • 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();

  • And, in this case, offset(0, 1, 1, 2) is used, 2 columns can be retrieved by one call.

  • 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.

Modified script:

Google Apps Script side:

Please modify the function checklogin as follows.

From: 
var last = ranges.pop();
  obj.lastDate = last.offset(0, 1, 1, 1).getDisplayValue();
  obj.lastTime = last.offset(0, 2, 1, 1).getDisplayValue();
To: 
obj.dateTime = ranges.flatMap(r => r.offset(0, 1, 1, 2).getDisplayValues());

HTML&Javascript side.

From: 
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);
To: 
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);

And

From: 
<span id="lastTime"></span> || <span id="lastDate"></span>
To: 
<span id="dateTime"></span>

Note:

  • From your question, unfortunately, I cannot understand about the situation of your actual goal. So in above modified script, I followed your current display. So please modify this for your actual goal.

References:

user_71086530
Related