Retrieve data range with two column filters

Question

I have sheet Transactions with the main data, and sheet Report (Active) with the name of the person and into which the metrics that will be calculated from the Transaction main data will be fed. First step is to retrieve the data for that person (cell B6 in the Report) with another condition based on a different column.

var report = SpreadsheetApp.getActiveSpreadsheet();
var trsheet = report.getSheetByName("Transactions");
var areport = SpreadsheetApp.getActiveSheet();
var agent = areport.getRange('B6').getValue();

var trdata = trsheet.getDataRange().getValues();
var tdata = trdata.filter(function (row) {
    return row[29] == agent && row[5] == 'Closed' ;
  });

This produces no errors, but an empty array for tdata. How do I correctly add the second condition to the filter function?

Welcome to Stack Overflow. Please add some sample values for Report!B6 for data to be filtered and the expected results. — Rubén
Thank you Rubén! Marios below answered the question. It was an error in the column number. — Mike B.

soMario soMario · Accepted Answer · 2020-08-13T17:39:10

The code looks fine. It might not work because of the two following reasons:

1. You might be comparing different data types. For example agent might be a type of string in the Transactions sheet and integer in your active sheet.

It would be a good idea to try to use getDisplayValues() instead:

var report = SpreadsheetApp.getActiveSpreadsheet();
var trsheet = report.getSheetByName("Transactions");
var areport = SpreadsheetApp.getActiveSheet();
var agent = areport.getRange('B6').getDisplayValue();

var trdata = trsheet.getDataRange().getDisplayValues();
var tdata = trdata.filter(function (row) {
    return row[29] === agent && row[5] === 'Closed' ;
  });

2. Make also sure that in the sheet Transactions:

column F row[5] contains the keyword you are looking for : 'Closed',
column AD row[29] contains the value of the variable you are looking for : var agent.

Retrieve data range with two column filters

1 Answers