How to create an annotated line graph in Google Sheets

line graph in Google Sheets
Animated line chart in Google Sheets

This post looks at how to create a more advanced line graph in Google Sheets, with comparison lines and annotations, so that the viewer can absorb the maximum amount of insight from a single chart.

For fun, I’ll also show you how to animate this line graph in Google Sheets.

Want your own copy of this line graph?

Click here to access your copy of this template >>

This chart was originally developed for The Write Life during their 4-day product sale earlier this year. It featured as part of a dashboard that was linked to the E-junkie sales platform and displayed sales data in real-time:

Google Sheet e-junkie real-time dashboard
Google Sheet e-junkie real-time dashboard

As with any graph, we start with the data:

The data table

Line graph data table

The key to this line graph in Google Sheets is setting up the data table correctly, as this allows you to show an original data series (the grey lines in the animated GIF image), progress series lines (the colored lines in the animated GIF) and current data values (the data label on the series lines in the GIF).

In this example, I have date and times as my row headings, as I’m measuring data across a 4-day period, and sales category figures as column headings, as follows:

Annotated line graph data table

Red columns

The red column, labeled with 1 above, contains historic data from the 2015 sale.

Red column 2 is a copy of the same data but only showing the progress up to a specific point in time.

In red column 3, the following formula will create a copy of the last value in column 2, which is used to add a value label on the chart:

=IF(AND((C2+C3)=C2,C2<>0),C2,"")

Purple columns:

Purple columns 4,5 and 6 are exactly the same but for 2016 data. The formula in this case, in column 6, is:

=IF(AND((F2+F3)=F2,F2<>0),F2,"")

Green columns:

Data in green columns 7 and 8, is our current year data (2017), so in this case there is no column of historic data. The formula in column 8 for this example is:

=IF(AND((H2+H3)=H2,H2<>0),H2,"")

Creating the line graph in Google Sheets

Highlight your whole data table (Ctrl + A if you’re on a PC, or Cmd + A if you’re on a Mac) and select Insert > Chart from the menu.

In the Recommendations tab, you’ll see the line graph we’re after in the top-right of the selection. It shows the different lines and data points, so all that’s left to do is some formatting.

Line graph selection

Format the series lines as follows:

  • For the historic data (columns 1 and 4 in the data table), make light grey and 1px thick
  • For the current data (columns 2, 5 and 7 in the data table), choose colors and make 2px thick
  • For the “max” values (columns 3, 6 and 8 in the data table), match the current data colors, make the data point 7px and add data label values (see steps 1, 2 and 3 in the image below)

Line graph data labels

This is the same technique I’ve written about in more detail in this post:

How can I annotate data points in Google Sheets charts?

Animating the chart with Apps Script

How about creating an animated version of this chart?

Oh, go on then.

When this script runs, it collects the historic data, then adds that data back to each new row after a 10 millisecond delay (achieved with the Utilities.sleep method and the SpreadsheetApp.flush method to apply all pending changes).

I don’t make any changes to the graph or create any fancy script to change it, I leave that up to the Google Chart Tool. It just does its best to keep up with the changing data, although as you can see from the GIF at the top of this post, it’s not silky smooth.

By the way, you can create and modify charts with Apps Script (see this waterfall chart example, or this funnel chart example) or with the Google Chart API (see this animated temperature chart). This may well be a better route to explore to get a smoother animation, but I haven’t tried yet…

Here’s the script:

function startTimedData() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Animated Chart');
  var lastRow = sheet.getLastRow()-12;
  
  var data2015 = sheet.getRange(13,2,lastRow,1).getValues(); // historic data
  var data2016 = sheet.getRange(13,5,lastRow,1).getValues(); // historic data
  
  // new data that would be inputted into the sheet manually or from API
  var data2017 = [[1],[7],[14],[19],[27],[32],[34],[36],[44],[49],[57],[65],[72],[76],[79],[86],[92],[99],[104],[109],[111],[112],[120],[128],[130],
                  [132],[133],[140],[144],[149],[151],[152],[158],[162],[170],[177],[179],[184],[188],[194],[200],[205],[211],[216],[224],[232],[238],
                  [241],[246],[248],[252],[259],[266],[268],[276],[284],[291],[299],[300],[301],[306],[311],[315],[316],[323],[324]];
  
  for (var i = 0; i < data2015.length;i++) {
    outputData(data2015[i],data2016[i],data2017[i],i);
  }
  
}

