Complex views in embedded Google Sheets

Question

I have a Google Sheets contact list for a multicampus boarding school. The sheet at present summarizes the contact info for each person into the first 4 cells of the row. Entry is made using the rest of the columns. This overall makes editing and sorting far easier.

The kind of thing I would like to do:

A: Only the summary columns are visible.
B: No user can edit data in the embedded web page version.
C: Users can sort and filter on columns that they cannot see.

For example, the first summary column contains this:

Juan Francisco Alvarez
2140 AG01

Decoded, besides the name, it says that he had laundry number 2140, he was last at the Anytown campus in 2001, and that he graduated.

I want users to be able to do things like:

Show me all the grads of O1, sorted by laundry number. (Students that started the same year as Juan will all be in a consecutive block of laundry numbers)

So for this I want to sort by laundry number, without laundry number being a visible column.

Show me all the students who may have overlapped with me (class of 96 to 2006) and who have last known addresses in British Columbia.

I want to sort by cohort year (the year they would have graduated, had they finished) filter by 96 to 2006, filter by the presence of BC in the City/Prov column.

Who has died? (There is a Deceased in Notes for these)

Hide the other campuses. I'm only interested in Mudville (M)

So far in named views I seem to be able to filter or sort on one thing, and one thing only.

Link to sample sheet

Answer 1

You need to write a script and publish it as a Web app.

You can't do that with just Google Sheets. Requirement (C) requires custom script. Unfortunately, for the viewers to use the script, you need to grant them full edit permission, which contradict your requirement (B).

Answer 2

A technique I developed for a different spreadsheet may have application here.

The new problem was, "How do I find what types of lilacs does my supplier have"

My supplier has 20 some kinds of lilacs, in forms from bare root seedlings to 15 gallon potted trees. They are scattered through his list based, I think on which greenhouse, field, supplier they come from.

For me the key was this:

=sort(filter(Source,arrayformula(regexmatch(Formal,$C$2)),arrayformula( regexmatch(Common,$D$2))),3,True)

where Source was the individual sheet provided by my supplier, Common and Formal were the common name (which included pot size,etc) and formal (latin) name of the plant.

$C$2 and $D$2 were the things to match.

This requires that at least this sheet be shared publicly for editing. As a solution it doesn't embed into a web page.