Can Google spreadsheets solve my problem?

September 23, 2011 3:54 PM

I have a massive Google spreadsheet containing entry info for a small film festival. I need to be able to produce tailored reports from it that only contain need-to-know info, instead of making people look at the master sheet. Is this possible? If not, what online software should I use?

If it is possible, maybe you could point me toward a place I can learn how to do it. "Need-to-know" in this case means that, for example, the hospitality person only has to see what people are coming to the festival, the print traffic person only has to see formats and locations, etc.

posted by goatdog to computers & internet (5 answers total)

Do you have a programmer friend/festival volunteer? This is the sort of thing databases are made for. If you're not overly worried about security, and mainly are worried about the sorting/selective displaying for efficiency's sake, this should maybe take a few hours to import into a database and then put a simple frontend on a password protected page.
posted by jsturgill at 4:24 PM on September 23, 2011

I don't know of a way to ensure that inappropriate people can't ever see the content you filter out of their view, but if your only goal is to create distinct, simplified views of the data, then here's a way to do it. (Note: I'm not a spreadsheet wizard, so I wouldn't be surprised if there's an easier/better way keeping within Google Docs.)

Create an additional worksheet for each view. For each column in each new sheet that should be derived from the original sheet, enter a formula in the top cell like so: ='Sheet1'!A1. The cell containing that formula will have a squarish blue dot you can drag down to the bottom of the sheet, and voila, the new sheet replicates the column of data you care about. Repeat that for each column going into this view.

Then, you have two ways to let people see it. Under sharing, you have the option to publish worksheets as web pages, either collectively or individually. Publishing them under individual links has the merit of probably never showing the 'wrong' data to the wrong group of people. Except ... absolutely anyone who gets those links will be able to read the data. An alternative is to share the entire document with read-only permissions for all the appropriate folks. But you have to trust them to only look at 'their' worksheet.

Obviously, this is a class of problem that custom web development would handle much, much better, but I can see wanting to keep this simple.
posted by Monsieur Caution at 4:44 PM on September 23, 2011

I'm on board with Monsieur Caution—as another non-guru this is basically how I'd accomplish what you want to do: Break out (copy and paste into a new document) the appropriate columns into separate documents, and distribute the appropriate links to those who need them. Alternatively, if it's just a matter of wanting to keep your people from having to pore over a huge worksheet to find the info they need (rather than keeping info private from certain parties or something like that), you could just publish a list of the new worksheets to everyone and let them use what they need.

I'd make sure to give the new documents meaningful names like 'Roster' and 'Formats & Locations', rather than something like 'view1' or 'versionx'.
posted by carsonb at 4:52 PM on September 23, 2011

Get this book: The Practical SQL Handbook. Read the section on database design, break up your spreadsheet accordingly. Save as .csv files. Download MySQL (and an optional free GUI to make life easier if you don't want to learn the SQL syntax...but it's better if you do). Set up the tables. Import the data. Create views. Query and print to your heart's content. You can do it!
posted by iamkimiam at 5:03 PM on September 23, 2011 [2 favorites]

Filters? Sorting?

Set up columns for the hospitality person, the print person, the rock star, the technician, whatever. If they need to see it, put an 'X' in the box and filter to show just that.
posted by chrisinseoul at 8:26 PM on September 25, 2011