Well this was a bit of a pain. The solution is below:
//Get spreadsheet data.
data = response.getDataTable();
//Get the column headings.
for (var col = 0; col < data.getNumberOfColumns();
col++) {
propertyName =
data.getColumnLabel(col).split(' ').join('');
cols[col] = propertyName;
}
//Get the row values.
for (var row = 0; row < data.getNumberOfRows(); row
++) {
entry = new Object();
for (var col = 0; col <
data.getNumberOfColumns(); col++) {
propertyName = cols[col];
//Use the formatted value for time.
if (data.getColumnType(col) ==
"timeofday") {
entry[propertyName] =
data.getFormattedValue(row, col);
}
else {
entry[propertyName] =
data.getValue(row, col);
}
}
rows[row] = entry;
}
jsonData.rows = rows;
On Dec 20, 10:43 am, Peppi <[email protected]> wrote:
> OK, I thought I was on the right track with this code. But when I call
> toJSON, it still formats the JSON string in a manner in which I have
> to access it like so - jsonData.rows[0].c[0].v
>
> //Get spreadsheet data.
> var data = response.getDataTable();
>
> //Provide some structure to the data based on the column headings in
> the spreadsheet.
> var structuredData = new google.visualization.DataTable();
>
> for (var col = 0; col < data.getNumberOfColumns(); col++) {
> structuredData.addColumn(data.getColumnType(col),
> data.getColumnLabel(col).split(' ').join(''));
>
> }
>
> for (var row = 0; row < data.getNumberOfRows(); row++) {
> structuredData.addRow();
> for (var col = 0; col < data.getNumberOfColumns(); col++) {
> structuredData.setCell(row, col, data.getValue(row, col));
> }
>
> }
>
> var jsonString = structuredData.toJSON();
> jsonData = JSON.parse(jsonString);
>
> On Dec 20, 9:57 am, ChartMan <[email protected]> wrote:
>
>
>
>
>
>
>
> > Please provide with the code example you are using.
>
> > On Mon, Dec 20, 2010 at 4:31 PM, Peppi <[email protected]> wrote:
> > > I don't want to hardcode this data. It is coming from a Google
> > > Spreadsheet and needs to be generic so any spreadsheet can be read
> > > from. I'm really just using the Visualization API as an easy way to
> > > get the data from the spreadsheet. Not building a visualization with
> > > it at all.
>
> > > On Dec 20, 4:30 am, ChartMan <[email protected]> wrote:
> > > > So now I realize that you are using a structure that is not completely
> > > > aligned with the data table model.
> > > > Try the following
>
> > > > var data = new google.visualization.DataTable();
> > > > data.addColumn('timeofoday', 'startTime');
> > > > data.addColumn('timeofoday', 'endTime');
> > > > data.addColumn('string', 'title');
> > > > data.addColumn('string', 'location');
> > > > data.addRows([[ [8, 30, 0], [14, 0, 0], "CASE District VI Board
> > > > Meeting", "Pershing
> > > > Place North"],
> > > > [ [8, 45, 0], [14, 30, 0], "CASE District VII
> > > Board
> > > > Meeting", "Pershing Place South"]]);
>
> > > > Then you can use data.toJSON() like stated above.
>
> > > > ChartMan
>
> > > > On Sun, Dec 19, 2010 at 5:59 PM, Peppi <[email protected]> wrote:
> > > > > Thanks. That will do in a pinch, but the property names that toJSON
> > > > > generates aren't very intuitive. Is there a way to convert this:
>
> > > > > json.rows[0].c[0].f
> > > > > json.rows[0].c[1].f
>
> > > > > to this:
>
> > > > > json.rows[0].startTime.value
> > > > > json.rows[0].endTime.value
>
> > > > > Thanks.
>
> > > > > On Dec 19, 9:46 am, ChartMan <[email protected]> wrote:
> > > > > > The data table toJSON method returns a Json object in its string
> > > format.
> > > > > > If you want to get the object for that string you can use
> > > > > eval(jsonString)
> > > > > > or the safer method JSON.parse(jsonString)
>
> > > > > > ChartMan
>
> > > > > > On Fri, Dec 17, 2010 at 11:29 PM, Peppi <[email protected]> wrote:
> > > > > > > Hi,
>
> > > > > > > I'm using the Visualization API to pull data from a Google
> > > > > > > Spreadsheet. I then need to turn the DataTable that is returned
> > > into
> > > > > > > JSON. I tried using the toJSON() function, but after I do this, I
> > > > > > > can't seem to access any members of the JSON object. I don't think
> > > > > > > this function works, and I don't really want to use it anyway. I
> > > would
> > > > > > > prefer to use the labels (column headings) as property names.
>
> > > > > > > I want to be able to create a JSON object with the following
> > > format,
> > > > > > > but startTime, endTime etc. would change depending on the
> > > spreadsheet
> > > > > > > that I am reading from. I don't want this to be tied to a specific
> > > > > > > spreadsheet or format.
>
> > > > > > > {
> > > > > > > "rows": [
> > > > > > > {
> > > > > > > "startTime": "8:30 AM",
> > > > > > > "endTime": "2:00 PM",
> > > > > > > "title": "CASE District VI Board Meeting",
> > > > > > > "location": "Pershing Place North"
> > > > > > > }
> > > > > > > {
> > > > > > > "startTime": "8:30 AM",
> > > > > > > "endTime": "2:00 PM",
> > > > > > > "title": "CASE District VI Board Meeting",
> > > > > > > "location": "Pershing Place North"
> > > > > > > }
> > > > > > > ]
> > > > > > > }
>
> > > > > > > How can I create this from the DataTable?
>
> > > > > > > Thanks.
>
> > > > > > > --
> > > > > > > You received this message because you are subscribed to the Google
> > > > > Groups
> > > > > > > "Google Visualization API" group.
> > > > > > > To post to this group, send email to
> > > > > > > [email protected].
> > > > > > > To unsubscribe from this group, send email to
> > > > > > > [email protected]<google-visualization-
> > > > > > > api%[email protected]><google-visualization-
> > > api%[email protected] <api%[email protected]>
> > > ><google-visualization-
> > > > > api%[email protected]<api%[email protected]><
> > > api%[email protected]<api%[email protected]
> > > >
>
> > > > > > > .
> > > > > > > For more options, visit this group at
> > > > > > >http://groups.google.com/group/google-visualization-api?hl=en.
>
> > > > > --
> > > > > You received this message because you are subscribed to the Google
> > > Groups
> > > > > "Google Visualization API" group.
> > > > > To post to this group, send email to
> > > > > [email protected].
> > > > > To unsubscribe from this group, send email to
> > > > > [email protected]<google-visualization-
> > > > > api%[email protected]><google-visualization-
> > > api%[email protected] <api%[email protected]>>
> > > > > .
> > > > > For more options, visit this group at
> > > > >http://groups.google.com/group/google-visualization-api?hl=en.
>
> > > --
> > > You received this message because you are subscribed to the Google Groups
> > > "Google Visualization API" group.
> > > To post to this group, send email to
> > > [email protected].
> > > To unsubscribe from this group, send email to
> > > [email protected]<google-visualization-
> > > api%[email protected]>
> > > .
> > > For more options, visit this group at
> > >http://groups.google.com/group/google-visualization-api?hl=en.
--
You received this message because you are subscribed to the Google Groups
"Google Visualization API" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/google-visualization-api?hl=en.
