CODEWITHSUNDEEP
javascriptgoogle-apps-scriptchartsgoogle-visualization
Joe
Joe

Reputation: 15

How to embed a dynamic annotated timeline chart directly into Google Sheets

Google Sheets only offers one timeline chart in the stock offerings - I had located the timelines chart option through google.visualizations that would take care of what I am looking for, except I am not trying to publish this externally - I want to build this in my current report but cannot for the life of me figure out how to integrate the sample code through scripts. (Timeline Reference: https://developers.google.com/chart/interactive/docs/gallery/timeline).

I have only attempted to utilize the sample code just to see if this worked but had no luck. I also attempted to remove the .addrows section to dynamically update the datatable with a range from the sheet but I don't think I entered this correctly either. INCLUDED BELOW is the sample code

    <html>
      <head>
        <script type="text/javascript"                 
        src="https://www.gstatic.com/charts/loader.js"></script>
        <script type="text/javascript">
          google.charts.load('current', {'packages':['timeline']});
          google.charts.setOnLoadCallback(drawChart);
          function drawChart() {
            var container = document.getElementById('timeline');
            var chart = new google.visualization.Timeline(container);
            var dataTable = new google.visualization.DataTable();

            dataTable.addColumn({ type: 'string', id: 'President' });
            dataTable.addColumn({ type: 'date', id: 'Start' });
            dataTable.addColumn({ type: 'date', id: 'End' });
            dataTable.addRows([
              [ 'Washington', new Date(1789, 3, 30), new Date(1797, 2, 4) ],
              [ 'Adams',      new Date(1797, 2, 4),  new Date(1801, 2, 4) ],
              [ 'Jefferson',  new Date(1801, 2, 4),  new Date(1809, 2, 4) ]]);

            chart.draw(dataTable);
          }
        </script>
      </head>
      <body>
        <div id="timeline" style="height: 180px;"></div>
      </body>
    </html>

I expected to create a horizontally scrollable timeline chart that includes annotation on the data entry - The chart should be populated from a query I am running on sheet 2 - this query populates a Term Start & Term End date (the paramaters for an entry in the chart), an identifier for the data, and a notes section (for annotation).

Upvotes: 1

Views: 795

Answers (1)

WhiteHat
WhiteHat

Reputation: 61285

you can use the query class to pull data from a sheet --> google.visualization.Query

see following working snippet... 

google.charts.load('current', {
  packages:['timeline', 'table']
}).then(function () {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1bIydJJY_-H9NHHhxca9U-jNmKjYUkln14v20N7klAGg/edit?usp=sharing');
  query.setQuery('select E,A,C');
  query.send(handleQueryResponse);

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

    var container = document.getElementById('timeline');
    var chart = new google.visualization.Timeline(container);
    var dataTable = response.getDataTable();
    chart.draw(dataTable);
  }
});
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="timeline"></div>

Upvotes: 1

Related Questions