How to create CSS pie charts in Geckoboard dashboards with data from Google sheets

I’ve been working on an interesting project for a client recently, creating a custom Geckoboard dashboard for sales and financial data from Google spreadsheets. Geckoboard pairs well with Google sheets; it’s a fast and cost-effective way of visualizing your data.

One of the client requests on this project was a pie chart to display the data for sales sources. At the moment, Geckoboard doesn’t come with a pre-built pie chart option for data that comes from Google sheets (full list of Google sheets widgets here). So, unless you build a custom back-end widget to get data from Google’s API, which is a fairly complex project, you’re out of luck. Hang on a minute though, there is a way!

Geckoboard pie chart with legend
A Geckoboard Text Widget displaying a pie chart with legend

There’s a crafty workaround you can use to create a pie chart in your Geckoboard using a text-widget, which pulls data from a Google spreadsheet and updates dynamically along with the rest of your charts.

TL;DR solution

SHOW ME THE CODE! Got it. You’ll need three things:

  1. A text widget in your Geckoboard dashboard, like this
  2. A google spreadsheet with the data for your pie chart, like this
  3. A custom CSS file, like this

Then it’s simple process to link the Geckoboard text widget to your Google spreadsheet, and upload the custom CSS file:

Creating a Google Sheet text widget pie chart in Geckoboard
Easy as pie!

The full solution

Ok, curious to find out a little more about this process? I’ve laid out the steps I went through to create the pie charts, so hopefully you can use or modify these to your own needs and build your own cool Geckoboard pie charts.

How this pie chart technique works in a Geckoboard dashboard

At the heart of this technique is the humble text widget that Geckoboard offers. It pulls in text data from a single cell of a Google spreadsheet and allows you to include certain HTML tags. This is the crucial piece that allows us to turn the text into a pie chart. We can pass in div tags with class attributes.

Next you’ll need a Google Sheet with your pie chart data. We’ll put all of this data into a single cell, wrap it all in custom HTML div tags and then link Geckoboard to this cell. However, it won’t look much like a pie chart at this stage.

The final piece required is a custom CSS file that contains the code to style these div tags based on the class attributes. This code will turn the div elements into pie chart segments.

If we then change the class names in the google sheet we can make the pie appear “dynamic”, since the custom CSS file contains all the different classes to handle variations in segment size. In other words, if the data changes, the class attributes in the text cell of the Google Sheet change. Then Geckoboard imports the new text with the new class tags, which are then displayed differently by our CSS file and so the pie chart is updated!

Pie charts in pure HTML and CSS

Before we hook up a Google spreadsheet to Geckoboard, let’s create a few pie charts with pure HTML/CSS.

First an acknowledgement of this brilliant article from Lea Verou published recently on Smashing Magazine: Designing Flexible, Maintainable Pie Charts With CSS and SVG, that inspired me to take on this challenge and believe that a solution was possible.

In a serendipitous twist, this article was featured in the Smashing Magazine email newsletter the very morning that I was starting this project. I knew my client wanted a pie chart. I knew Geckoboard didn’t have a pre-built widget, but I had a hunch I could do something with the text widget. This article confirmed that something cool and crafty was possible, and showed me where to start.

Overflow hidden approach

I’ve used a slightly different approach to that described under the “Transform Based Solution” of the Smashing Magazine article I referenced above, using some of the techniques there as well as the idea of nested div elements, which I saw applied in this post from Kyle Larson: How to Create CSS3 Pie Charts.

What I’m essentially doing is creating rectangles, that I round into semi-circles, and then rotate to uncover/hide underlying elements with different colors, giving the visual impression of slices.

To begin I create a basic HTML page which I will use to display my pie chart. I link to my css stylesheet, stylesheet.css, and place it into the same folder as my index HTML file.

With some basic HTML set up, I can now add some styling to my CSS file.

Let’s walk through the CSS in steps, starting with the rectangle that forms the background of our pie:

1. Create an initial rectangle:

(Note: the dashed border has been added for illustration purposes only and doesn’t feature in the code below.)

Add the following between the <body></body> tags of the index.html file:

And add the following div elements to the stylesheet.css file:

The mp-ct div is the outer container for all the other elements of our pie chart. The mp-bg div container is the base element for our pie chart.

Code for this step on GitHub

2. Add left and right containers

(Again, borders added to simply illustrate the elements.)

Here I’ve added a left and right container to divide my pie chart in half, and apply the overflow: hidden; attribute to it. This will prevent any of the slices from bleeding through into the wrong half of the pie chart, as you’ll see below.

Both margins have a width of 50%. The right container has a left-margin of 50% and the left container has a right-margin of 50%, which ensures that they only cover their respective halves of the pie chart:

The index file becomes (new lines in yellow):

and add these classes to the CSS file (all these are new lines):

Code for this step on GitHub

3. Turn the rectangles into circles and add some color

Next, I set the border-radius of the mp-bg element to 50% to turn our square into a circle, and then add an overflow: hidden; attribute. This is incredibly useful here as it’ll clip anything outside the borders of this element. In other words, it’ll now hide any content that lies outside our circle.

