Hi James, Don't publish a Google Spreadsheet as the 'result' of each query your users made. The steps involved in getting the URL to be used with the Visualisation API (xref http://code.google.com/apis/chart/interactive/docs/spreadsheets.html#Google_Spreadsheets_as_a_Data_Source ) cannot be automated via the Spreadsheets API or the Google Apps Script API.
Let me try to put it all in perspective for you. The Google Visualization API is a client-side technology which runs in the browser's JavaScript engine. The data to plot is encoded in the browser's memory as a 2-dimensional Javascript array which is encapsulated by a DataTable [1]. Once you've gotten your data into the browser, you can leverage chart-display code which other people have created, to actually plot it using HTML. To get the data into the browser, you've got 2 options: 1) populate a DataTable instance with data you get from an XmlHttpRequest on the browser side. You write a bit more JavaScript in the browser, but you are limited only by XmlHttpRequest and your imagination. OR 2) delegate the task of constructing a table to a Query [2]. You write less JavaScript in the browser, but your server has to be a compliant DataSource capable of supporting all non-optional operations which the query syntax demands [3]. You are limited by the abilities of the query language and the availability of data sources which support the Chart Tools DataSource Protocol [4]. It's not clear (to me) if the Query object will append relevant cookies to the XmlHttpRequest it generates on your behalf. If these drawbacks are acceptable, then you could query one of: a) Google Spreadsheet (if your data set can fit there, and you can get the URL ahead of time), or b) Google Fusion Tables, or c) Salesforce (apparently) d) any other 3rd party service obeying the Chart Tools DataSource Protocol, including e) a web-service you write yourself Note that there's no reason to go with 2e unless you want other people to consume your data through the Chart Tools DataSource Protocol too. You'd just go with option 1, because there is no value to you in implementing all that general querying capability, or being limited to its notions. You should definitely think about whether you could publish your data as a bunch of Google Fusion Tables, because of the hosting resources offered by Google for doing that. Could each data series could be pre-computed and published as a Google Fusion Table? Remember that the number of tables is not a concern so much as the 250MB/user limit, there's an API for uploading stuff, and tables can be dynamically merged from other tables. If you find yourself heading towards option 2e for flexibility reasons, then you'd do well to consider whether you can interpose your service between the browser and Google Fusion Tables, letting Fusion do most of the heavy-lifting (eg. sorting), and you just do a bit of post-processing on the query result. In fact, that would work for option 1 as well. If you leaned towards this, you might do well to code your service on Google App Engine, if we assume that inter-Google-product network latency is likely to be better than extra-Google network latency. I hope this was as educational for you to read, as it was for me to prepare :-) cheers, David. [1] http://code.google.com/apis/chart/interactive/docs/reference.html#DataTable [2] http://code.google.com/apis/chart/interactive/docs/queries.html [3] http://code.google.com/apis/chart/interactive/docs/querylanguage.html [4] http://code.google.com/apis/chart/interactive/docs/dev/implementing_data_source.html On Mon, Mar 26, 2012 at 3:49 PM, Jim Schmitz <[email protected]>wrote: > Hello, > > I have been a developer for years but am new to google apps and google > docs. I am trying to build an application using google apps and I am not > sure how to proceed. > > I have a large dataset of volatility data and I want to be able to plot > different subsets of that data in charts. For example, plot the SPX 6 > month 25 delta call minus the 25 delta put vs. time. From what I have > read, that shouldn't be that difficult, but right now I am feeling pretty > overwhelmed. > > It seems to me I can store the data in the google apps datastore and then > build an app to query the data based on user input, do some calculations > and output the resulting data to a spreadsheet with a chart. Is that a > reasonable approach? From what I have read I cannot use Google Fusion > because that cannot be integrated with the datastore, and my dataset is too > large to fit in a spreadsheet. I like the Google Visualization API but I > don't think the query language supports the calculations I need to do. > > What is the best place to start here? Where do I start learning about how > to build a google app that works with data, charts and spreadsheets? Right > now the biggest challenge is wading through all the information. I wish I > had someone to talk to about this. > > -Jim > >
