Filtering 2D Array with a Variable Filter

Question

I am trying to break out an array into several different arrays to paste information across several sheets. I am having trouble filtering my main data array (bigArray) dynamically with a separate array (sheetCheck). If I type a static filter i.e. "02A" the filter works. How can I modify my filter to accept variable information?

function testArray (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //Get full array of Budget Pricing Breakdown Sheet
  var originalArray = ss.getRangeByName('XxTestRange').getValues();
  //Delete unused columns from array. Only Leaves sheetNumber, description, qty, UM, unitCost
  var newArray = originalArray.map(function(row){return[row[0],row[5],row[6],row[7],row[10]];});
  //Delete unused rows that have a value of X in the sheetNumber
  var bigArray = newArray.filter(function(item){return item[0] != "X";});

  //Get array of Applicable Tabs
  var originalSheetsArray = ss.getRangeByName('XxTestRange2').getValues();
  //Remove unused column
  var newSheetsArray = originalSheetsArray.map(function(row){return[row[0],row[2]];});
  //Delete not applicable rows
  var sheetCheckBad = newSheetsArray.filter(function(item){return item[1] != "Not Applicable"});
  //Separate just appicable trades
  var sheetCheck = sheetCheckBad.map(function(row){return[row[0]];});

  for (var i=0; i<sheetCheck.length;i++){
    var sheetNumber = sheetCheck[i];
    //Logger.log(sheetNumber)
    var pasteArray = bigArray.filter(function(item, sheetNumber){return item[0] == sheetNumber})
    //var pasteArray = bigArray.filter(filterSheetLogic)
    Logger.log(sheetNumber);
    Logger.log(pasteArray);
  }
}

//Pull individual Arrays based on sheet numbers
var filterSheetLogic = function(item, sheetNumber){
  if (item[0] == sheetNumber){
    return true;
  } else {
    return false;
  }
}

In order to correctly understand about your situation, can you provide the sample values for input and output you want? — Tanaike
– Tanaike, Commented Apr 12, 2019 at 23:08
I believe the OP is asking how to be able to include the variable sheetnumber as a function parameter var pasteArray = bigArray.filter(function(item, sheetNumber){return item[0] == sheetNumber}) because paste array is currently empty on all iterations. If one removes the variable sheetNumber the function works correctly. I just used 11 columns of numbers. — Cooper
– Cooper, Commented Apr 13, 2019 at 2:40
Here's a link to a sample spreadsheet. I am using the array filters to break the data from Budget Pricing Breakdown sheet into separate arrays that can be pasted into the other sheets titled 02A, 03A,..,04A. The full version of the sheet copied hundreds of rows from budget pricing breakdown and distributing them to over 100 sheets. link — Chris MMgr
– Chris MMgr, Commented Apr 13, 2019 at 2:47
@Cooper you are absolutely correct. Deleting the sheetNumber variable fixed my issues. Thank you for the help! — Chris MMgr
– Chris MMgr, Commented Apr 15, 2019 at 14:53

oshliaer · Accepted Answer · 2019-04-13 11:15:18Z

1

You can't call that

 bigArray.filter(function(item, sheetNumber) ...

Because the callback of the filter get its own arguments. In this case sheetNumber always equals the array's index 0, 1, 2 etc.

My case is building a fabric to generate a valid callback. For an example

/**
 * The big array filter's builder. It's a fabric
 * @param {string} sheetNumber
 * @returns {object} The filter's callback
 */
var filterBuilder_ = function(sheetNumber) {
  return function(item, _, __) {
    return item[0] == sheetNumber;
  };
};

After this change your main loop to

for (var i = 0; i < sheetCheck.length; i++) {
  var sheetNumber = sheetCheck[i][0];
  var bigArrayfilter = filterBuilder_(sheetNumber);
  var pasteArray = bigArray.filter(bigArrayfilter);
  Logger.log(sheetNumber);
  Logger.log(pasteArray);
}

I apologize if I do not understand the task correctly.

edited Apr 13, 2019 at 11:15

answered Apr 13, 2019 at 10:47

oshliaer

4,9875 gold badges35 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Filtering 2D Array with a Variable Filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related