2

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

1
  • How are you embedding the spreadsheet? Are you able to create the complex views? Are "named views" the same as "filter views"?
    – Rubén
    Jan 18, 2016 at 2:18

2 Answers 2

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

2
  • Someone already write the script, publish it as a web app and shared it with the world. See Awesome Tables.
    – Rubén
    Jan 18, 2016 at 2:20
  • Thank's Rubén. Perfect solution. Gracias, Danka, Merci Jan 19, 2016 at 2:41
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.