CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom
Tom

Reputation: 87

Creating a Script for filtering by criteria in sheets

I'm very new to coding and would appreciate any support with what is probably a simple script.

I want to create a script that triggers daily, that filters by criteria on a few different columns.

I have searched the web and can only find help with sorting. I can't seem to find answers when it comes to filtering by criteria.

I want to filter Column D (Status) to any cells that do not have the value 'Closed' and want to also filter column T (Scope) to have only had the value 'Strategic'

Then sort this range by 1st column. (Which I believe I have achieved below) I then want to run a trigger on this daily which I can set up after.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Studio")

  var range = ss.getRange(2, 1, sheet.getLastRow()-1, ss.getLastColumn())

range.sort([{column: 1, descending: false}]);}

Upvotes: 1

Views: 2353

Answers (2)

Jean
Jean

Reputation: 1

Here is the code that worked for me (as I personally could not make the solution shown in the accepted answer to work):

function onEdit(e){

  var ss = e.source.getActiveSheet()  
  var ra = e.range
  var erow = e.range.getRow() 
  var ecolumn = e.range.getColumn()

// name of the sheet + coordinates of the searchbox (here is A1)
  
  if (erow == 1 && ecolumn == 1 && ss.getName()=="Sheet1" ){
    var val = ra.getValue()
    var rang = ss.getDataRange();
    var filterCriteria = SpreadsheetApp
    .newFilterCriteria()
    .whenTextContains(val) 
    .build();
    var filter = rang.getFilter() || rang.createFilter();
    filter.setColumnFilterCriteria(3, filterCriteria); // column to search = 3
  }
  
}

Cheers, Jean

Upvotes: 0

e__n
e__n

Reputation: 717

Not so simple. You need to use filter and filterBuilder, which are not the most intuitive. Here's the basic code to get you started. Modify this for your needs.

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data Studio");

  var range = ss.getRange(2, 1, sheet.getLastRow()-1, ss.getLastColumn());

  // Get the existing filter, else create one for this range. May need to just
  // remove all filters first in case they conflict.
  var filter = range.getFilter() || range.createFilter();

  var column_Status = 4; // column D
  var filterCriteria_Status = SpreadsheetApp.newFilterCriteria()
    .whenTextDoesNotContain('Closed')
    .build();

  var column_Scope = 20; // column T
  var filterCriteria_Scope = SpreadsheetApp.newFilterCriteria()
    .whenTextContains('Strategic')
    .build();

  var column_to_sort = 1;
  filter.setColumnFilterCriteria(column_Status, filterCriteria_Status)
    .setColumnFilterCriteria(column_Scope, filterCriteria_Scope)
    .sort(column_to_sort, true);
}

Setting a daily trigger is pretty straightforward from menu: Edit > Current project's triggers.

Upvotes: 2

Related Questions