I have this particular code that already works to find a user's row using a inputted text, say input user1 has been found in row 1 and thus returns 1. However, this time I want to grab another columns value of the same row, the email column for example, and not just the row ID. How can I turn this code to return the user's email instead.

   var ss= SpreadsheetApp.openByUrl(url);
   var thisSheet = ss.getSheetByName("users");
   var tf = thisSheet.createTextFinder("user1");
   var userID = tf.findNext().getRowIndex();
   return userID;

I tried adding .getValues() after the .getRowIndex() but it results in an error.

CodePudding user response：

In your script, how about the following modification?

About I tried adding .getValues() after the .getRowIndex() but it results in an error., getRowIndex() doesn't return Range Object. By this, an error occurs. In this case, the Range Object can be retrieved from findNext().

From:

var userID = tf.findNext().getRowIndex();

To:

var userID = tf.findNext().getValues()[0];

• By this modification, for example, when the email address is put to column "B", you can retrieve it by userID[1].

Note:

• By the way, when the column of user1 is the column "A", I think that the following modification might be able to be used.

    var ss = SpreadsheetApp.openByUrl(url);
    var thisSheet = ss.getSheetByName("users");
    var tf = thisSheet.getRange("A2:A"   thisSheet.getLastRow()).createTextFinder("user1");
    var userID = tf.findNext().offset(0, 0, 1, thisSheet.getLastColumn()).getValues()[0];
  

Reference:

• findNext()