GAS Filter Criteria to Filter records in an Array that have NON-Blank (text) cells in selected singl-CodePudding

I'm trying to find a way in Apps Script, using an array method (NOT using a table), to filter records from a large array that have text data in selected fields.

The records in the newly filtered array would contain ONLY those that have any (text) data in the specified field(s).

The filtered data array would then be copied into a 'Sheets' table for further use.

Finding records with blank fields using "" as the criteria, or specific data, such as "Yes", works well.

My work-around involves a 'for' loop to clear records from the table into which the array has been copied. However, this takes more time than than the 'filter' records method.

The example shows the method to filter only records that have a 'Blank' cell in the specified field. I have tried so many possible criteria that I've lost track of every option I have tried, but here are some of the criteria I've tried to find records that have non-blank cells: "<>" !="" !"" "!Null" ">0" "is not null" "!Empty".

var Ss = SpreadsheetApp.getActiveSpreadsheet();
var DataSheet = Ss.getSheetByName("VolunteerListTbl");//Source Table of data
var LstRowNum = DataSheet.getLastRow();
var LstColNum = DataSheet.getLastColumn();
  
// "DataSheetRangeValues" is an array of entire dataset
var DataSheetRangeValues = DataSheet.getRange(3,1, LstRowNum , LstColNum).getValues();

var FilterCriteria = ""; //CASE: NO Coordinator assigned
var FilteredData = DataSheetRangeValues.filter(function(e){return e[1]===FilterCriteria});
var NewSheetName = "CustomSearch_Tbl"; //Each search gets a different 'Sheet' name
var C_SrchResSheet = Ss.getSheetByName(NewSheetName);
  
//Copy data from 'FilteredData' array to a new table 'NewSheetName'
  
 C_SrchResSheet.getRange(3,1,FilteredData.length,LstColNum).setValues(FilteredData);
     
  RecordsFound = FilteredData.length;

CodePudding user response：

Try this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("VolunteerListTbl");
  const lr = sh.getLastRow();
  const lc = sh.getLastColumn();
  const vs = sh.getRange(3, 1, lr - 2, lc).getValues();//note the numrows param
  const fvs = vs.filter(e => e[1] != "");//this does not seem consistent with the description in your question
  const nsh = ss.getSheetByName("CustomSearch_Tbl");
  nsh.getRange(3, 1, fvs.length, fvs[0].length).setValues(fvs);
}