function outputData(d1,d2,d3,i) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Animated Chart');
  
  sheet.getRange(13+i,3).setValue(d1);
  sheet.getRange(13+i,6).setValue(d2);
  sheet.getRange(13+i,8).setValue(d3);
  Utilities.sleep(10);
  SpreadsheetApp.flush();
}

function clearData() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Animated Chart');
  var lastRow = sheet.getLastRow()-12;
  
  sheet.getRange(13,3,lastRow,1).clear();
  sheet.getRange(13,6,lastRow,1).clear();
  sheet.getRange(13,8,lastRow,1).clear();
  
}

On lines 6 and 7, the script grabs the historic data for 2015 and 2016 respectively. For the contemporary 2017 data, I’ve created an array in my script to hold those values, since they don’t exist in my spreadsheet table.

This code is available here on GitHub.

Finally, add a menu for access from your Google Sheet with the following code:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  
  ui.createMenu("Timed data")
    .addItem("Start","startTimedData")
    .addItem("Clear","clearData")
    .addToUi();
}

This allows you to run the Start and Clear functions directly from your Google Sheet browser tab, rather than the script editor tab.

That’s it. Hit Start and you should see your chart animate before your eyes:

Animated Apps Script chartAnimated Apps Script chart

If you look closely, you’ll also see the data populating your sheet.

How to create a waterfall chart in Google Sheets

Update December 2017: Google have recently added Waterfall Charts to the native charts in the Chart Tool of Google Sheets, obviating the need for you to manually create your waterfall charts (or use apps script) per my original post.

Now you simply highlight your data, click Insert > Chart and under the Chart type picker choose “waterfall”, as shown in the following image:

New native waterfall charts in Google Sheets


The original post that follows was first published in late 2016, and I’m leaving it here for anyone who wants to look under the hood at how waterfall chart data is constructed and how to do that using apps script.


Original article:

In this post, we’ll look at how to create a waterfall chart in Google Sheets.

Waterfall charts are real. And useful. They show the cumulative effect of a series of positive and/or negative values on an initial starting value.

The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value:

Headcount Waterfall Chart

It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar).

The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below).

I’ll show you how to create both of these cases, starting with the easier, positive-bar case.

After creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts.

Templates are available for all three methods, with links at the end of each section and at the end of this post.
Continue reading How to create a waterfall chart in Google Sheets

Animated Data Visualization of Washington D.C.’s warming temperatures

I love animated charts where the animation is central to the storytelling; where it adds value, rather than being simply fancy decoration.

When it’s done well, it can be extremely powerful, mesmerizing and compelling.

One of the best examples I’ve seen is Bloomberg’s animated temperature visualization. I love how powerfully this chart conveys its message, and I’ve been semi-obsessed with it ever since I saw it.

So, over the past few weeks and since my last experiment with the Google Visualization API, I’ve been chipping away at this project to create an animated temperature chart within the Google environment. It’s finally in a state I can share here, however it’s what you might call an MVP still…

Here’s my animated temperature chart built with the Google Visualization API connecting to Washington D.C. data in a Google Sheet:

Animated temperature chart using Google Chart API

You can see this animated chart in all its glory here.

The original Bloomberg temperature chart

Here’s the original animated visualization from Bloomberg – one of my favorite visualizations – that inspired this whole exercise.

I find it mesmerizing to watch and tremendously effective at illustrating the trend of rising temperatures.

I make no claim that my chart is a patch on Bloomberg’s sumptuous, smoothly animated chart with its incredible attention to detail (click through to the live version to see what I mean).

Hopefully though, my version captures some of the same spirit and demonstrates how powerful Google’s Visualization library can be.

Another example: NASA’s animated chart

I saw this chart from NASA show up in my Twitter feed recently and I couldn’t help but smile in appreciation.

Another beautiful, animated chart showing long-term temperature trends. This arrived as a timely dose of inspiration to push me to persevere with my own attempt.

NASA released this last month, showing that “August 2016 was the warmest August in 136 years of modern record-keeping”. Wowser!

I wonder if the authors of this chart were similarly inspired by the Bloomberg chart?

Creating an animated temperature chart with Google Sheets and the Google Chart API

Ok, so is it possible to create something similar with data in a Google Sheet?

Yes, but it involves coding with Google’s Visualization API.

The data is the same that I used for my other Google Visualization API project, which I documented here.

It comes from the University of Dayton: http://academic.udayton.edu/kissock/http/Weather/citylistUS.htm

I’ve put all of the data cleaning/wrangling in my previous post, so I won’t repeat it here.

