Weirdness with filter.

[RobertPHeller]

I am getting weird results with the filter parameter:

sauron% curl -L "https://script.google.com/macros/s/AKfycbwz3mAVQlt4OGxorkKPiyx0PCaQSY0T0RJ1YItca60RGBlt5yHw45XeLn8sHu9DpCymRQ/exec?url=/api/officials&limit=0&apiKey=d08Low0RV7yL&filter=lower(office)+contains+'selectboard'"
[]sauron% curl -L "https://script.google.com/macros/s/AKfycbwz3mAVQlt4OGxorkKPiyPCaQSY0T0RJ1YItca60RGBlt5yHw45XeLn8sHu9DpCymRQ/exec?url=/api/officials&limit=0&apiKey=d08Low0RV7yL&filter=lower(office)+contains+'selec'"
[{"office":"Selectboard","name":"Laurie DiDonato","term":"2024","electedp":true,"index":29},{"office":"Selectboard","name":"Gillian Budine","term":"2022","electedp":true,"index":30},{"office":"Selectboard","name":"Dan Keller","term":"2023","electedp":true,"index":31},{"office":"Selectboard Clerk","name":"Joy Gorzocoski","term":"2020","electedp":false,"index":168},{"office":"Selectboard Clerk","name":"Liam DiDonato","term":"2020","electedp":false,"index":169}]
sauron% curl -L "https://script.google.com/macros/s/AKfycbwz3mAVQlt4OGxorkKPiyPCaQSY0T0RJ1YItca60RGBlt5yHw45XeLn8sHu9DpCymRQ/exec?url=/api/officials&limit=0&apiKey=d08Low0RV7yL&filter=lower(office)+contains+'selec'"
[{"office":"Selectboard","name":"Laurie DiDonato","term":"2024","electedp":true,"index":29},{"office":"Selectboard","name":"Gillian Budine","term":"2022","electedp":true,"index":30},{"office":"Selectboard","name":"Dan Keller","term":"2023","electedp":true,"index":31},{"office":"Selectboard Clerk","name":"Joy Gorzocoski","term":"2020","electedp":false,"index":168},{"office":"Selectboard Clerk","name":"Liam DiDonato","term":"2020","electedp":false,"index":169}]

sauron% curl -L "PCaQSY0T0RJ1YItca60RGBlt5yHw45XeLn8sHu9DpCymRQ/exec?url=/api/officials&limit=0&apiKey=d08Low0RV7yL&filter=lower(office)+contains+'select'"
[]

I want to select all Selectboard members, but it looks like lower('Selectboard') contains 'selec', but that lower('Selectboard') does NOT contain 'select'. Huh? What is going on here?

[siquylee]

I think you did not encode the condition correctly. It does not support function in query condition so you should try Selectboard contains 'select' instead of lower('Selectboard') contains 'selec',

[RobertPHeller]

I tried various combinations, which gave results that did not make sense. Literally exact equality did not work, but the lower() sort of worked, but weirdly, but only for one partitular case. lower('Selectboard') contains 'selec' worked. but lower('Selectboard') contain 'select' did not, but lower('Selectboard') contains 'electboard' did. This does not make any logical sense -- 'Selectboard' only *sometimes* contains a 't'! This is nonsense. Unless 't' is some kind a strange cat in a box that might be both dead and alive! Huh? The problem was *only* with the 't' in 'Selectboard'. Other elements of other records generally worked just fine. And no, there are no 'hidden' UTF-8 characters. The spreadsheet was created from an ASCII CSV file. Unless Google Sheets import from ASCII CSV files is funky in some odd way. At this point I have totally given up on Google Sheets. I will be looking for some other way of dealing with this. Maybe a dedicated PHP coded webpage using a SQL database. Maybe Zoho's cloud spreadsheet. Maybe something else (AWS?). At Sun, 17 Apr 2022 17:39:38 -0700 gslack-app/sheet-api ***@***.***> wrote:

…

I think you did not encode the condition correctly. It does not support function in query condition so you should try Selectboard contains 'select' instead of lower('Selectboard') contains 'selec',

-- Robert Heller -- Cell: 413-658-7953 GV: 978-633-5364 Deepwoods Software -- Custom Software Services http://www.deepsoft.com/ -- Linux Administration Services ***@***.*** -- Webhosting Services