Dynamic Filter for the Google Sheets Search Rows module

How To
1

How can I search rows in a Google Sheet using dynamically applied filters? Here is a simple example of what I mean:

I have a scenario that searches two sheets. The first sheet contains search criteria which will be used to search the second sheet.

1
1856Ă—473 30.5 KB

In the first sheet, the first column contains an email address to identify a user, while the other columns contain search parameters that specify which features the user requires in a lake to live on. A criteria with a value of 1 means the lake must have the feature, a value of 0 means the lake must NOT have the feature, and a blank value means it doesn’t matter if the lake has the feature.

2

By searching the sheet for an email address (banditomartino@gmail.com) we find Bandit’s search criteria. Bandit does NOT want a lake that allows boats to waterski (a full rec lake) because the waves make canoeing difficult. He doesn’t care if the bottom is sandy or if the lake is large. However, he wants the water to be clear and deep, and he wants there to be a bar/restaurant on the lake he can boat to.

3

The second sheet contains a list of lakes and their features. Searching the sheet to find which lakes match Bandit’s requirements should result in rows that include Bass Lake, Brekk Lake, and Fish Lake.

4
41104Ă—893 90.5 KB

Unfortunately, if I filter the second sheet using values in the first sheet, it returns no results. The problem is how to filter when the values in the first sheet are blank because it doesn’t matter if the feature is present or not. In this case, the Sandy and Large fields in the first sheet are empty, so the filter will only return rows where the corresponding lake columns are empty.

Does anyone have advice on getting the scenario to return the lakes that correctly match Bandit’s requirements?

1 Like
2

Hi @Dmitri_Martin,

we are using google sheets literally for everything and I thought I almost know everything there is - but that’s a great question! :stuck_out_tongue_winking_eye:

I’d suggest a little different solution by not filtering in the google sheets module but using the filters in Make. Let me explain:

a) get the row as you do it.
b) get all lakes including their attributes
FILTER
c) do whatever you want to do with the ones who pass the filter

How to set up the filter:

  • choose Boolean filter: true/false
  • if value of a) is empty then make it true (because the customer does not care)
  • if the value of a) is not empty, compare it to the value of b) and if those are equal, return true or false.
  • do this for every column and use AND because you only want lakes which are a great fit on every aspect :slight_smile:

I hope it works this way, if not please let me know!

Best,
Richard

1 Like
3

Thank you for help but I don’t understand. Are you saying the Search Rows module shouldn’t have any filters so all rows are returned as separate bundles? If so, how do I filter the bundles? In a link filter to one other module or a series of filtered links to a series of modules? What would the other modules be?

4

I figured out a way to do this using only the filters within Search Rows. Basically, I created a variable for each feature preference that stored its opposite value and then did a “not equal to” filter in Search Rows. Not obvious but so simple!

1 Like