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.
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.