Datasets from Google Spreadsheets
Dataset comes with a set of basic importers and parsers that make it easier to import data from a variety of sources, local and remote. As a convenience, one of these sets deals exclusively with Google Spreadsheets.
In order for Dataset to retrieve the data, several things are required:
- The spreadsheet must be published (visible publicly.)
- You need the key from the url.
- If the worksheet you're importing is not the first one, you will also need the index of that worksheet.
In order to connect to a google spreadsheet the following properties need to be set on your Dataset options:
"1"(optional - fetches 1 by default.)
For example we are going to fetch this spreadsheet: https://docs.google.com/a/guardian.co.uk/spreadsheet/ccc?key=0Asnl0xYK7V16dFpFVmZUUy1taXdFbUJGdGtVdFBXbFE#gid=0
By default, the format of the raw data is rather cumbersome. Additionally, the file containing your data may be quite large (even though your data isn't). There are now two different parsers built into Dataset that handle google spreadsheets, one of which is much faster, but does not support utf-8 properly. This is a due to google not properly escaping the data when creating the output json. By default we use the more robust parser, but if your dataset is large or you want to try the faster parser, set
fast:true during dataset initialization.
Fast importing also supports name sheets, so instead of setting
worksheet to the index of the sheet you want, you can set
sheetName to the name of the sheet. Note this only works with the fast parser.
If it's so unreliable why would I use it?
It's not that unreliable. If your data is fairly standard (numeric/string etc.), it will be fine. The only time this becomes an issue is when you start introducing utf-16 characters into your data or having escaped strings. If google resolves their escaping issues, we'll be sure to update Dataset to use this parser by default.
Use Google Spreadsheets a Lot?
Drag this button to your toolbar and anytime you're viewing a google spreadsheet that is publicly published, just click it to grab some code to get you started!
The following dialog should appear in your window when you click the bookmarklet on a google spreadsheet.