Can Google spreadsheets solve my problem?
September 23, 2011 3:54 PM Subscribe
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.
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.
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
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
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]
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
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
« Older It turns out the owls are pretty much exactly what... | Crazy family history...how do I avoid passing it... Newer »
This thread is closed to new comments.
posted by jsturgill at 4:24 PM on September 23, 2011