How to add annotation to a Google Chart where dataset comes from Google Sheet

[Travel Berlin]

Hello everyone,

I would like to add some annotations to my personal chart -which is a line chart by the way- as shown here:

https://developers.google.com/chart/interactive/docs/roles#annotationrole

Now, that doesnt seem hard at all; except that in my case the chart takes its data from a Google Sheet, so I am not using this kind of construction where I can manually define columns

var data = new google.visualization.DataTable();
data.addColumn('string', 'Month'); // Implicit domain label col.
data.addColumn('number', 'Sales'); // Implicit series 1 data col.
data.addColumn({type:'number', role:'interval'});  // interval role col.
data.addColumn({type:'number', role:'interval'});  // interval role col.
data.addColumn({type:'string', role:'annotation'}); // annotation role col.
data.addColumn({type:'string', role:'annotationText'}); // annotationText col.
data.addColumn({type:'boolean',role:'certainty'}); // certainty col.
data.addRows([
    ['April',1000,  900, 1100,  'A','Stolen data', true],
    ['May',  1170, 1000, 1200,  'B','Coffee spill', true],
    ['June',  660,  550,  800,  'C','Wumpus attack', true],
    ['July', 1030, null, null, null, null, false]
]);



but rather THIS one where I don't know ho to proceed:



// Load the Visualization API and the corechart package.
google.charts.load('current', {'packages':['corechart']});

// Set a callback to run when the Google Visualization API is loaded.
google.charts.setOnLoadCallback(drawGID);

function drawGID() {

var query = new google.visualization.Query(
'https://docs.google.com/spreadsheets/d/***/gviz/tq?gid=0&sheet=****');
query.setQuery('select A, H, P, Q, R, S, T, U, E, F, G limit 40 offset 2');
query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
} 

// Set chart options
 var options = {'title':'Mon graphique',
 'width':'1300',
 'height':'1000',
 'hAxis':{'title':'time (mois)'},
 'vAxis':{'title':'price'}
 };// fine options
 
var data = response.getDataTable();
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
chart.draw(data, options);
}

We have tried on stackoverflow already but they say it's a duplicate from a previous answer already, which is not 
in my opinion.

any help would be appreciated, thanks

[Daniel LaLiberte]

WhiteHat's answer is correct, though a bit confusing for your particular use.  I added this comment:  "The one difference in WhiteHat's answer regarding where your data comes from, whether locally defined or via a spreadsheet query, doesn't matter because the solution is independent of that. Focus on how a DataView is used to change the role of a column of (string) data in your DataTable into a column used for annotations."

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/0fcb9365-c379-423a-9c04-1779779253ca%40googlegroups.com.

--

Daniel LaLiberte

dlaliberte@Google.com   Cambridge MA

[Travel Berlin]

Ok, thanks Daniel: I didnt understand a single bit of what you said. I'll try it out to my best and get back to you. thanks

To unsubscribe from this group and stop receiving emails from it, send an email to google-visualization-api+unsub...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/0fcb9365-c379-423a-9c04-1779779253ca%40googlegroups.com.

--

Daniel LaLiberte

dlali...@Google.com   Cambridge MA

[Travel Berlin]

ok, let's try to solve this now. I feel like I am very close to ut but there must be someting I am missing.

First of all, I've been able to implement dataview in the code and I am very happy with that since I wasn't sure I would be able to.

The problem is I really can't depict annotations correctly within the chart.

let's go step by step following the code.

This is the whole code I am using

google.charts.load('current', {'packages':['corechart']});

google.charts.setOnLoadCallback(drawGID);

function drawGID() {



var query = new google.visualization.Query(

'https://docs.google.com/spreadsheets/d/1-j2QlD8lXvmWQJAyjWRtHO3PSYurCSjbYkn_5K5pBj0/gviz/tq?gid=0&sheet=dca'/* + queryString*/);
//I chose to draw only a small part of the sheet, for the sake of simplicity
query.setQuery('select A, I limit 40 offset 2');

query.send(handleQueryResponse);
} //fine funzione drawGID

