freedive tips and tricks

Load data dynamically

Many governments and organizations frequently provide feeds to live data. This can include political data like election results, environmental data and much more. You can offer that data to your readers in an easy to consume format with freeDive.

Google Spreadsheets offer several special funtions to load content dynamically from the web. You can load XML, HTML tables or lists, CSV and TSV files, RSS feeds and Google Finance data. If that seems like a lot, it is. 

This section will focus on loading a CSV of the latest data from California rivers, updated every 30 minutes during the day, and every hour at night.  Dreamflows, a river rafting and kayaking site, publishes the data from the USGS and California Department of Water Resources.

Start by creating a new spreadsheet called River flow. Then go to the bottom of the window and add a new sheet. 

add new sheet

 

Go to cell A1 in Sheet2 and type =ImportData("http://www.dreamflows.com/realtime.csv"). The feed should import as shown below.

import river csv

 

That's pretty great and the data will update automatically every time the user loads the freeDive widget. But freeDive requires the data to be cleaned up a bit.

Click on Sheet1 at the bottom of the page to return to the original sheet. Type "Name" in cell A1.

Next, we need to import the first river name from Sheet2. Type =Sheet2!B8 in cell A2. The result should look like this:  

import river name


Now type "Place" in cell B1, and then in cell B2 type =Sheet2!B8  The result should look like this:  

import place

 

See the pattern? Great. Continue the process until Sheet1 looks like this:

import row

 

Now we need to get the rest of the data. Click once on cell A2. Hold down the shift key and click once on cell G2 to select al the data in the row. Find the blue square in the bottom-right corner of cell G2. Click and drag the blue square down to about row 200. This will give you the last 200 reports.

extend formulas

 

Perfect. You're ready to publish and share the spreadsheet and use freeDive to make it searchable.

If you want to see an RSS example, check out our spreadsheet that pulls in a feed of the latest earthquakes from the USGS.