Google Sheets use a formula for the supporting lookup column value

Hi @Timtech 

You may have to think about approaching this a bit differently.

NOTE: This will work on low volume Zaps.

CONCEPT

Try another GSheet tab that uses a formula to references a value you update via a Zap which then filters the original data set to the desired records that match less than or equal to, for which you can then do a GSheets Find Many Rows step.

FILTER: https://support.google.com/docs/answer/3093197?hl=en

QUERY: https://support.google.com/docs/answer/3093343?hl=en

ALTERNATIVE (Advanced Approach)

Use Airtable instead of GSheets.

Airtable has useful APIs: https://airtable.com/api

Airtable APIs can be leveraged and filtered via a Webhooks step in the Zap: https://zapier.com/apps/webhook/integrations#triggers-and-actions

NOTE: Airtable returns 100 records by default.

Hi Troy,

Thanks for the quick response. The rows in GSheets are being created by data from Knack. So constantly adding new rows. The GSheet option you suggested doesnt seem quite what I l am looking for as I am having basically to sort the data before I find the rows. I am looking to make this automated as it is not really going to be a low volume Zap. I will look into both options. It just a shame the supporting lookup volume value cant use a formula like <=.

Hi @Timtech 

If you’d like you can submit a feature request to Zapier Support here: https://zapier.com/app/get-help

FYI: Generally, when trying to deal with multiple records, usually an app’s APIs have to leveraged, as most Zap app triggers/actions/searches are performed on a per record basis, and for the ones that do support line items, the configuration options tend to be limited/restricted.

Yes I can see this being a huge obstacle for me. But as usual I will have to find a work around. Basically I have Buyers in Knack with same fields as Sellers. So the Buyer records all get dumped in to a GSheet and then when a Seller record comes into Knack my Zap executes with a new record Knack Trigger and then the Zap Action runs to find multiple rows om GSheets based on Area and Price. Then it emails the potential Sellers Agent who I work with the list of potential Buyers. Price is seldom and exact match it usually a range or maximum.

I could us Zapier Filter after finding the rows but it only has Greater Then or Less Then but not Less then or equal to for Numbers. Go figure.

@Timtech

With Airtable you can share Views that have preset filter: https://support.airtable.com/hc/en-us/articles/360046107493-Shared-view-URL-filters

@Timtech 

With Zapier Filters, you can achieve the “equal to or less than” by adjusting the “less than” operator value.

For example, if you only wanted values equal to or less than 10, you’d set the Filter logic conditions to be less than 11.

I got that but I was using the price field from my Seller record from Knack that was the trigger of the Zap. This number will always change with the records. I can figure a work around for that pretty easily.

On a separate note which I probably should do a new post once I have retrieved multiple rows from sheets which I wanted. What  is the best a way to send one email with the all the rows formatted like a table. Because when you go to create the email and insert data in the body altogether example of one field below . I have multiple fields for each individual row found. I know how to send an email in Zap but its the formatting. Ss no way to send an email with all the rows like the action pulled them in?

@Timtech

You can also use Digest by Zapier: https://zapier.com/apps/digest/help

This can be used with the Looping action: https://zapier.com/apps/looping/help

I will take a look at both. Thank you.