Google Charts dataview manipulation

743 views
Skip to first unread message

Th Mrckx

unread,
Oct 30, 2015, 4:51:42 PM10/30/15
to Google Visualization API
Hi Google Vis community,

I am trying to make a visual representation (using charts) of some data I have in a google spreadsheet.
This data will be embedded on a website using html. 
I get to the point where I can see the data from the spreadsheet. So far, so good.

Trouble begins when I try to adjust my dataTable to a dataView and alter that dataview to fit the charts specific needs.
What I'm basically looking for is a way to transpose and then filter data so only certain columns (or rows) remain.

To try and be as specific as possible I added a small portion of my data in this google spreadsheet on the sheet named 'original'.
What I want to achieve by transposing is found on the sheet named 'transposed' and finally you can see the data that has to remain as well as the chart I'm trying to create.

I hope I'm clear on my problem. If anything remains unclear, please let me know.

Thanks for any assistance!


Daniel LaLiberte

unread,
Nov 4, 2015, 8:39:54 AM11/4/15
to Google Visualization API
Sorry Th, DataViews cannot do the transpose operation on the data.  You'd have to write a loop to do that yourself, or perhaps use a Query with the pivot clause:  https://developers.google.com/chart/interactive/docs/querylanguage#pivot

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/cce6bffb-764c-461e-80ff-cd084449d4be%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.



--
dlaliberte@Google.com   5CC, Cambridge MA
daniel.laliberte@GMail.com 9 Juniper Ridge Road, Acton MA

Th Mrckx

unread,
Nov 17, 2015, 3:43:09 PM11/17/15
to Google Visualization API
Hello Daniel,

thank you for the quick reply and excuse my belated response.

I was wondering how to write such a loop on a dataview. I already found out it doesn't exactly work as an array (I have some basic javascript knowledge).
If any good information exists on how to understand and manipulate the dataview, I would greatly appreciate you pointing me in the correct direction.

Kind regards and once again thanks in advance
Th

Op woensdag 4 november 2015 14:39:54 UTC+1 schreef Daniel LaLiberte:
Sorry Th, DataViews cannot do the transpose operation on the data.  You'd have to write a loop to do that yourself, or perhaps use a Query with the pivot clause:  https://developers.google.com/chart/interactive/docs/querylanguage#pivot
On Fri, Oct 30, 2015 at 4:51 PM, Th Mrckx <thijs....@gmail.com> wrote:
Hi Google Vis community,

I am trying to make a visual representation (using charts) of some data I have in a google spreadsheet.
This data will be embedded on a website using html. 
I get to the point where I can see the data from the spreadsheet. So far, so good.

Trouble begins when I try to adjust my dataTable to a dataView and alter that dataview to fit the charts specific needs.
What I'm basically looking for is a way to transpose and then filter data so only certain columns (or rows) remain.

To try and be as specific as possible I added a small portion of my data in this google spreadsheet on the sheet named 'original'.
What I want to achieve by transposing is found on the sheet named 'transposed' and finally you can see the data that has to remain as well as the chart I'm trying to create.

I hope I'm clear on my problem. If anything remains unclear, please let me know.

Thanks for any assistance!


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.



--
dlali...@Google.com   5CC, Cambridge MA
daniel.l...@GMail.com 9 Juniper Ridge Road, Acton MA

Daniel LaLiberte

unread,
Nov 17, 2015, 4:57:56 PM11/17/15
to Google Visualization API
A transpose on a datatable (or dataview) requires that you generate a new datatable (not a dataview) and then set up a double nested loop to iterate through your old data and add it to the new datatable.  It would look basically like this (in pseudo code):

var oldData = some datatable or dataview.
var newData = new google.visualization.DataTable()

for each column of oldData
  for each row of oldData
    var value = oldData.getValue(row, column)
    newData.setValue(column, row, value)

Notice that the row and column indices are swapped from the old data to the new data.
This is probably not correct, and not optimal.  It would probably be better to set up a JavaScript array of arrays, and then construct the newData using arrayToDatatable() instead of adding each value one at a time.  Hope that helps.


On Tue, Nov 17, 2015 at 3:43 PM, Th Mrckx <thijs....@gmail.com> wrote:
Hello Daniel,

thank you for the quick reply and excuse my belated response.

I was wondering how to write such a loop on a dataview. I already found out it doesn't exactly work as an array (I have some basic javascript knowledge).
If any good information exists on how to understand and manipulate the dataview, I would greatly appreciate you pointing me in the correct direction.

Kind regards and once again thanks in advance
Th

Op woensdag 4 november 2015 14:39:54 UTC+1 schreef Daniel LaLiberte:
Sorry Th, DataViews cannot do the transpose operation on the data.  You'd have to write a loop to do that yourself, or perhaps use a Query with the pivot clause:  https://developers.google.com/chart/interactive/docs/querylanguage#pivot
On Fri, Oct 30, 2015 at 4:51 PM, Th Mrckx <thijs....@gmail.com> wrote:
Hi Google Vis community,

I am trying to make a visual representation (using charts) of some data I have in a google spreadsheet.
This data will be embedded on a website using html. 
I get to the point where I can see the data from the spreadsheet. So far, so good.

Trouble begins when I try to adjust my dataTable to a dataView and alter that dataview to fit the charts specific needs.
What I'm basically looking for is a way to transpose and then filter data so only certain columns (or rows) remain.

To try and be as specific as possible I added a small portion of my data in this google spreadsheet on the sheet named 'original'.
What I want to achieve by transposing is found on the sheet named 'transposed' and finally you can see the data that has to remain as well as the chart I'm trying to create.

I hope I'm clear on my problem. If anything remains unclear, please let me know.

Thanks for any assistance!


--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.



--
dlali...@Google.com   5CC, Cambridge MA
daniel.l...@GMail.com 9 Juniper Ridge Road, Acton MA

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/64f37cbd-7d5a-48af-a06f-ef7b4b4c920c%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.



--
dlaliberte@Google.com   5CC, Cambridge MA
daniel.laliberte@GMail.com 9 Juniper Ridge Road, Acton MA

Daniel LaLiberte

unread,
Nov 17, 2015, 4:59:38 PM11/17/15
to Google Visualization API
I should have thought of this before.   Andrew Gallant wrote some code to do the transpose in an earlier post:  https://groups.google.com/forum/#!searchin/google-visualization-api/transpose$20datatable/google-visualization-api/d_0XLvCEOFs/MIvWrV34AwAJ

Th Mrckx

unread,
Nov 23, 2015, 2:29:56 PM11/23/15
to Google Visualization API
Hi Daniel, 
once again: thank you!

I tried the following (see attached file) taking into account your suggestion of using arrays and avoiding adding elements one after the other. When I open this file in my browser, I don't see anything.
Perhaps you could give me some pointers on a good platform to test my scripts because I feel blind doing this in notepad++ without any logging options...

If you could take another look, I'd appreciate it. 

Thanks in advance
Th

Op dinsdag 17 november 2015 22:59:38 UTC+1 schreef Daniel LaLiberte:
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.



--
dlali...@Google.com   5CC, Cambridge MA
daniel.l...@GMail.com 9 Juniper Ridge Road, Acton MA

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.

To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
dlali...@Google.com   5CC, Cambridge MA
daniel.l...@GMail.com 9 Juniper Ridge Road, Acton MA
test09_charts.html
Reply all
Reply to author
Forward
0 new messages