Home / Questions / 238667
Accepted
Iván Montero
Asked: 2020-02-18 23:00:36 +0800 CST 2020-02-18 23:00:36 +0800 CST

How to filter a 2D Array using two exact match criteria?

  • 772

This is the third part of a question that started with trying to filter a spreadsheet using a formula:

Formula Filtering

Here I realized that what I wanted to do could not be done using the spreadsheet formulas, so I decided to try it using scripts.

Scripted Filtering

To perform this filtering with scripts, I tried to use an external library called 2D Arrays Library , with which I was able to filter the data, but not in the way I wanted.

My intention is to filter the data of a 2D Array in which I can use 2 exact criteria, since with the library I have only managed to filter the cells that contain these criteria.

For this, I leave at your disposal a test spreadsheet that contains a list with the data to be filtered, a sheet with the current filter and another sheet with the expected result, in addition to containing the script and the library that I am using.

test sheet

javascript

2 Answers

  1. The following shows how to use the method filterto Arrayfilter by exact match of two conditions, it is assumed that the area corresponding to the filtered data was previously emptied.

    function filtrarDatos2Criterios() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var address = 'Llista!A6:AX1000' // Referencia de los datos origen
  var data = spreadsheet.getRange(address).getValues();

  // Primer criterio
  var columnIndex1 = 5
  var value1 = 'Bomber-conductor'; // Valor a incluir


  // Segundo criterio
  var columnIndex2 = 6
  var value2 = 'A'; // Valor a incluir

  // Filtrar por coincidencia exacta usando dos criterios
  var filteredData = data.filter(function (dataRow) {
    return dataRow[columnIndex1] === value1 && dataRow[columnIndex2] === value2;
  });


  // Determinar número de filas y columnas requeridos para desplegar el resultado
  var rows = filteredData.length; // número de filas
  var columns = filteredData[0].length; // número de columnas

  // Pasar los datos a la hoja destino
  var target = 'Asignacion';
  spreadsheet.getSheetByName(target).getRange(9,1,rows,columns).setValues(filteredData);  
}

    Related

    • 1
  2. Best Answer

    I leave the complete solution of the script:

    function onEdit(e){

  var range = e.range;
  var colIndex = range.getColumnIndex(); //Same as getColumn()
  var rowIndex = range.getRowIndex(); //Same as getRow()

  //Donde utilizar el filtro
  var rangeLlista = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Llista").getRange("A6:AX500").getValues();

  //Lo que queremos borrar
  var rangeBorrar = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Asignacion");

  //Asignacion de las celdas que tiene que filtrar.
  var RangeAsignacion = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Asignacion").getRange("A2:F2");

  //Los criterios a filtrar
  var Asignacion1 = RangeAsignacion.getCell(1,2).getValue().toString();
  var Asignacion2 = RangeAsignacion.getCell(1,5).getValue().toString();

  //Columna de los criterios a filtrar, la culumna A = 0
  var columnIndex1 = 5;
  var columnIndex2 = 6;

  //Mira si se modifican las celas que nosotros queremos
  if(range.getSheet().getName() === 'Asignacion'){

    var watchRange1 = {
      top : 2, // start row
      bottom : 2, // end row
      left : 1, // start col
      right : 6, // end col
    };

    if(colIndex >= watchRange1.left && colIndex <= watchRange1.right && rowIndex >= watchRange1.top && rowIndex <= watchRange1.bottom && e.Value != 0){

      //Borrado del contenido anterior
      var start = 9;
      var end = rangeBorrar.getMaxRows();

      rangeBorrar.deleteRows(start, end - start);

      //Se realiza el filtrado
      var filteredData = rangeLlista.filter(function (dataRow){

        return dataRow[columnIndex1] === Asignacion1 && dataRow[columnIndex2] === Asignacion2;

      });

      var rows = filteredData.length;
      var columns = filteredData[0].length;
      SpreadsheetApp.getActiveSheet().getRange(9,1,rows,columns).setValues(filteredData);

    };

  };

};

    The only thing I would like to modify is the method to delete the rows, since the one I use also deletes the formatting of the cells.

    • 0