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.
Best Answer
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).