Search match with for loop google app script

Tags: , , ,



I am tying to execute a function to check if an email address matches a cell value in my spreadsheet and make an array of any matches but I can’t seem to make it work.

I imagine it’s an issue with the loop, although it’s completing, I am not logging the array from logger.log.

Any help much appreciated!

`function searchEmail() {

var formOneSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
var lr = formOneSheet.getLastRow();
var formOneData = formOneSheet.getDataRange().getValues();
var col_to_search = 1;
var formOneRow = [];

for (var i = 0; i <= lr; i++) {
  if(formOneData[0][col_to_search] === "abc@gmail.com"){

  return i;
  formOneRow.append(i);
  
  Logger.log(formOneRow);
  }}}`

Answer

  • The code after return i; won’t be executed since the function is terminated when return is called.

  • Also in the for loop you don’t use i to iterate over the array.

  • Since the array index starts with 0, you don’t want to include the length of the array as the upper limit in the for loop.

  • You want to use push().

  • I assume you want to log the resulting list after the for loop.

Try this:

function searchEmail() {

var formOneSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
var lr = formOneSheet.getLastRow();
var formOneData = formOneSheet.getDataRange().getValues();
var col_to_search = 1;
var formOneRow = [];

for (var i = 0; i < lr; i++) {
  if(formOneData[i][col_to_search] === "abc@gmail.com"){
   formOneRow.push(formOneData[i][col_to_search]);
 }
 } 
   Logger.log(formOneRow)
 }


Source: stackoverflow