Using The Autofilter Application

The application requires that the spreadsheet you use be published. This application is read-only; it will not modify the spreadsheet you provide.

First, make sure your document is published by going to the Publish tab within the Spreadsheets application. Next, copy and paste the URL of spreadsheet. Visit this application and click 'Load Different Sheet' and paste the URL. The autofilter application will then open your document for you.

Remember that loading large documents can take a very long time, especially in Internet Explorer. If your document is small and has not loaded in about 10 seconds or so, please verify that you provided a valid key and the document is published.

Filtering Based on Data

The whole use of this application is to auto-filter your large data-sets. To filter based on a column, select a value to filter on in the drop-down box at the head of the column.

When you pick a value, only entries in that column that match that option will be shown. To clear the filtering, choose 'Show All' from the filter drop-down box.

It's also possible to filter on multiple columns. Just continue applying filters in different columns, and only rows that match all conditions will be shown.

Using The Bookmarklet

To make switching to this application from your spreadsheet easy, a bookmarklet is provided. After publishing your document from the Spreadsheets application, hit the bookmarklet link that you saved, and it will take you to this auto-filter application and load up your sheet automatically.

Drag this button: Open in AutoFilter to your bookmarks toolbar to install the bookmarklet. To use it, click the bookmarklet when you're visiting the Spreadsheets document you want to auto-filter.

Using Custom Filters

The real power of this application comes from its powerful support for custom filters. Say you want to show all rows where a value is less than or equal to 5. With simple filtering, that's just not possible. With custom filters it's not only possible—it's also easy.

To use a custom filter, choose 'Custom Filter' from the drop-down box in the column you want to filter on. A prompt box will show up, asking you to fill in your filter. Your expression can be as simple or complicated as you like. Here are the general rules:

Basically, expressions are written just like they'd be in C, Java, Javascript, and any number of other languages. The variable 'value' will be set to the value currently being considered for inclusion. More specifically:

• Your expression should return true if the value being examined should be shown, or false if it is should be hidden.
• Anywhere that says 'value' (without quotes!) will be replaced by the value in each row that is being considered.
• All basic equality testing operators are available: ==, !=, >, < , >=, and <=.
• Boolean operations are available, too. You can use and (&&), or(||) and not (!).
• Use parentheses to bind expressions if you want to override the expression order.
• Strings must be contained in quotes (either single or double). Strings are always compared without regard to case.
• Numbers need not be in quotes.
• Wildcard operators are allowed. '*' matches any 0 or more characters, '?' matches exactly one character. Use a backslash before the character to have it be intrepreted as a non-wildcard. Wildcard characters that are in strings derived from 'value' will be interpreted as normal strings. Wildcards may be used for the 'needle' argument of the contains function.
• A limited number of built-in functions can also be used. Please refer to the section entitled 'Supported Functions' for more information.
• If you make a custom filter and then select custom filter from that same column later, it will auto-fill the prompt box with exactly how the computer intepreted your expression. This is very useful for troubleshooting expressions that aren't doing what you expect them to.

A few examples:

• value < 5 || value > 20
• value == '*dog*' && value != 'doghouse'
• date(value) < date('2/20/2006')
• length(value) == 3 || length(value) > 10

Supported Functions

The following functions are supported:

• price of value or price(value) Extracts a decimal number from the given price-string.
• date of value or date(value) Converts the value or string into a date object, which is useful for making date comparisons.
• length of value or length(value) Returns the length of the given string.
• haystack contains needle or contains(haystack, needle) Returns true if the string needle was found in the string haystack, false otherwise.

Natural-Language Filters

Those mathematical expressions are useful, but they can be intimidating. Luckily, this application also understands a lot of basic natural language expressions, too.

• Use the word 'value' (without quotes) as a stand-in for the value to be considered for each row.
• Use 'and', 'or', and 'not' just like they work in English.
• Use 'is', 'is not', 'is greater than', 'is less than', 'is greater than or equal to', and 'is less than or equal to' just like you normally would.
• One-word strings don't need to be enclosed in quotes (although to be on the safe side, you should). If you want a string longer than one word, however, you'll need to enclose it in quotes.
• All functions support natural-language syntax. For example, "length of '****' " would return 4, and "value contains 'ro' " would be true for any value that contained the string 'ro'.
• If you go back to enter a new custom filter after you'd already entered one, the text of how the computer understood your expression will show up in the prompt box by default. This is helpful for troubleshooting expressions that don't work how you expect.

Filter Examples

All of the following are valid natural-language filters. Give them a try, or make your own!

