Google Sheets – Link to a Filtered View Based on Params

google sheets

I have a spreadsheet1 with the following columns:

name, country, value

In another spreadsheet2 I'd like to have set of clickable links based on parameters:

param1, param2, link

where:

  • link opens a new, filtered view of spreadsheet1, based on params from spreadsheet2
    something like:

    spreadsheet1 where name=param1 and country=param2

How can I achieve this in Google Spreadsheet? I know nothing about scripts yet.

Best Answer

As I mentioned in comments, this can't be done as you wanted because of the lack of programmatic access to filtered views (until this request gets implemented).

So I wrote a script that does something along the lines that you wanted, but using the show/hide columns functionality. To simplify things, I am assuming that the data and the parameters are in different sheets of the same spreadsheet.

For example, suppose the first sheet of your spreadsheet is 

+------+---------+-------+
| name | country | value |
+------+---------+-------+
| Alex | Canada  |   111 |
| Mary | Canada  |   222 |
| Alex | Brazil  |   333 |
| Mary | Brazil  |   444 |
| Mary | Canada  |   555 |
+------+---------+-------+

and the second sheet has 

+------+---------+
| name | country |
+------+---------+
| Mary | Canada  |
| Alex | Brazil  |
| Mary | Brazil  |
+------+---------+

Instead of placing links in the second sheet, I opted for a simpler (for me) approach, a custom menu option:

menu

To use this:

  1. Click on any cell in the row of the second sheet that has the parameters you want.
  2. Choose "Filter" from the menu

You should see the first sheet filtered by the desired parameters: 

+------+---------+-------+
| name | country | value |
+------+---------+-------+
| Mary | Canada  |   222 |
| Mary | Canada  |   555 |
+------+---------+-------+

To go back to seeing all rows, use the menu command "Show all".

Here is the script that does all of this: copy-paste it to Tools->Script Editor (erasing the placeholder there). After saving it, you will need to close and reopen the spreadsheet to have the custom menu option appear. 

function onOpen() {
  var menu = [{name: "Filter", functionName: "filter"}, {name: "Show all", functionName: "clear"}];
  SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function filter() {
  clear(); 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getActiveCell();
  var row = cell.getRow(); 
  var range = sheet.getRange(row, 1, 1, 2);
  var values = range.getValues();
  var name = values[0][0];
  var country = values[0][1];
  if (name&&country) {
    sheet = ss.getSheets()[0];
    var range = sheet.getDataRange();
    var values = range.getValues();
    for (var i = 1; i < values.length; i++) {
      if (values[i][0]!=name || values[i][1]!=country) {
        sheet.hideRow(range.getCell(i+1,1));
      }
    }
  SpreadsheetApp.setActiveSheet(sheet); 
  }
  else {
    Browser.msgBox('Parameters not found in the active row');
  }
}

function clear() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var lastRow = sheet.getLastRow();
  sheet.showRows(1,lastRow);  
}
Related Topic