Google Script GetRange with Filter based on values in a column AND select only certain columns

Question

I have a dataset of 35 columns and 300 rows. I want to get the range that contains rows only for certain values in column 30 (names). The name for which to filter the data is based on the report file cell B6 in the report sheet that is active. So far I tried this:

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

var criteria = SpreadsheetApp.newFilterCriteria().whenTextEqualTo(agent).build();
var trange = tsheet.getRange().createFilter().setColumnFilterCriteria(30, criteria); // ERROR
var tdata = trange.getValues();

I receive an error Exception: The parameters () don't match the method signature for SpreadsheetApp.Sheet.getRange.

The second part, I only want to get several columns, 5,6,7, 13, 15. I can't create another filter with the Spreadsheet app, so is the only way to make an array and filter out the needed data from there? I'm just trying to think ahead and reduce the amount of calculations.

Answer 1

Try with filter():

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

var data = tsheet.getRange('A1:AI300').getValues();
var tdata = data.filter(function (row) {
    return row[29] == agent && row[5] == 'Closed' ; // starts from 0, column A is 0.
  }); 

To select particular columns from tdata do:

var cr_data = getCols(tdata,[5,6,7, 13, 15]);

where getCols() is defined as follows:

function getCols(arr,cols) {
  return arr.map(row =>
    row.filter((_,i) => cols.includes(++i)))
}

and finally you can copy cr_data to a particular place/sheet like that:

sheet.getRange(1,1,cr_data.length,cr_data[0].length).setValues(cr_data);

Regarding the second part of your question I would like to redirect you to this post:

Best method to extract selected columns from 2d array in apps script