• value is 5 or value is greater than 10
• value contains dog and value is not doghouse and value is not hotdog
• length of value is less than 3
• length of value is 2 and value is not NR
• val is not 3
• date of value is less than date of '3/23/2005' Note that date literals must be within quotes.
• price of value is less than 3.25
• value is red or blue
• green or blue
• value contains dog or cat
• length of value is 3 or 4
• value is not red or blue
• not green or blue
• value is red or green or blue
• value is not red or green or blue
• length of value is not 3 or 4 or 5
• *berry Matches any string that ends in 'berry'
• g*t Matches any string that starts with 'g' and ends in 't', like 'great', 'goat', and 'got'
• */8/* Matches any date (in standard format) that is the 8th day of the month
• */?/* Matches any date (in standard format) that occurs in the first 9 days of the month

FAQ

There are lots of ways! The easiest is to hit the 'Load Different Sheet' link at the top of the page. Make sure you have the URL of your published sheet handy. When the dialog box pops up, just paste in the URL to your sheet, and the program will automatically extract the key. Alternatively, you can append '?key=xxxxx' to the end of the URL of this application, where xxxxx should be the 23-digit key for the document you want to view (the key can be found at the end of the URL you use to view the document on Google Docs and Spreadsheets). Finally, there's a handy-dandy bookmarklet that will automate the whole process for you. More information on that is available in the bookmarklet question below.

If your document is not published then this application will have no way to access it. Unfortunately, due to the way the Spreadsheets API is designed combined with security restrictions built in to browsers, there's no good way for this application to detect when you're trying to load an unpublished sheet (at least, not without involving a server). Luckily, publishing a document is easy: when viewing your spreadsheet, go to the 'Publish' tab in the upper right-hand corner. Hit the 'Publish Document' button to make the document viewable—but not editable—by anyone who knows the document's URL. Then, you can either use the convienent bookmarklet (see next question) or copy and paste the URL it provides.

The bookmarklet is very easy to use. When you're viewing your (published) spreadsheets on Google spreadsheets, just hit the bookmarklet. It will take you to this application and automatically load your spreadsheet. Installing the bookmarklet is easy, too. Just scroll to the bottom of the window until you see the button marked 'Open in AutoFilter.' Drag that button onto your bookmarks toolbar, and you're done!

Sometimes, if you have a lot of columns with a lot of text in them, your spreadsheet might not fit horizontally in the space provided. Luckily, it's possible to tweak the size of the select boxes. Just hit the 'Adjust Column Widths' as the top of the page, and enter a smaller number. Unfortunately, due to a large number of various browser and DOM quirks, it's not possible for the application to easily figure out the optimal column widths itself.

It's possible to create a link that will automatically load up the spreadsheet you're viewing. Just use the current URL, but add the text '?key=xxxxxxxxxx' (where key xxxxxxxx is the 23-digit key for the document you want to share). People visiting that link will be taken directly to the sheet you're viewing. Note that any filtering you've done in this application will not be reflected in the version your friends will visit. If you used the bookmarklet to get to this application, then you can just give your friends the URL as is.

One of the strengths of this application is its powerful support for custom filters. Unfortunately, the more power you have, the more the potential for confusion grows. Often, the problem will be that the precedence of operators is not what you would expect. To override operator precedences, make liberal use of parentheses to group statements in the way you want them. For example, instead of the potentially confusing "value is orange or value contains 'a' and value is not 'apple'" use "(value is orange or value contains 'a') and value is not 'apple'".

Keep in mind that the natural-expression parser is trying to make educated guesses about what your ambiguous expressions mean. If you're not getting what you expect, try to make it guess less by being more precise; for example, "red or orange" is less precise than "value is 'red' or value is 'orange' ".

One other trick is to get a peek into what the computer thinks your statement says. If you select 'Custom Filter' from the drop down again, the prompt box will be auto-filled with how the computer understood your last filter for that column. Although the text can sometimes be confusing, it will often give insight into what exactly went wrong—and even if you can't fully understand it, how the computer grouped your statements should be clear.

The short answer is no. But don't give up hope! Although there's no UI for it, you can actually change the sheetID by appending ?sheet=xxxxx to the end of the autofilter application URL, where xxxxx is the id of the sheet you want.

Of course you can! Use the '*' character to match any 0 or more characters, and the '?' character to match exactly one of any character. Use '\*' or '\?' if you don't want the character to be interpreted as a wildcard. Strings derived from expressions containing 'value' will never be interpreted as containing wildcards. In other words, the only strings that are allowed to have wildcards are ones that you type into your expression directly.

The Google Spreadsheets API requires that the first row of your spreadsheet be a header row in order to work correctly—that is, the first row of your spreadsheet should be the name for each column. Try reformatting your spreadsheet and trying again.

This application uses the published data from your spreadsheet, so rest assured it cannot—and will not—alter your original spreadsheet.