Google Sheets REGEX or WildCard Search in LoopUp Filter

[ryan_ioa]

Hey There,

I have a “status” column in a Google Sheet that I’m hoping to filter if any form of “delivered” is in it.

Example:
“This item has been delivered.” - Will return
“delivered” - Will return
“We are delivering your item” - Will NOT return

Can I do this with the Google Sheets filters provided in the n8n Google Sheets Module?

Thanks,
Ryan

[RicardoE105]

Welcome to the community @ryan_ioa

That is sadly not possible. What you can do is:

1. Return all the fields in the spreadsheet and filter them with an IF Node. Of course, depending on the amount of data that you have, this is extremely inefficient.

2. Move to something like Airtable or Baserow (Open source), where you can filter the data before being returned.

Also, you can make a feature request here in the community.

[ryan_ioa]

Hey @RicardoE105,

Thanks for the response.

Yeah, I figured that was the case.

I actually ended up returning all rows in the sheet and using a Function with the following script to pull out items w/ a specific status:

var packages_to_delete = []
var is_delivered = ""

for (var i = 0; i < items.length; i++) {
  // n8n throwing a weird "not a function" bug. Concatenating something to the 
  is_delivered = items[i].json.status
  is_delivered += "1"
  is_delivered = is_delivered.toLowerCase().includes("delivered")
  
  if (is_delivered) {
    packages_to_delete.push(items[i])
  }
}

return packages_to_delete;

I was getting a weird “not a function” error when I tried to use the .includes() function on items[i] which had me scratching my head for a good hour but once I concatenated the variable with a random string it started working? Do you know why that is?

Thanks again.

Cheers,
Ryan

[RicardoE105]

I would guess the status it’s taking a value different than a string in some items. You can do:

is_delivered = items[i].json.status || “”

[Asuwini_P]

Hi @RicardoE105 ,

Do we have any options available now to regex the data in google sheets ?

[MutedJam]

Hi @Asuwini_P, are you looking at applying matching logic to data read from Google Sheets? You can do this by adding an Edit Fields (Set) node after your Google Sheets node.

Then simply use a .match() (or similar) function with your regular expression inside an n8n expression.