How do I convert this textFinder to only return the email it finds in a specific column?

I currently have a Spreadsheet table of employee details. The code below works fine as long as the email is unique (only one occurrence in the table’s column E). However, other user emails are also found in the supervisor/manager columns email, which results into the code below referencing that first email occurrence and thus messing up the userFullName output. What I wanted to do is to search only a specific column, column E, for the userEmail.

   var userEmail = Session.getActiveUser().getEmail();
   var url = '';
   var ss= SpreadsheetApp.openByUrl(url);
   var thisSheet = ss.getSheetByName("employeeList");
   var tf = thisSheet.getRange("A1:Z" + thisSheet.getLastRow()).createTextFinder(userEmail);
   var userFullName = tf.findNext().offset(0, -4, 1, thisSheet.getLastColumn()).getValues()[0][1];
   console.log(userFullName);

>Solution :

From What I wanted to do is to search only a specific column, column E, for the userEmail., if you want to search a value of userEmail from only column "E", how about the following modification?

From:

var tf = thisSheet.getRange("A1:Z" + thisSheet.getLastRow()).createTextFinder(userEmail);

To:

var tf = thisSheet.getRange("E1:E" + thisSheet.getLastRow()).createTextFinder(userEmail);

  • The method of createTextFinder of Class Range can search the value from the range.

  • From your scriptm, it supposes that there is no header row. If you have a header row, please modify E1:E to E2:E.

Leave a Reply