4

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.

Marios
  • 26,333
  • 8
  • 32
  • 52
Mike B.
  • 99
  • 3
  • 9

1 Answers1

9

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

Marios
  • 26,333
  • 8
  • 32
  • 52
  • 1
    Thank you Marios. It works great. I was hoping to initially for var data to get range that is already filtered to save time on making another variable filtered by column values, but this will work just fine. – Mike B. Aug 13 '20 at 14:24
  • A follow-up question. I want to add another filter based on a second column criteria: row[5] == 'Closed' How do I add this to the filter(function)? – Mike B. Aug 13 '20 at 14:54
  • `var tdata = data.filter(function (row) { return row[29] == agent && row[5] == 'Closed' ; }); ` – Marios Aug 13 '20 at 15:01
  • Thank you. I ran it and tdata returns an empty array. It runs without an error, but it doesn't like something in : var tdata = data.filter(function (row) { return row[29] == agent && row[5] == 'Closed' ; // starts from 0, column A is 0. }); – Mike B. Aug 13 '20 at 16:32