Publishing Pivot Table Charts to Your Website With Google Sheets

In the spring of 2013, we used SurveyMonkey Audience to collect data about what CRM systems were deployed at organizations across the U.S. The main question we asked in the survey was “What is your organization’s primary CRM system?”

After the survey finished its run, SurveyMonkey provided us with a CSV file of the survey results from approximately 750 respondents. The first thing we did was to upload the CSV file to Google Drive and then open the file with Google Sheets. Once we had the data in Google Sheets, we were able to easily create pivot tables and associated charts.

In our case, we wanted to slice and dice the data by company size and by U.S. market area, so we ended up with a lot of sheets.

At the time, we took screen shots of the Google Sheets pivot table charts and then uploaded PNG files to WordPress in order to visually display the survey results. It turns out that another way we could have displayed the charts would have been to embed code provided by Google Sheets and thereby create a more interactive visitor experience. For example, hovering over a pie chart slice “pops” the slice and displays more data behind the slice in an embedded pivot table chart.

Embedding the charts would have also allowed us to make small changes to the charts within the sheets and have those changes auto-update on the blog post.

Since 2013, Google has changed the embed code parameters. Google will likely change them again in the future, but here’s a look at how to work with the current embed code.

If you don’t know how to create a pivot table in Google Sheets, here’s a quick tutorial.

Create a Chart From a Pivot Table in Google Sheets

While clicked into a pivot table, add a chart. We highly recommend that you move the chart to its own sheet. This will make managing the published embed code easier. To do this, click on the arrow in the top right of the chart and select Move to own sheet…

Google Sheets - Move Chart to Own Sheet

Generate the Embed Code for the Chart

In Google Sheets, select File > Publish to the web…

Click on the dropdown that reads Entire Document and then select a sheet that contains a pivot table chart. After clicking the Publish button, you’ll see code under the embed tab:

Publish Google Sheets to the Web

The embed code that Google Sheets provides is not dimensioned for most use cases, so the first thing to do is to add width and height parameters. We added width=”100%” height=”450″. We found that once the height exceeds a certain value, the scrollbars will disappear.

A close look at the embed code above reveals several parameters. Here’s the function of each one.

GID – references the sheet within the Google Sheets spreadsheet that will be displayed when one sheet is published and the default sheet that will be displayed when multiple sheets are published
single=true/false – when more than one sheet is published, “false” allows the visitor to select any one of the published sheets
widget=true/false – “false” removes the sheet tab at the bottom of the embedded image and adds text with links to the bottom that reads: “Published by Google Sheets – Report Abuse – Updated automatically every 5 minutes”
headers=true/false – we have not yet discovered the difference between true and false

Here are several examples of embedded charts with varied parameters. Be sure to hover over the pie slices:

Single Sheet, Chart Not Maximized, Legend Included

Multiple Sheets, Default Sheet’s Chart Maximized, Legend Included in Default Sheet

Single Sheet, No Legend, Maximized Chart, Widget=False

You may need to play around with the source sheet settings and the embed code parameters to get the desired results, but Google Sheets pivot table charts are an effective way to visually display data to your website visitors.

Leave a Reply

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