Need to Query Google Sheet for specific data from Google Apps Script

Question

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. My boss has also asked me if there is a way that employees can see what their last 3 registrations were, in case they may have forgotten to record an entry or exit time. So what I need to do is look for each employee's specific name/id in the sheet where the data is being received, and print that data on the web app under a "Last Registrations" section.

Here is a minimal reproducible example, that just asks for a name, looks for that name in a data base and then allows you to send your submission while bringing the timestamp of the submission. All i would need to see is how to look for the person's name in the data base of "ReceivedData" (which comes from earlier registrations on the the web app) and how to bring the timestamp from that submission to the web app under the "Last Registrations section". Here is also a spreadsheet with some data where you can work. (https://docs.google.com/spreadsheets/d/1CJoPuq3sHE5L31GwlvS4Zygm1sL3M0HGC7MgW3rCq3g/edit#gid=0)

Sorry for the long description and thank you.

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) {
  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 getLastRow =  webAppSheet2.getLastRow();
  var found_record = '';
  for(var i = 1; i <= getLastRow; i++)
  {
   if(webAppSheet2.getRange(i, 1).getValue() == Name)
   {
     found_record = 'TRUE';
   }    
  }
  if(found_record == '')
  {
    found_record = 'FALSE'; 
  }
  
  return found_record;
  
}



  
    
    



     
    Name

    
    
    
    

 
 

    
    
    
    Last Registrations

Tanaike · Accepted Answer

I believe your goal as follows.

From So what I need to do is look for each employee's specific name/id in the sheet where the data is being received, and print that data on the web app under a "Last Registrations" section. and All i would need to see is how to look for the person's name in the data base of "ReceivedData" (which comes from earlier registrations on the the web app) and how to bring the timestamp from that submission to the web app under the "Last Registrations section"., I understood your question as follows.
1. You want to search the inputted value from ReceivedData sheet in the Spreadsheet.
2. You want to put the last timestamp retrieved by searching the inputted value from the sheet to below of "Last Registrations section".

Modification points:

From your script, I thought that when the script for searching the inputted value is included in the function checkLogin in GAS side, it might be suitable.
And, in order to show the last timestamp, is added to HTML and LoginUser in Javascript is modified.

When above points are reflected to your script, it becomes as follows.

Modified script:

Google Apps Script side:

Please modify checkLogin as follows.

function checkLogin(Name) {
  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 check = webAppSheet2.getRange(2, 1, webAppSheet2.getLastRow(), 1).createTextFinder(Name).matchEntireCell(true).findNext();
  var obj = {check: check ? '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;
}

HTML & Javascript side:

Please modify LoginUser as follows.

function LoginUser() {
  var Name = document.getElementById("Name").value;
  google.script.run.withSuccessHandler(function({check, lastTimestamp}) {
    if(check == 'TRUE') {
      document.getElementById("loginDisplay").style.display = "none";
      document.getElementById("dataDisplay").style.display = "block";
      document.getElementById("lastTimestamp").innerHTML = lastTimestamp;
    } else if(check == 'FALSE') {
      document.getElementById("errorMessage").innerHTML = "Name not found";
    }
  }).checkLogin(Name);
}

And, please add HTML as follows.

From:

Last Registrations

To:

Last Registrations

Note:

In this modified script, your shared Spreadsheet and script are used. So when the structure of Spreadsheet is changed, the script might not be able to be used. Please be careful this.

Reference:

Class TextFinder