Find value in every cell of SpreadSheet

Question

Cannot make the code works, i want to check for every cell in spreadsheet for a value and paste the entire row that contains the value below.

var rowValue = spreadSheetData.getValues()
var findText = spreadSheetData.createTextFinder(usedEmail)
var bool

var mailFinder = findText.findAll().map(x => x.getA1Notation())
Logger.log(mailFinder)

if (choice == "Sí") {
  var i = mailFinder.indexOf(usedEmail)
  if (i != "") {
      rowValue[i][0] = new Date() 
      var rowValues = rowValue[i]
    bool = true
  }else{
    bool = false
  }
}

If i do like the code above, it works and gives me true or false depending of the result but the "i" var throws me "undefined" because i'm not using the rowValue variable. In the other hand if i add a "for" iteration like:

    for(i = 0; i < rowValue.length; i++){
          rowValue[i][0] = new Date()
          var rowValues = rowValue[i]
    }

It copies the last row and that's not the approach i want because i want to copy the row where the value is.

Example sheets:

| header1  | header2                   |
| -------- | ------------------------- |
| 231232132| valueiwanttofind@gmail.com|
| asdasdas | row                       |

| header1                   | header2                   |
| ------------------------- | ------------------------- |
| 231232132                 | row                       |
| valueiwanttofind@gmail.com| another row               |

the valueiwanttofind@gmail.com can be in any cell. I tried another approach but without luck...

 for(var i = 1; i < rowValue.length; i++) {
      for(var n = 1; n < rowValue[i].length; n++) {
        var findText = rowValue[i]
        Logger.log(findText)
        if (choice == "Sí") {
          if (findText.indexOf(usedEmail)) {
              rowValue[i][0] = new Date() 
              var rowValues = rowValue[i]
            bool = true
          }else{
            bool = false
          }
        }
      }
    }

UPDATE:

Thanks for your help, i managed to implement what i needed, i will explain briefly so if anyone comes with the same problem

function getItems(email,usedEmail, choice, name, gender){ //This functions brings vars as parameters from main function who map every row
  var spreadSheet = SpreadsheetApp.openById(form.getDestinationId())
  var spreadSheetData = spreadSheet.getDataRange()
  var rowValue = spreadSheetData.getValues()
  var bool

    rowResult = rowValue.find(r => r.includes(usedEmail)) //I didn't know how to use find, instead i used findAll with textfinder

    if (choice == "Sí") {
      if (rowResult) {
          var rowValues = rowResult //pass rowValues with the result above
        bool = true
      }else{
        bool = false
      }
    }
    
    for(i = 0; i < rowValue.length; i++){
        rowValue[i][0] = new Date() //I used just in case to put a the current date when the form is sent to not copy that value(overwrite).
    }

  Logger.log(usedEmail)
  Logger.log(bool)
  if (bool == true){
    setItems(spreadSheet, rowValues)
    //RespuestaAutomatica(name, email, gender) is another function i have
    Logger.log("Inscripción válida")
  }else{
    Logger.log("Inscripción fallida, email no registrado")
  }

}

function setItems(spreadSheet, rowValues){ //the function created to append the rows at the next one.
    spreadSheet.appendRow(rowValues)
}

Thanks for your help!

Answer 1

Something like this?

function myFunction() {
  var email = 'valueiwanttofind@gmail.com';     // the wanted email
  var sheet = SpreadsheetApp.getActiveSheet();  // the current sheet
  var data = sheet.getDataRange().getValues();  // all the data from the sheet
  var row = data.find(r => r.includes(email));  // the row with the email or undefined
  if (row) sheet.appendRow(row);                // append the row at the end of the sheet
}

It copies (appends) the first row that contains the email at the end of the sheet.

(If you want the index of the row, you can use findIndex() instead of find())

If you want to append all the rows that contain the email you can use filter() instead of find():

function myFunction() {
  var email = 'valueiwanttofind@gmail.com';       // the wanted email
  var sheet = SpreadsheetApp.getActiveSheet();    // the current sheet
  var data = sheet.getDataRange().getValues();    // all the data from the sheet
  var rows = data.filter(r => r.includes(email)); // the rows with the email

  // append the array of rows at the end of the sheet
  if (rows) rows.forEach(row => {
    sheet.appendRow(row);     // <--- it's ok if there are a few rows only 
    SpreadsheetApp.flush();
  });
}

(The script should be improved if you have many rows with the email).

Answer 2

SUGGESTION:

Although it is still unclear what's the specific purpose this line of code below, I'm hunching that your main goal is to get the row numbers where matches are found on your sheet. But feel free to let us know if we misunderstood your post.

Line of code that is unclear:

  rowValue[i][0] = new Date() 
  var rowValues = rowValue[i] 

Why are you assigning a date value on an array data in rowValue[i][0]? What's the specific purpose of this?

Perhaps you can try this tweaked script:

//Initialized these lines below for the code to work
var spreadSheetData = SpreadsheetApp.getActiveSpreadsheet();
var usedEmail = "valueiwanttofind@gmail.com";
var choice = "Sí";

function testFunction() {
  var rowValue = spreadSheetData.getDataRange().getValues();
  var findText = spreadSheetData.createTextFinder(usedEmail);
  var bool;

  var mailFinder = findText.findAll().map(x => x.getA1Notation().toString())

  if (choice == "Sí" & mailFinder.length != 0) {//Make sure mailfinder is not null
    var rowValues = mailFinder;
    rowValues = rowValues.map(function(x){ return x.replace(/[^abc]/,'') }); //Get the row numbers of the macthed rows that are orginally in A1Notation format
    Logger.log("Found \""+""+usedEmail+"\" on Row #:\n" + rowValues);
    bool = true;
  }else{// If mailFinder has no data found
    bool =  false
  }
  Logger.log(bool);
}

Sample Result:

Test Sheet that I have used: