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

Hello Developer, Hope you guys are doing great. Today at Tutorial Guruji Official website, we are sharing the answer of Google Script GetRange with Filter based on values in a column AND select only certain columns without wasting too much if your time.

The question is published on by Tutorial Guruji team.

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

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

We are here to answer your question about Google Script GetRange with Filter based on values in a column AND select only certain columns - If you find the proper solution, please don't forgot to share this with your team members.

Related Posts

Tutorial Guruji