excel vs google charts capabilities

[Megan Cunniff]

First of all, I apologize for these questions being fairly amateurish.
I'm new to the API, and to coding in general really.

What I'm trying to do is create Google Chart versions of Excel sheets
which graph various concepts in chemistry. One example is a line chart
with three lines taken from three separate columns. Since I have the
Excel sheets already, I assume I could transfer them to Google
Spreadsheets and pull from them in that way. However, the Excel
spreadsheet is interactive, with four sets of buttons to raise/lower
variables (one of which affects the equation for all three lines/
columns and the latter three of which each affect a separate column).
I've been looking at the examples for Controls/Dashboard and they're
all simpler than that; the data is hardcoded in and the dashboard
controls which parts of it are shown. What I need is something where
the data that's being visualized stems from the user-controlled
factors. (In case people are curious, the chart deals with the
distribution of the speed of gas molecules (http://en.wikipedia.org/
wiki/Maxwell-Boltzmann_distribution) and the variables are the
temperature and the molecular weights)

My first question is whether this is possible/practical, and, if so --
since I'm guessing explaining it all might take more space/time than a
help forum is good for -- whether you'd recommend any tutorials or
resources that might help me learn enough to muddle through this sort
of thing.

Thanks for your time!

Sincerely,
Megan

[asgallant]

Welcome Megan,

I suspect that this is doable, but perhaps not in the way you're thinking.  I have no experience with Google Spreadsheets, but I don't think there is any way to modify data or trigger macros attached to the sheets via the Visualization query API.  What you could try, though, is setting up the calculations server-side in PHP or client-side in javascript, though if you're new to coding, then javascript is probably easier to work with.  I took a quick look at the wiki entry you posted, and I think it requires some functions not explicitly provided by javascript, but it doesn't look like there's anything you can't do in js (with some custom functions) either.

Mozilla has very good javascript reference documentation: https://developer.mozilla.org/en/JavaScript/Reference, in particular you may have need of the Math object: https://developer.mozilla.org/en/JavaScript/Reference/Global_Objects/Math

http://www.jsfiddle.net/ is a good place to play around with javascript to test things out (and you can save your code to share with others if you find something vexing or have a brilliant idea).

Google's Visualization Playground (http://code.google.com/apis/ajax/playground/?type=visualization) lets you experiment with the various kinds of charts, examine the different ways they can be constructed, and see what the options do.

Googling "javascript tutorial" will get you a bunch of resources, though I would recommend against anything from W3Schools - they are often vague, incomplete, or just plain wrong about the subjects they purport to be experts in.

[Riccardo Govoni ☢]

Hi Megan,

If you already have an interactive Excel sheet, you could try replicating the same functionality in Google Spreadsheets using Google Apps Script (http://www.google.com/support/forum/p/apps-script/ ) which allows you to define 'macros' and custom functionality on top of your Google Spreadsheets data (just like Excel macros do). Google Apps Scripts requires you to use the Javascript language to define the interactive functionalities you need. They have various tutorials here : http://code.google.com/googleapps/appsscript/articles.html

You could arrange your tool in this way:

- define a Google Spreadsheet containing your data a few interactive controls that, once clicked, trigger a Google Apps Script function to do the computations you need and write the generated output (the three columns of data) in some other part of the spreadsheet (maybe a separate sheet?)

- have a line chart, embedded in the Google Spreadsheet, use such generated output as its input. It should then update automatically whenever the data changes.

This would let you achieve what you need while remaining within Google Spreadsheets, and hopefully without having to write too much code aside from the actual calculations your macros already contain.

-- R.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.

To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/qhUBk08GcUEJ.

To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.

[Megan Cunniff]

Thank you both for your helpful responses! Riccardo's solution was
indeed very light on code, and easy to work out.

Bad news was that the in-spreadsheet chart plus the script functions
ended up lagging a bit too badly on the iPads we tested with, so we'll
probably have to end up looking into other things. (If anyone knows of
any other HTML5-based slash iPad-suitable alternatives, I'd love to
hear back...though, heh, I feel bad writing that on a Google
forum...)

Still, very good practice and now I know about the option for other
things! Thanks again!

-M

[Riccardo Govoni ☢]

For your purpose you don't really need to have the entire spreadsheet functionality available, do you?

If so, to easen the load when accessed via tablets, you could try serving the interactive functionality and charts outside of the spreadsheet.

This would be a combination of:

- exporting the scripts to be accessed as services (http://code.google.com/googleapps/appsscript/guide_user_interfaces.html#RunDecision )

- export the charts by embedding them into an html page of yours and accessing the spreadsheet data via queries ( http://code.google.com/apis/chart/interactive/docs/queries.html ).

In this way you'd rely on Google Spreadsheets to host and manipulate your data, but not on it for the view/presentation part.

Now, I'm not sure how much more complicated your case becomes if you use the above suggestions. It looks like it's approaching a complexity where you should consider whether it's worth bending Google Spreadsheets to fit your case or it might be just easier to code a small custom app for the case at hand.

-- R.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.