function handleQueryResponse(response) {
if (response.isError()) {
alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
return;
}


// Set chart options

        var options = {'title':'Ceci n est pas un graphique',

                       'width':'1300',
                       'height':'1000',

                       'hAxis':{'title':'time (mesi)'},
                       'vAxis':{'title':'price'},
                       'lineWidth':'2',
                       explorer: { actions: ['dragToZoom', 'rightClickToReset'], maxZoomIn: .01}

                       };// fine options
 
var data = response.getDataTable();

//da qui proviamo dataview
    var view = new google.visualization.DataView(data);
    view.setColumns([0,1/*,
      2/*,
      {
        // calc: 'stringify',
        // type: 'string',
        sourceColumn: 2,
        role: 'certainty'
      }/*,
      3,
      {
        calc: "stringify",
        sourceColumn: 3,

        type: "string",
        role: "annotationText"

      }*/
    ]);

var chart = new google.visualization.LineChart(document.getElementById('chart_div'));

chart.draw(view, options/*{ height: 800 }*/);
} //fine funzione handleQueryResponse

and this is the sheet we'll be referring to:

https://docs.google.com/spreadsheets/d/1-j2QlD8lXvmWQJAyjWRtHO3PSYurCSjbYkn_5K5pBj0/edit#gid=1892761984

Now, if as you can see we try to draw ONLY two columns (A, I) it works fine: 

this means that dataview works.

Now, if I try to include column AT (see the linked sheet ) as annotation column what happens is a blank page

this is the code I use (I'll post only the variations):

var query = new google.visualization.Query(

'https://docs.google.com/spreadsheets/d/1-j2QlD8lXvmWQJAyjWRtHO3PSYurCSjbYkn_5K5pBj0/gviz/tq?gid=0&sheet=dca'/* + queryString*/);
query.setQuery('select A, I, AT limit 40 offset 2');

//query.setQuery('select A, H, P, Q, R, S, T, U, E, F, G limit 40 offset 2');
query.send(handleQueryResponse);


//da qui proviamo dataview
    var view = new google.visualization.DataView(data);
    view.setColumns([0,1,
      2,
      {
 // calc: 'stringify',
 // type: 'string',
        sourceColumn: 2,
        role: 'annotation'
      }
    ]);

You may think that the problem is that I commented 

 // calc: 'stringify',
 // type: 'string',

but it' not of course because even if you uncomment them nothing changes.

And, besides, if you look at the reference from google itself it's specified that in the presence of sourceColumn you don't need to specify neither the calc command nor the type:

sourceColumn - [Optional, number] The source column to use as a value; if specified, do not specify the calc or the type property. This is similar to passing in a number instead of an object, but enables you to specify a role and properties for the new column.             

 

Then, as a third outcome, if I try to pass a simple column (AR) and mark it as certainty (which is absolutely nonsense), it somehow manages to depict it, but of course without any real usability:

var query = new google.visualization.Query(

'https://docs.google.com/spreadsheets/d/1-j2QlD8lXvmWQJAyjWRtHO3PSYurCSjbYkn_5K5pBj0/gviz/tq?gid=0&sheet=dca'/* + queryString*/);

query.setQuery('select A, I, AR limit 40 offset 2');

//query.setQuery('select A, H, P, Q, R, S, T, U, E, F, G limit 40 offset 2');

query.send(handleQueryResponse);

//da qui proviamo dataview

    var view = new google.visualization.DataView(data);

    view.setColumns([0,1,

      2,

      {

// calc: 'stringify',

// type: 'string',

        sourceColumn: 2,

        role: 'certainty'

      }

    ]);

now: what am I missing?

thanks

[Daniel LaLiberte]

You are missing the fact that the array of columns corresponds one-to-one with the columns you want in your dataview.  You have:  

[0,1,

      2,

      { ...}]

Which is 4 columns.  The "2," means you are including column 2 as is, not with role: 'annotation', so it will try to draw the chart with strings for your data, but that fails.   If you look in your browser debugger console, you'll see some error message about not all the columns being the same type, or bad type (I'm not sure which error it reports).  So remove the "2," and things will start to look better.

--

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 view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/8732e176-6a7f-4a0f-970a-b2fb8fc44ab7%40googlegroups.com.

[Travel Berlin]

hey man, I never thought that as in Italian I would say that to a French, but you did great! thanks!! :)

I didn't realize that any object after a comma was considered a new column, instead I thought that the specifications inside the {} were just details of the  preceding column index expressed outside (index number two in this case). Instead it was a completely new column.

I was not sure infact that a comma could be used after a column number without starting a new element of the array, but I convinced myself it had to be like that because I saw it in many other examples.

really thanks again French man :)

[Daniel LaLiberte]

Cool.  Glad you got it working.  The notation, by the way, is just JavaScript data structures, so you can find out more about what various things mean from JS documentation.

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/google-visualization-api/a8ce1444-45e3-41c3-bf6a-bae18f3fa24b%40googlegroups.com.