Google Spreadsheet Hacks

[dark_administrator]

While our Chromatic Yuletide 2010 Fandoms spreadsheet has remained steady at ~1000 fandoms, our Chromatic Yuletide 2010 Prompts spreadsheet has reached ~500 (!!!) prompts. We realize that may be a rather overwhelming amount of data to shift through and so we have organized this very, very basic guide to navigating through Google Spreadsheets in hope it will help you find the information for which you're looking.

Google Spreadsheet Hacks

So someone linked you to a Google spreadsheet document, what now?

First of all, check what type of spreadsheet it is:

Published spreadsheet:

Screencap of a Google Docs spreadsheet, html coded with fixed tables.

A published spreadsheet is basically a html version of a spreadsheet document which the owner has chosen to make available for a simple and easy presentation of spreadsheet data; the layout will be plain and fixed and the url will contain https://spreadsheets.google.com/pub?key= and the document numbers.

Public spreadsheet:

Screencap of Google Docs' editing interface with a spreadsheet opened and with the text "Public on the web" highlighted.

A public spreadsheet is a spreadsheet document to which the owner(s) has granted everyone access; you do not need a Google account nor an authorization key.

You will see toward the top of the document next to the Google docs logo and the title an icon of a globe and a Public on the web text and the url will be https://spreadsheets.google.com/ccc?key= and the document numbers.

Protected spreadsheet:

Screencap of Google Docs' editing interface with a spreadsheet opened and with the text "Anyone with the link" highlighted.

A protected spreadsheet is a spreadsheet document to which the owner(s) has granted anyone with a link access; you do not need a Google account, but you do need a link with an authorization key.

You will see toward the top of the document next to the Google docs logo and the title an icon of a globe & lock with a Anyone with the link and the url will be https://spreadsheets.google.com/ccc?key=[document numbers]&authkey=[authorization key].

Private spreadsheet:

Screencap of Google Docs' editing interface with a spreadsheet opened and the text "Private to me" highlighted.

A private spreadsheet is a spreadsheet document to which the owner(s) has grant no one or selected Google accounts access; you need a Google account.

You will see toward the top of the document next to the Google docs logo and the title an icon of a lock and a Private to me [+ x more] and the url will be https://spreadsheets.google.com/ccc?key= and the document numbers.

Secondly, if you are viewing a public, protected or private spreadsheet, check if you were granted the ability to edit it.

View only:

Screencap of a Google Docs header with the text "View only" highlighted.

If you were grant access to view only, you will see toward the top right of the document next to the Share drop-down menu a View only text. This means you will not have the ability edit the spreadsheet in any ways.

Otherwise, if you do no see a View only text, you can contribute to the spreadsheet by adding or editing data.

Screencap of Google Docs' editing interface with a spreadsheet opened and with a) the text "Saved seconds ago" and b) undo & redo buttons highlighted as well two text boxes illustrating multiple users editing.

Google docs is designed so that multiple users can work simultaneously on a document. For example, if there are multiple users working, their placements in a document will be shown in highlighted boxes (e.g. anonymous user z is shown in a green box while you are shown in a blue box; if you are both working on a spreadsheet and anonymous user z is typing data in a cell, that cell will be shaded gray). Google docs also has excellent save and undo features.

Thirdly, you can sort and filter spreadsheet data so they become more manageable. Using Google spreadsheet, you can sort the data of a column in alphabetically order in two ways:

1. Select a column you want to sort or the entire spreadsheet (Ctrl+A).
2. Go to Tools > Sort... and a Sort range from [Column:Row] to [Column:Row] window will appear.
3. If the spreadsheet has header titles, check Data has header row.
4. Then under sort by, select the column you want to sort and choose A → Z or Z → A. You may also choose to Add another sort option. If so, select that option, then select the second column you want to sort and choose A → Z or Z → A and so on. When you're done, click Sort.
   
   
   
   Screencap of Google Docs' sorting options window with the options "Data has header row", "sort by [Column F] A → Z" and "+ Add another sort option" highlighted.
5. The spreadsheet will then refresh and it will be sorted alphabetically by your setting.
   
   
   
   Screencap of a Google Docs spreadsheet with Column A then B sorted.

Or you could:

1. Select a column you want to sort and right-click.
2. A context menu will appear. Select Sort sheet by A → Z or Sort sheet by Z → A.
3. The spreadsheet will refresh and it will be sorted alphabetically by your selection.

Remember, however, sorting the spreadsheet will force the sorting view for everyone who has access to the spreadsheet (e.g. if the spreadsheet is sorted alphabetically by column A originally and you sorted it by column F, anyone who access the spreadsheet after that will see the spreadsheet sorted by column F) so you may want to revert the sorting, if there's a default for the spreadsheet, to the original one.

You can also filter data by switching to List view by going to View > List view:

Screencap of a Google Docs spreadsheet in List view.

The url of List view will be https://spreadsheets.google.com/lv?key=[document numbers] and in that view, you can filter data in multiple ways. For example, using the Chromatic Yuletide 2010 spreadsheet, you could select Medium and filter results to TV Shows:

Screencap of a Google Docs spreadsheet in List view with a drop-down menu for Column D (i.e. Medium).

And then filter Region by South Korea and sort Recipient alphabetically:

Screencap of a Google Docs spreadsheet in List view with a) Column C sorted by A → Z and b) Column D & E filtered.

The spreadsheet will refresh each time and display your results.

Lastly, you can copy or download a copy of the spreadsheet for your own personal use.

To download a copy of the spreadsheet, go to File > Download as and select the format you would like to download the spreadsheet as (e.g. CVS, HTML, Text, Excel, etc.). If you're logged in to a Google Accounts, you could make a copy of the spreadsheet for your Google docs by going to File > Make a copy....

There are a lots of more awesome features so be sure to play around with Google Spreadsheets. Ours are once again:

• Chromatic Yuletide 2010 Fandoms & Characters List: spreadsheet and html version
• Chromatic Yuletide 2010 Prompts: spreadsheet, form and html version
• 2009 Prompts That Work for the Dark Agenda Challenge: spreadsheet and html version

ETA: There was some technical issues in accessing these spreadsheets, but hopefully, they are fixed now!