Google Sheets Search Rows Filter Fail Fixed!

I have a scenario that puts new clients from Acuity Scheduling into a Google Sheet, after first checking that they’re not already in the Sheet using a Search Rows module filtered by first, last, email and phone. This somehow thought a recent client named Nouhaila was 14 different other clients and overwrote their names and emails with hers:

Thank goodness for Sheets cell edit history or I’d have no way of knowing which clients were overwritten!

Question is, why those rows and how to prevent recurring?

The same rows were subsequently overwritten with another client, Margo, so it’s something she and Nouhaila have in common that’s triggering a false match.

Weird, this is all it’s filtering on. Must be something about the trim function?

Got it: it was all the ones with blank phones!

Filtered by Margo’s name, they’re all blank and blank has been replaced with another blank by the automation:

To confirm, filtered by phone column and the only blanks were those same records:

To prevent recurrence, made it match on (email AND first) OR (phone AND first) OR (first AND last) instead of just email OR phone OR (first AND last) . This would have prevented the Nouhaila and Margo errors, but would not prevent a future error if client Joe Smith and client Joe Jones both lack an email or both lack a phone. This seems fairly unlikely, however. If anyone can think of a more foolproof solution, I’d love to hear it!

Hi @Robert_McKay,

Props on figuring out the initial issue of the phone number is blank.

There are a couple of ways to do this, but not sure if there is a way to figure out a unique client given that the Name could be duplicate for clients. And, From your description, it seems that the email/phone number can be blank.

What you can do, if it makes sense, is to reduce the Search Rows Returned Data to 1 or 2, so it won’t grab all the data matching it. Which still doesn’t solve the issue.

Can you just not use,

(email AND first AND last) OR (phone AND first AND last) OR (first AND last)

And, Add a filter, checking if Phone/Email from Acuity is blank then just pick the search rows with those fields only?

1 Like

Yeah I’d have to think that through. The other issue is I’m a tutoring agency, so a lot of clients have 2 parents & a kid, all of whom might book at various times with different first/last/email/phone combos. And Acuity doesn’t do unique client IDs. So you see how it gets complicated…