Search bar query for data from Google Sheet

App Building
,
1

I am building an app for a data I have on Google Sheet. I have fetched the data from my Google Sheet and displayed on the table. I am working on to add a search bar to my table where I can search for a specific terms from multiple columns (I can say two columns to be more specific).

To achieve this I tried to leverage Query JSON with SQL resource where I filter the data I fetched from the Google Sheet. But, the where clause doesn't seem to work as expected - it keeps returning empty data for a reason I don't know.

I am using the below query:

FROM {{ getPartsTranslation.data }}
WHERE LOWER(part_name_en) LIKE LOWER('%{{ textInput1.value }}%')

I have also added the data I am working on for your reference:

[
  {
    "part_name_en": "Shock Absorber",
    "part_name_pt": "Amortecedor",
    "part_name_fr": "Amortisseur",
    "hs_code": "8708.80.16.00",
    "drive_side_specific": "No",
    "alternative_names": "Shock, Shocker, Dampener, Suspender",
    "links": "WIKI",
    "category": "Suspension"
  },
  {
    "part_name_en": "Filter - Oil",
    "part_name_pt": "Filtro - Oleo",
    "part_name_fr": "Filtre à huile",
    "hs_code": "8421.23.00.00",
    "drive_side_specific": "No",
    "alternative_names": "Oil Cleaner, Oil Element,Hydraulic Filter",
    "links": "WIKI",
    "category": "Engine"
  },
]

Thank you for your time!

1 Like
2

Hey @gemechisw,

An easy solution would be to add a search term to the table.
This filters the data displayed on the table. You can add {{ textInput1.value }} here:

image
image829×685 86.8 KB

If this doesn't work for you or it's not the intended behavior, you can change your query to match this format:

SELECT *
FROM {{formatDataAsArray(query1.data) }}
WHERE title ILIKE {{'%' + textInput1.value + '%'}}
1 Like
3

Thank you @GuilhermeSilva this easily did the job.

I have already got the result I wanted with this query, but I think your solution seems very effecient:

SELECT * 
  FROM {{ getPartsTranslation.data }}
WHERE
  lower(part_name_en) LIKE lower(concat('%', {{ searchField.value }}, '%')) OR
  lower(part_name_fr) LIKE lower(concat('%', {{ searchField.value }}, '%')) OR
  lower(part_name_pt) LIKE lower(concat('%', {{ searchField.value }}, '%')) OR
  lower(alternative_names) LIKE lower(concat('%', {{ searchField.value }}, '%'))

Does the table search term work for contents that come from multiple tables? Which one should I use in that scenario?

2 Likes
4

You can test it yourself, but search term filters at the table level, so you can pass all the contents to the table's datasource and the search term will filter through all of those.

2 Likes