I have only started using Apps Script for a few months and usually I can figure out why it's giving me error messages. But this latest one has me completely stumped and I cannot find anyone who has asked anything similar on here.
I have very simple code which retrieves data from a spreadsheet, loops through it to remove some values before pasting it back into another sheet. However, the loop keeps throwing up the error "TypeError: Cannot read property '0' of undefined".
After some digging I think I found the issue - the array I have extracted from the sheet is 3 rows short of what it should be.
So var refArray = sheet.getRange("F5:F").getValues(); has the length 65 even though there are 68 rows in the sheet.
But what really puzzles me is that when I run var len = refArray.length it returns 68! So, for lack of better lingo, this confuses my loop as it cannot find the last 3 items and I get the above error (at least that's what I think).
I have tried extracting the data using
var refArray = sheet.getRange(1,6,sheet.getMaxRows(),1) but this also just skips over the last 3 rows. There is no particular reason it would skip these rows, and sometimes it only skips 2, so I don't think the issue lies within my data...
I can share a mock-up of my sheet and script if it helps, I have no clue why it is doing this or how to fix it!
Thanks already everyone.
EDIT
I may have been barking up the wrong tree as I just tried to replicate the issue in another sheet and the array seemed to be extracted correctly, but then the error popped up again when it started to loop. As I said I'm just dabbling in Apps Script so my loops are very basic. I'm just gonna add the full sequence below and see if someone can spot the error:
var s = SpreadsheetApp.getActive();
var sheet = s.getSheetByName("Sheet1");
var sheet2 = s.getSheetByName("Sheet2");
function test() {
// get archive Array
var archiveArray = sheet2.getRange("A3:A").getValues();
console.log("Array Check: "+archiveArray[0][0]);
// get ref Array to loop through
var refArray = sheet.getRange("F2:F").getValues();
console.log("Array Check: "+refArray[0][0]); // length 29
var len = refArray.length;
console.log(len); // length 29
for ( var l = len - 1; l > 0; l--){
for( var a = 0; a < archiveArray.length; a++) {
if ( archiveArray[a][0] == refArray[l][0] ) {
refArray.splice(l,1); // remove all duplicates
}
}
}
console.log("New array:"+refArray);
I'd propose to use array.includes() method and create a new array instead of change existed one:
With a loop:
var archiveArray = ['a', 'b', 'c', 'd'];
var refArray = ['a', 'b', 'e', 'f'];
var new_refArray = [];
for (var val of refArray) {
if (!archiveArray.includes(val)) new_refArray.push(val)
}
console.log(new_refArray); // output [ 'e', 'f' ]Or with a filter:
var archiveArray = ['a', 'b', 'c', 'd'];
var refArray = ['a', 'b', 'e', 'f'];
var new_refArray = refArray.filter(val => !archiveArray.includes(val));
console.log(new_refArray); // output [ 'e', 'f' ]The final code can be like this:
var s = SpreadsheetApp.getActive();
var sheet = s.getSheetByName("Sheet1");
var sheet2 = s.getSheetByName("Sheet2");
function test() {
var archiveArray = sheet2.getRange("A3:A").getValues()
.flat().filter(String); // convert 2D into 1D and remove empty elements
var refArray = sheet.getRange("F2:F").getValues()
.flat().filter(String); // convert 2D into 1D and remove empty elements
var new_refArray = refArray.filter(val => !archiveArray.includes(val));
console.log(new_refArray);
}
Note: if you want to paste the result array back on the sheet it should be a 2D array. You can convert the flat array into 2D array this way:
new_refArray = new_refArray.map(x => [x]); // [a,b,c] --> [[a],[b],[c]]