CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jereme
Jereme

Reputation: 47

Would like to create a sort of "active-filter" shortcut

What I'm envisioning is -

I have 2 sheets of data currently. In Sheet 1 I have rows of data, at the end of each row of data I would like to have an active option like a button or link that when clicked/activated will bring the user straight to the data in Sheet 2.

There is also one more condition in that this link/button when activated will also filter the data in Sheet 2 based off a value in its corresponding row.

For example:

[Sheet 1]

Row 1 - CriteriaX........ButtonX Row 2 - CriteriaY........ButtonY Row 3 - CriteriaZ........ButtonZ

Clicking on either of the buttons will bring the user straight to the data in Sheet 2 already filtered by the corresponding Criteria.

Is there a way to do this?

Appreciate any advice!

Warmest regards

Upvotes: 2

Views: 42

Answers (1)

CMB
CMB

Reputation: 5163

Solution:

You can assign Google Apps Script functions to buttons by clicking the three dots menu and selecting Assign Script:

enter image description here

Then you can put the function name in the dialog box.

Actually you have to do this for every row as per your original proposal, which becomes repetitive quickly as you add more rows. To solve this, you can use only one button, and the selected cell becomes the filter criteria.

As for the code itself, you need to use the Filter class to define filters for ranges.

function createFilter() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var criteriaValue = ss.getActiveCell().getValue();
  var sheet2 = ss.getSheetByName("Sheet2");
  var range = sheet2.getRange(1,1,sheet2.getLastRow(),sheet2.getLastColumn());
  if (range.getFilter() != null) {
    range.getFilter().remove();
  }
  if (criteriaValue != '') {
    range.createFilter().setColumnFilterCriteria(1, 
      SpreadsheetApp.newFilterCriteria()
        .whenTextEqualTo(criteriaValue)
        .build()
    );
  }
  ss.setActiveSheet(sheet2);
}

Sample Output:

Filtering by Product A:

enter image description here

Filtered Table after clicking button:

enter image description here

Upvotes: 1

Related Questions