@gtable(())

gtable is a new, experimental, feature in pluskit 1.1. It allows you to include easily include dynamic content within your RW projects that comes from Google spreadsheets.

Why is this cool?

  1. You can present dynamic data that is updated on the fly.
  2. You can use forms to provide searchable database functionality
  3. Google spreadsheets provides a easy way to have 'web' storage that you can edit with a nice visual editor.
  4. You can Collobarate with Google spreadsheets. Invite other people to add content and have that content appear on the web page.
  5. Google Spreadsheets can do more than math. There are also 'web' functions that provide 'up to date' for things like stock prices.
  6. The updates are instantly reflected. You can do simple inventory management with Google spreadsheets and have the website update automatically.
  7. Google keeps revisions so you can easily see what changes were made and roll back to old versions of your data.


In order to use gtable I highly recommend you install Growl first. It's a system wide notification that gtable uses to provide diagnostics, errors and warning -- Trust me you will be much less frustrated after installing it.

The syntax of gtable is a little unusual because of the large number of arguments. It comes in the form of name:value pairs where the first part is the argument name and the second part is the argument value.

Name Value Required? Dynamic?
key key value yes no
sheet sheet name no no
search search term determines what results to return based on search no yes
header true or false no no
theme theme name (see here for list of themes) no no
id id Provides a custom ID for the resulting table no (but recommended) no
html semicolon (;) seperated list of columns to treat as having embedded HTML no yes
ignore semicolon (;) seperated list of colulmns to not show in table no yes
sortby column name to sort no yes
reverse true or false Determines sort order no no

Enough talk, let's get started.

Getting Started (quick version)



  • Assuming you have a google account go to http://docs.google.com and create a spreadsheet
  • At minimum you need the 'key' argument (see more details here). You may also, optinally, want the 'sheet' argument:
  • Make sure you are publishing the spreadsheet so it's public. gtable requires that the spreadsheet be published
  • insert the gcell comment @gtable((arguments)) into any styled text page.

A few things to keep in mind

  • The page must have a 'php' extension.
  • Google spreadsheets returns data in UTF8. If you use non ascii characters your page needs to be UTF8 also to avoid getting 'garbage'
  • During preview, publish or export you have to be 'online' since I sort out the exact references during that time.
  • Your web host must allow PHP to access external sites. Almost all due but occasionally you'll find one that doesn't
  • Doesn't work on .mac (no PHP) your site.



Example



For example. Take the spreadsheet below



The key is pegfyTc8f6LYQpwkWvmq-Dg, I've published it (required to do if you want to view it on the web) and I've also set it up to 'auto publish' after every change.

There are more details on the argument list here but for now just trust me. I want to show headers, the 'Name' and "Description Comments" column contain HTML so the gtable command looks like:

@gtable((key:pegfyTc8f6LYQpwkWvmq-Dg,id:my-phone-table,header:true,html:Name;Description Comments))

The live results are:

namedescriptioncommentsavailabilitycostmemorynotes_cztg3
iPhone A really slick phone from: Apple No $599 8 8GB version //
iPhone A (slightly less) Phone from Apple No $499 4 4GB version
N95 A slick phone from Nokia No $785 1 Battery Hog
Ocean A phone from Helio Yes $300 0.5 Looks slick but who want's it when you can get a iPhone?
Razr Thin but otherwise unremarkable phone from Motorola Yes $59 0.01 No thanks.
T601 Old school phone Yes $29 0.01 Old
Treo 650 My Current Phone, Feeling it's getting really old in the tooth Yes $45 0.25 No thanks



Now let's say you wanted it sorted by price, and the first column "Name" had embedded HTML and you wanted a header and the 'lab-report' theme and oh-yes, please only show phones between $10 and $1000 you would say.

@gtable((pegfyTc8f6LYQpwkWvmq-Dg,id:my-phone-table2,theme:lab-report,search:cost > 10 && cost < 1000,sortby:cost,html:name,header:true))

With this as the actual 'live' result

namedescriptioncommentsavailabilitycostmemorynotes_cztg3
T601 Old school phone Yes $29 0.01 Old //
Treo 650 My Current Phone, Feeling it's getting really old in the tooth Yes $45 0.25 No thanks
Razr Thin but otherwise unremarkable phone from Motorola Yes $59 0.01 No thanks.
Ocean A phone from Helio Yes $300 0.5 Looks slick but who want's it when you can get a iPhone?
iPhone A (slightly less) Phone from <a href="apple.com">Apple</a> No $499 4 4GB version
iPhone A really slick phone from: Apple No $599 8 8GB version
N95 A slick phone from Nokia No $785 1 Battery Hog



Furthermore, let's say you wanted to hide the "Notes" and "Memory" column and refine the seach for phones with >= 1GB of ram.
@gtable((pegfyTc8f6LYQpwkWvmq-Dg,theme:lab-report,search:Cost > 10 && Cost < 1000 && Memory>=1,ignore:Notes;Memory,sortby:Cost,html:name;descriptioncomments,header:true))

namedescriptioncommentsavailabilitycost_cztg3
iPhone A (slightly less) Phone from Apple No $499 //
iPhone A really slick phone from: Apple No $599
N95 A slick phone from Nokia No $785

Finally, let's say you wanted the user to be able to specify the maximum amount of money they want to pay. No problem, since 'Search' is a dynamic field you can have allow for it as a runtime variable and use a simple form element



With the result:





Ok, at this point your head may be swimming, remember it's really not that hard. Study the command reference and look at my examples and most of all experiment.