How to filter an array using the filtered index of another array?

Question

My current code grabs the values of the same range of data for Values, Notes, and Background Colors into 3 separate arrays. Then it filters the Values array by the first item in the nested array, returning only the items that matches the defined statArray array item. Now I want the other two array filters (Notes, Background) to be filtered by the indexes of the returned items from the filtered Value array. That way when I set the Backgrounds and Notes arrays they overlap correctly onto the values.

function moveAllNew() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = SpreadsheetApp.getActiveSheet();
  var activeSheetName = activeSheet.getName();
  var lastRow = activeSheet.getLastRow();
  var lastColumn = activeSheet.getLastColumn();
  var statArray = ["Captures Sent 📩","QA In Progress 👌","Bagging in Progress 01 🎒","Bagging in Progress 02 🎒","Bag Sent 📩","Bag Rejected ❌","Bag Accepted ✅","Captured 📷",""];
  var rowHeaderCount = 3;
  var rowStart = 4;
  var columnFormulaB = 2;
  var columnFormulaS = 19;
  var columnFormulaAC = 29;
// gets the range of the current sheet
  var activeRange = activeSheet.getRange(rowStart,1,lastColumn-rowHeaderCount,lastColumn);
  var activeRangeFormulaB = activeSheet.getRange(rowStart,columnFormulaB,lastRow-rowHeaderCount);
  var activeRangeFormulaS = activeSheet.getRange(rowStart,columnFormulaS,lastRow-rowHeaderCount);
  var activeRangeFormulaAC = activeSheet.getRange(rowStart,columnFormulaAC,lastRow-rowHeaderCount);
// creates array of the active sheet (values, notes, backgrounds, and formula columns (B, S, AC))
  var activeRangeValues = activeRange.getValues();
  var activeRangeNotes = activeRange.getNotes();
  var activeRangeBackgrounds = activeRange.getBackgrounds();
  var activeRangeFormulaB = activeRangeFormulaB.getFormulasR1C1();
  var activeRangeFormulaS = activeRangeFormulaS.getFormulasR1C1();
  var activeRangeFormulaAC = activeRangeFormulaAC.getFormulasR1C1();
// filtered QA arrays
  var qaSheet = ss.getSheetByName("QA 👌");
  var qaRangeValues = activeRangeValues.filter(function(item){return item[0] === statArray[0] || item[0] === statArray[1];});
  var qaRangeNotes = activeRangeNotes.filter(function(item){return item[0] === statArray[0] || item[0] === statArray[1];});
  var qaRangeBackgrounds = activeRangeBackgrounds.filter(function(item){return item[0] === statArray[0] || item[0] === statArray[1];});
  Logger.log(qaRangeValues);
  Logger.log(qaRangeNotes);
  Logger.log(qaRangeBackgrounds);

Currently the qaRangeValues and qaRangeNotes arrays would come back empty because the statArray item is never a color nor a note value.

tehhowch · Accepted Answer · 2019-07-19 20:37:59Z

2

Probably you meant to use lastRow instead of lastColumn when determining activeRange.

Anyway, you need to perform some operation that allows you to access the current indices, and while you have the indices, also operate on your other equal-sized arrays.

One example:

var qaData = {values: [], notes: [], bgs: [] };
activeRangeValues.forEach(function (row, idx) {
  if (row[i] === statArray[0] || row[i] === statArray[1]) {
    qaData.values.push(row);
    qaData.notes.push(activeRangeNotes[idx]);
    ...
});
// Use the qaData object

answered Jul 19, 2019 at 20:37

tehhowch

9,9124 gold badges27 silver badges47 bronze badges

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

1 Comment

Shane Sullivan Over a year ago

Thanks for catching my typo with lastRow and lastColumn. The example is very helpful. I'm still learning the basics and you've opened my eyes up to the usefulness of object literal syntax.

Collectives™ on Stack Overflow

How to filter an array using the filtered index of another array?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related