Suffice to say, I did create a new pivot table with average monthly and average annual temperatures. Here’s the data in a Google Sheet, ready to go. It shows the average monthly temperature for each year from 1995 to 2016, along with an annual average.

Onwards with the Google Visualization API…

After plenty of head-scratching, reading documentation and trying different examples, I finally managed to create an initial, working prototype that cycled through the temperature data in my Google Sheet, comparing a baseline (1995) to each successive year:

First working prototype of the animated temperature chart

It was a start, even if it didn’t show much!

After another bout of head-scratching and staring gormlessly into space, I was a little closer:

Animated temperature chart progress

After another bout of…you get the gist.

I added the year in the top left and removed the legend. Also, you’ll notice in this next GIF that I’ve got the Chrome Dev Tools open so I can see the objects being created at each iteration of the loop. This helped me massively when figuring out how to build the Series object dynamically.

Animated temperature chart progress

Getting much closer now!

The last major hurdle was building the series options object dynamically within the loop, so I could control the colors of each series.

This allowed me to set the most recent monthly data to red, the most recent annual average to grey, and then have everything else fade into the background with muted colors.

After a final few tweaks, I had the chart I was after:

Animated temperature chart using Google Chart API

I’m not going to go through the code step-by-step, but it’s all here on GitHub.

I will however highlight a few interesting parts of the code, which were key to making this work.

Very briefly, the code collects all of the data from the Google Sheet and initially shows only the first two columns of data, the 1995 average line and the 1995 monthly line.

I used the DataView class to show only specific columns of data in the chart, e.g. the initial chart view is created from the data in this snippet:

var data = response.getDataTable();
var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
var view = new google.visualization.DataView(data);

// set columns initially
view.setColumns([0,1,2]);
...

Then I loop through the remaining columns, adding them one at a time to the view and refreshing the chart on each iteration:

function updateChart() {
    if (i < howManyTimes ){
      arr.push(i+2)
    }
    view.setColumns(arr);
...

The color of the lines is controlled by the options object, which I build dynamically within each loop. Modulus arithmetic is used to distinguish odd and even columns (to distinguish between monthly and annual data) and color them accordingly:

for (var j = 0; j < i; j++) {
  var innerObj = {};
  if ((j % 2) == 0) {
    innerObj["color"] = '#e9e9e9';
    innerObj["lineDashStyle"] = [4,1];
  }
  else {
    innerObj["color"] = '#ffdbdb';
    innerObj["lineDashStyle"] = [0,0];
  }
  seriesObj[j] = innerObj;
}
...

The other key feature is a timer that refreshes the chart on every iteration of the loop, with an 80 millisecond buffer:

if( i < howManyTimes ){
    setTimeout( updateChart, 80 );
}

The full code can be seen here on GitHub.

Documentation

Should you want to create something with the Chart API yourself, here's some resources relating to the techniques discussed above:

Google Visualization API reference
DataTable class
DataView class
Line chart examples with the Visualization API
Animation examples with the Visualization API
And finally, the Google Visualization API Forum

Recreating Tufte’s famous weather chart with Google Sheets and Charts API

My Twitter feed is mostly populated with data-related resources and people, so I almost always find something new and shiny going on there to distract me.

Sometimes this can be a good thing though (and sometimes, it can be the best thing ever).

Take last Friday. I found myself procrastinating, browsing my home feed when I saw this tweet for the upcoming Microsoft Data Science summit:

Tufte temp chart tweet

What caught my eye was not the actual tweet, but the sumptuous weather visualization that I recognized as one of Edward Tufte’s famous creations.

I clicked through to the article, which linked to Tufte’s original chart (here) and a fantastic tutorial about re-creating the chart using R, from Brad Boehmke (which is actually the chart shown in the tweet above).

It looks at daily average temperature over a period of time and captures the historic min/max range, the normal range (95% confidence interval), the 2014 temperature line and picks out the highs and lows. It renders all of this information in a single, beautiful column/line combination chart.

Curiousity piqued, I wondered whether this would be possible with Google Sheets?

The short answer is….. Continue reading Recreating Tufte’s famous weather chart with Google Sheets and Charts API

Web traffic chart with dynamic banding in Google Sheets

This is a simple but effective technique for adding dynamic bands to your charts, which are useful to highlight specific parts of your chart.

For example, in this chart of website pageviews, I’ve added bands to show weekdays or weekends and make it easier to see the changing trends.

Chart with dynamic banding

Continue reading Web traffic chart with dynamic banding in Google Sheets