lundi 13 janvier 2020

Check if variable is in column, return contents of matching row - using loops

I'm having difficulties making this script work. I have a range of data in a sheet and I need to check if multiple string values are in this column. If they are, I need to get the entire row data for those strings. The string values are changing, I extract it from another spreadsheet into an array. The number of elements in the column are not equal to the number of elements in the string array.

var s = SpreadsheetApp.getActiveSpreadsheet();
var ss = s.getSheetByName("Sheet1");
var sheet1Data = ss.getDataRange(); // data range for sheet

// the column I need to check for values is column 1 - never changes

var headerD = array of strings extracted from another sheet
var sheetHeader = ss.getRange(1,1,ss.getLastRow(),1).getValues(); // values of 1st column on sheet

var sheetRow = []; // array for row data
for (var z = 0; z < headerD.length; z++) {
  for (var x = 0; x < sheetHeader.length; x++) {
    if (headerD[z].indexOf(sheetHeader)) {
    sheetRow[z] = need to get the row data from sheet here  
    }
  }
}

I'm really confused by nested loops, especially if the array lengths are different, so I'm not sure if the above code is a good start.

Edit:

var sheetRow = [];
for (var z = 0; z < headerD.length; z++) {
  for (var x = 0; x < sheetHeader.length; x++) {
    if (sheetHeader[x][0] == headerD[z]) {
      sheetRow[z] = ss.getRange(x,1,1,3).getValues();
    }
  }
}

If I write the code this way, I get the following error message: "Cannot read property '0' of undefined" for the row where the if statement is.

Aucun commentaire:

Enregistrer un commentaire