javascriptgoogle-apps-scriptgoogle-sheets-api

Resolving TypeError when trying to use findIndex


I'm trying to figure out why I'm getting the following error: TypeError: Cannot read properties of undefined (reading 'includes')

The error occurs at the line var nameIndex = dataFlatArr.findIndex(s => s.includes(lastName) && s.includes(firstName)); in the following script:

  var firstName = 'Steve';
  var lastName = 'Jobs';
  var dataArr =  getValues_({ spreadsheetId: fileID, sheetName: sht.getName(), start: 1, maxRow: lastRow, limit: 100000 });
  var columnsArr = [1,2,3,4];

  for(var i in columnsArr){ 
    if(foundColTrigger == 'No'){
      var dataFlatArr = dataArr.map(r => r[columnsArr[i]-1]);
      //Logger.log(dataFlatArr);
      var nameIndex = dataFlatArr.findIndex(s => s.includes(lastName) && s.includes(firstName));
      Logger.log(nameIndex);
      if(nameIndex != -1){
        foundColTrigger = 'Yes';
        indexColumn = columnsArr[i]-1;
        var fullDisplayName = dataArr[nameIndex][columnsArr[i]-1];
        Logger.log(fullDisplayName)
      }          
    }
  }

Function used to load dataArr:

//getValues in batches of 100K rows
function getValues_({ spreadsheetId, sheetName, start = 1, maxRow, limit = 100000 }) {
  return [...Array(Math.ceil(maxRow / limit))].flatMap((_) => {
    const last = start - 1 + limit;
    const range = `'${sheetName}'!A${start}:${last > maxRow ? maxRow : last}`;
    const temp = Sheets.Spreadsheets.Values.get(spreadsheetId, range).values;
    start += limit;
    return temp;
  });
}

And here is how my data that goes into dataArr looks:

Participants A Participants B Participants C Participants D
Jackson, Michael Wozniak, Steve James, Lebron Bonds, Barry
Blige, Mary J Gates, Bill Jordan, Michael J McGwire, Mark
Carey, Mariah Cook, Tim Curry, Stephen Sosa, Sammy
Jackson, Janet Jobs, Mark Durant, Kevin Judge, Aaron
Houston, Whitney Jobs, Steve Apple Abdul-Jabbar, Kareem Maris, Roger
Mercury, Freddie Musk, Elon Russell, Bill Ruth, Babe

This scripts runs through each column and checks to see if the firstName and lastName exist in the same cell in one of these columns. If so I return the index of the first instance and then ultimately I grab the full name as displayed by the data. In this case it should be Jobs, Steve Apple. indexColumn would be 1.

When I use getValues() to grab dataArr it works as expected but I want to continue using Sheets.Spreadsheets.Values.get(spreadsheetId, range).values; method because it is much faster to load. (~30 seconds faster)

Any thoughts on what could be happening? I'm open to alternatives if I can maintain the speed.


Solution

  • From

    var dataFlatArr = dataArr.map(r => r[columnsArr[i]-1]);
    

    to

    var dataFlatArr = dataArr.map(r => r[columnsArr[i]-1] ?? '');
    

    The above statement uses the nullish coalescing operator (??)...). When r[columnsArr[i]-1] is undefined, the expression returns '' instead of undefined.

    Or from

    var nameIndex = dataFlatArr.findIndex(s => s.includes(lastName) && s.includes(firstName));
    

    to

    var nameIndex = dataFlatArr.findIndex(s => s?.includes(lastName) && s?.includes(firstName));
    

    The above statement uses the optional chaining operator (?.)...). When s is undefined, the expression returns undefined instead of throwing an error.

    SpreadsheetApp.Range.getValues returns an empty string for all blank cells, but Sheets.Spreadsheets.Values.get(spreadsheet, range).values doesn't return values for the blank cells after the last ones with a non-blank value.