I also need to add an position: absolute to ensure that all my pie chart elements remain locked on top of the ancestor element (our pie background in this case).

I add a new div element, mp, and set the size to 100% of the parent div. I add a blue color class and rotate 0 degrees, using the transform method (although this is not strictly necessary, I found it useful to explicitly include a 0 rotation so that I knew the position of this semi-circle).

The index file becomes (new lines in yellow):

and corresponding CSS file:

Code for this step on GitHub

4. Create our first slice

Now the fun part, adding a slice. This is achieved by adding a new orange div element on top of the existing blue half. This lies on top of the blue half, thereby creating the visual effect of a blue slice:

I need to style the new div element (<div class='mp color-orange rt-left-40'></div>)and rotate it to create the blue slice. The new element is rotated 40 degrees around a left centre-point.

I add one new line to my index file, inside the right container, just below the <div class='mp color-blue rt-left-0'></div> line above

And add the following new lines to the bottom of my CSS file, which gives me the orange color class and a 40 degree rotation class to match the HTML code above:

Notice how the right container clips the content because of the overflow hidden setting, so that these slices only shows in the right half of the pie chart.

Code for this step on GitHub

5. Add another slice

Why not!

As per step 4 above, I need to add one new line of code to my index file:

And two more new lines to the bottom of my CSS file:

Code for this step on GitHub

6. Let’s look at the other half

Time to put our right container to one side and focus on the left half of the pie chart:

The HTML code in my index file to create this half pie is:

And the corresponding CSS file:

Code for this step on GitHub

7. Add a slice to this half

It’s almost exactly the same idea as above, with only a small adjustment needed in our CSS, shown below.

I add one new line to my index file from step 6, inside the left container:

And add to the CSS file, note that we’re now rotating about the centre-point, but it’s to the right side of our half container this time, not the left:

Code for this step on GitHub

8. Bring the two halves together

We now have div elements in the right and left containers:

And the relevant classes in our CSS file are now:

Code for this step on GitHub

9. Yes, but what about slices that straddle the 180 degree line?

We achieve this by simply adding a slice of the same color to both half containers. It’s easiest to picture them as two separate half pie charts that we then add together, as shown below.

You know what the CSS file looks like by now!

We can use this same technique for slices that are greater than 180 degrees. For example, say your first slice had an angle of 200 degrees, then you simply create one slice of 180 degrees in your right container and then create another slice of the same color and 20 degrees angle, in the left container.

10. The whole enchilada!

Our final HTML/CSS pie chart:

Code for this step on GitHub

Cool huh! That’s the basics of our pie chart sorted.

Last step to prepare the CSS

We still need to account for different angles for our slices so that the pie chart can change “dynamically” with our data.

Thankfully, this is an easy step, achieved by simply adding some additional classes to cover the 180 degrees of possible rotations in the left and right halves (data is rounded to the nearest degree as you’ll see in the Google spreadsheet section).

We change our stylesheet from step 10 and add these additional classes:

Finally, I add some browser-prefixes to these transform properties (e.g. see the example here), to ensure the pie charts will show as expected in different browsers. I’ve added -webkit, -moz and -ms, as follows:

That should cover us, so let’s see what we need to do with the data in our Google spreadsheet.

Setting up the data in Google spreadsheet

Let’s say I have some data from my marketing team showing how many leads we got this month:

Channel A - 3
Channel B - 7
Channel C - 21
Channel D - 19
Channel E - 4
Total - 54

I use the following formula to convert this data into degrees:

=round(val/total * 360,0)

where the val and total are references to cells in your Google sheet containing those values. The dataset now:

Channel A - 3 - 20 degrees
Channel B - 7 - 47 degrees
Channel C - 21 - 140 degrees
Channel D - 19 - 127 degrees
Channel E - 4 - 27 degrees
Total - 54 - 360 degrees

Ok, so now we have some data we can turn into a pie chart using the techniques discussed above. Recall that we’re going to build the HTML div tags for our pie chart in the Google spreadsheet, so that when Geckoboard pulls this text data into the dashboard, it has all the necessary div elements to style the pie chart, per our custom stylesheet, which we upload to Geckoboard as a final step.

As with the pie charts above, the best way to do this is in small, discrete steps:

  1. Calculate the cumulative degrees of the slices from 0 through to 360
  2. Decide if the slice is entirely in the right half, entirely in the left half, or straddles both
  3. Decide the start and end angles for slices in the right half
  4. Decide the start and end angles for slices in the left half
  5. Set the color of each slice
  6. Calculate where the div elements for the right and left containers lie
  7. Calculate the angle of each slice
  8. Create the div tags for each slice, based off the above steps
  9. Concatenate all the div tags together in one cell, for Geckoboard

It’s much easier to look at these steps in this example spreadsheet I’ve set up here. There’s probably more efficient ways of combining all of this info, but it’s good to see it in steps. I’ve included 2, 3, 4 and 5 segment pie chart calculations, but the technique could easily be extended to 6 or more slices.


