This is a short update, mostly so it's jotted down somewhere for later. With the bulk of our library catalogued in one big google sheet, but with a large number of un-catalogued books still to add, I needed a quick way to check if each book was already listed or not. Rather than CTRL+F'ing each title, I wanted to see if I could build an interface for keyword searching the list and return a matching subset. It ought to also prove useful when out at bookshops to prevent accidentally doubling up on titles.
Link to the current version of the Library
The operative bit of code, living in cell C3 in the "Live Search" sheet, is a query pointed at the entire dataset, combined with checking a handful of checkboxes to determine which columns to return in the filtered view. The checkboxes here are B4 through B10, and we force the search string and targets to all lower-case.
=query('Raw Data Rows'!A1:G , "Select " & IF(B4, "B", "") & IF(B5, if(B4, ", C", "C"),"") & IF(B6, if(OR(B4,B5), ", D", "D"),"") & IF(B7, if(OR(B4,B5,B6), ", E", "E"),"") & IF(B8, if(OR(B4,B5,B6,B7), ", A", "A"),"") & IF(B9, if(OR(B4,B5,B6,B7,B8), ", F", "F"),"") & IF(B10, if(OR(B4,B5,B6,B7,B8,B9), ", G", "G"),"") & " where lower(B) Contains '" & lower(B1) & "' OR lower(C) Contains '" & lower(B1) & "' OR lower(D) Contains '" & lower(B1) & "' ")
Meanwhile we're still finishing off the last few shelves, and will definitely post photos once they're all together organized!