I have a problem with removing empty arrays/objects from a multidimensional array for my search app in google sheets. I am using getLastRow so I thought I will avoid this problem but unfortunately it is not.

My array looks like this:

[[1.39080000912E11, RSSMA004025, ﾎﾞﾀﾝｷｬｯﾌﾟ M4X25, 4.0, 4.2, , , ], [1.39080000912E11, RWJAA058068, FTB-268, 1.0, 486.0, 486.0, , ], [, , , , , , , ], [, , , , , , , ]]

And I am looking for something that will delete empty arrays from the end of the array so it should be like this:

[[1.39080000912E11, RSSMA004025, ﾎﾞﾀﾝｷｬｯﾌﾟ M4X25, 4.0, 4.2, , , ], [1.39080000912E11, RWJAA058068, FTB-268, 1.0, 486.0, 486.0, , ]]

It is just a part of the whole array but I hope you will understand what I am looking for.

My code so far:

function getDataForSearch(){
    
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("WP");
  
  var test1 = ws.getRange(4, 6, ws.getLastRow(),8).getValues();

const arrFiltered = test1.filter(function(x){
  return (x !== (undefined || null || ''));
});

 Logger.log(arrFiltered);

}

I have a problem with removing empty arrays/objects from a multidimensional array for my search app in google sheets. I am using getLastRow so I thought I will avoid this problem but unfortunately it is not.

My array looks like this:

[[1.39080000912E11, RSSMA004025, ﾎﾞﾀﾝｷｬｯﾌﾟ M4X25, 4.0, 4.2, , , ], [1.39080000912E11, RWJAA058068, FTB-268, 1.0, 486.0, 486.0, , ], [, , , , , , , ], [, , , , , , , ]]

And I am looking for something that will delete empty arrays from the end of the array so it should be like this:

[[1.39080000912E11, RSSMA004025, ﾎﾞﾀﾝｷｬｯﾌﾟ M4X25, 4.0, 4.2, , , ], [1.39080000912E11, RWJAA058068, FTB-268, 1.0, 486.0, 486.0, , ]]

It is just a part of the whole array but I hope you will understand what I am looking for.

My code so far:

function getDataForSearch(){
    
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("WP");
  
  var test1 = ws.getRange(4, 6, ws.getLastRow(),8).getValues();

const arrFiltered = test1.filter(function(x){
  return (x !== (undefined || null || ''));
});

 Logger.log(arrFiltered);

}

• javascript
• google-apps-script
• google-sheets

• in your function const arrFiltered = test1.filter(function(x){ return (x !== (undefined || null || '')); });, x is an array. not an object. – LogiStack Commented Jan 19, 2021 at 2:30

6 Answers 6

Your function should look like

function getDataForSearch() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName("WP");

  var test1 = ws.getRange(4, 6, ws.getLastRow(), 8).getValues();

  const arrFiltered = test1.filter(function (x) { /* here, x is an array, not an object */
    return !(x.every(element => element === (undefined || null || '')))
  });

  Logger.log(arrFiltered);

}

I am using getLastRow so I thought I will avoid this problem but unfortunately it is not.

getRange syntax is

getRange(row, column, numRows, numColumns)

The script is using

ws.getRange(4, 6, ws.getLastRow(), 8).getValues();

The third parameter is numRows- the number of rows. If the last row is 10, You're getting the range from row 4 to row 14(4+10). To end at row 10, number of rows should be 7. The correct range should be therefore be

ws.getRange(1 + 3, 6, ws.getLastRow() - 3, 8).getValues();

Another possibility for such empty rows would be the traditional use of array formulas. See ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?

You want to use filter() and some()

var arr = [[1.39080000912E11, 'RSSMA004025', 'ﾎﾞﾀﾝｷｬｯﾌﾟ M4X25', 4.0, 4.2, , , ], [1.39080000912E11, 'RWJAA058068', 'FTB-268', 1.0, 486.0, 486.0, , ], [, , , , , , , ], [, , , , , , , ]];

// Will fail for falsly values, but works in this case
var updated1 = arr.filter(function (a) {
  return a.some(Boolean);
});

// var updated1 = arr.filter(a => a.some(Boolean));
console.log(updated1);

// check for undefined
var updated2 = arr.filter(function (a) {
  return a.some(function (val) {
    return val !== undefined;
  });;
});

// var updated2 = arr.filter(a => a.some(val => val !== undefined));
console.log(updated2);

//If it can be any of the values
var empty = [undefined, null, ''];
var updated3 = arr.filter(function (a) {
  return a.some(function (val) {
    return !empty.includes(val);
  });
});

// var updated3 = arr.filter(a => a.some(val => !empty.includes(val)));
console.log(updated3);

Try something like this perhaps. You want to only remove the arrays where all of the array elements are undefined.

var funnyArrays = [['a', 'b', 'c'],  [,,,], ['d','e','f'], [,,,]];

var fullArrays = funnyArrays.filter(a => !a.every(ax=> a == undefined));

you can join the elements in the array and then check their length for filtering

let test1 = [[1.39080000912E11, 'RSSMA004025', 'uiuiu M4X25', 4.0, 4.2, , , ],[1.39080000912E11, 'RWJAA058068;', 'FTB - 268', 1.0, 486.0, 486.0, , ],[, , , , , , , ],[, , , , , , , ]]


const arrFiltered = test1.filter(function(x) {
  return (x.join('').length !== 0);
});

console.log('new array',arrFiltered);

U can try that.

const notEmptyArrayOrObject = (arr)=> {

  if(typeof arr !== 'object'){ return true }

  if(!Array.isArray(arr){
     arr = Object.values(arr)
  }

  return arr.filter(item=>item === undefined).length !== arr.length
}

const filterEmptyFromArray = (array)=>array.filter(notEmptyArrayOrObject)