The following formulas assume that our dataset from above occupies columns A to C and the cumulative degrees data is in column D.

To decide if the slice is in the left or right half of the pie chart, or straddles both, I have this formula in column E:


To calculate the start angle of slice in right half, I have this formula in column F:


To calculate the end angle of the slice in right half, I have this formula in column G:


To calculate the start of any slices in the left half of the pie, i.e. if they are greater than 180 degrees, I have this formula in column H:


To calculate the end of any slices in the left half of the pie, I have this formula in column I:


The opening div tag that I need to prepend onto my text:

<div class='mp-ct'><div class='mp-bg'><div class='hold-right'>

The tag at the 180 mark, which closes the right container div, and opens the left container div:

</div><div class='hold-left'>

The closing div tag that I need to append to the end of my text:


Next, the formula to bring all of this info together, and add in the div parts to create our HTML code:

=K2&if(not(N2=""),"<div class='mp "&J2&" "&N2&"'></div>","")&L2&if(not(O2=""),"<div class='mp "&J2&" "&O2&"'></div>","")&M2

Putting this all together, our final formula simply concatenates all the slice divs, using the concatenate formula (where P2,P3,P4,… are references to the cells with content in):


which then gives this rather monstrous-looking string (!):

<div class='mp-ct'><div class='mp-bg'><div class='hold-right'><div class='mp color-blue rt-left-0'></div><div class='mp color-orange rt-left-40'></div><div class='mp color-yellow rt-left-100'></div></div><div class='hold-left'><div class='mp color-yellow rt-right-0'></div><div class='mp color-green rt-right-30'></div><div class='mp color-red rt-right-60'></div></div></div></div>

Despite its hefty appearance, it’s just the same few lines of HTML code that we had in our index file from step 10 earlier in this post, which contains all the information needed for the CSS.

Linking to Geckoboard

The final piece is to set up a text widget in Geckoboard, and link it to the text cell in your Google sheet.

I start by connecting Geckoboard to my Google sheet (instructions here) and then adding the text widget:

Geckoboard text widget

which, when you hit save, will look blank at first like this:

Blank Geckoboard text widget

It’s blank because we haven’t added the custom CSS stylesheet yet, and so all those div elements are doing nothing at the moment, so let’s fix that here:

Settings > Dashboard Settings > Theme: Custom CSS > Custom CSS

Geckoboard dashboard settings menu

Clicking Custom CSS will allow you to upload your stylesheet containing the CSS we created above.

Click save and….

Voila! The final version

You should now have a beautiful pie chart in place of the blank text widget. Magic!

Geckoboard Pie Chart

Legend anybody?

Let’s do it. We use a simple unordered list in HTML, which contains our pie chart data-points:

  • Channel A: 3 (6%)
  • Channel B: 7 (13%)
  • Channel C: 21 (39%)
  • Channel D: 19 (35%)
  • Channel E: 4 (7%)

The full HTML code for the legend is:

<div id='legend'><ul><li id='square1'><span>Channel A: 3 (6%)</span></li><li id='square2'><span>Channel B: 7 (13%)</span></li><li id='square3'><span>Channel C: 21 (39%)</span></li><li id='square4'><span>Channel D: 19 (35%)</span></li><li id='square5'><span>Channel E: 4 (7%)</span></li></ul></div>

This needs to be appended to the main text cell of our Google spreadsheet that we’ve linked Geckoboard to.

I’ve given each of the li items an id, so that I can change the color of them in the CSS. I wrap the text in <span></span> tags, which re-apply the black color to the text, so only the list bullet points change color. Lastly I change the round bullet points to squares, to make it look more like a legend.

The new code for our CSS file is as follows (and don’t forget to then upload this file to Geckoboard again):

View the final stylesheet with a legend here on GitHub.

The result, showing examples of pie charts with different numbers of slices:

Pie chart examples


It would be remiss of me to not mention how this technique shows up on mobile. Geckoboard provides a native app for viewing dashboards. The same pie charts dashboard above viewed in the Geckoboard native mobile app shows the data in tabular format but does not render the pie charts. The data is still there and still readable however, as shown in the following mobile screenshot:

Mobile Geckoboard pie view
Geckoboard pie charts in native mobile app

When viewing the desktop version of the dashboard on a smartphone (e.g. via the sharing link), the pie charts show up as intended in the mobile browser, albeit rather small:

Geckoboard pie charts in mobile browser
Geckoboard pie charts in mobile browser

Thanks for reading! Feel free to leave any comments, ideas or improvements below.

2 thoughts on “How to create CSS pie charts in Geckoboard dashboards with data from Google sheets”

  1. Ben,

    Awesome work… congratulations. Having had a look through it, I have decided that I don’t need a pie chart that badly… was wondering if there was an easier way to get xml/JSON out of my google sheet and let geckoboard do the heavy lifting?


Leave a Reply

Your email address will not be published. Required fields are marked *