Using Sortable, Searchable Data Tables in Posts

We’ve updated the content-management system so that it’s easier to add data tables to posts — and to make them searchable and sortable with pagination. Even better: Reporters don’t have to touch any code.

This functionality is possible thanks to a plugin that transfers data from your Google Docs spreadsheets into our database and ultimately into your blog posts. Here’s how it works.

First, you’ll want to format the columns and rows in you’re Google Docs spreadsheet exactly how you’d like them to appear in the browser. For example, if I wanted this display:



I would format my Google spreadsheet like this. Remember that normal width posts will require you to keep the number of columns limited.

Here’s the link to this spreadsheet if you want to experiment in a draft post. 

Next, in Google Docs, make sure you’re columns are properly formated. (Numbers higher than 999 should have comma indicator. Currency should have a $ symbol):

Then you have to share the document so that it’s public on the web. Go to File–>Share–>Change and make it “Public to the Web.” (If this part is confusing you, here is some easy-to-digest documentation). You’ll also need to go back in under File and select “Publish to the Web.”

Select start publishing. Check the automatically republish button. The popup window should look something like this:

Close the window, and then copy the full Google Docs address from the browser’s URL pane. Then switch over to your blog post. To insert a table, select the grid-like button in the visual editor:

Now paste in the URL into the popup screen. The “spreadsheet key” — a long code used by Google to name your spreadsheet — will automatically populate.

IF it doesn’t, go back into your url and copy the gobbely gook between the equal signs after it reads “key=”. Paste that into the key box.

A page setting of “0” will bring in the first sheet in your spreadsheet (more on that below). “Source” is where you obtained the data. “Paginate” allows readers to flip through the table if there are more than 25 records. “Allow filtering” lets readers search the document to explore the records.

 

(When the “Sheet” option is set to “0,” the plugin will pull data from the left-most sheet in your Google document):

After the settings are determined, click “Insert”. You’ll notice that the plugin automatically formats a short code that will render the table. Be careful not to delete or insert any characters inside the brackets. But you can tinker with the short code to change the experience for readers. Here are the elements in the short code (a 0 zero means the feature won’t display. 1) Whether a search box appears above the table; 2) Whether users can page through a large table; 3) Whether they can sort the data (only necessary for larger tables); 4) Google spreadsheet ID; and which “Sheet” in the Google document you’re displaying.

Feel free to add text above and below the short code, but leave line breaks so the text doesn’t bump into the table. Like this:

[spreadsheet key=”0AirC4nWDeIFTdHlJdW9Sc0cxcUxXbXVGaFpGcFVmdXc” source=”PA Department of Environmental Protection” sheet=0 filter=1 paginate=1 sortable=1]

Save the post. This is how the table renders. Notice the search bar and pagination above the headers:

Pagination allows users to navigate the document 25 records at a time. Hint: These data are stored in the browser, so you might notice performance issue with large tables (more than a few hundred records). Feel free to experiment, however, with consultation from the data reporting coordinator. This is how the pagination and source should appear:

Regardless of how many records are in your table, users will also be able to enter “wildcard” searches across all fields if you select a filter. Below a search for “Ford” selects two counties with the text string “Ford” in their names: Bedford and Bradford. The search also picks up numbers.

So that’s how to use tables. Good luck, and let us know if you need help.

Comments are closed.