Display data from pivot tables on your dashboards

Pivot tables are powerful functions available both in Excel and Google Sheets, and here we'll explain how to get them to work with Geckoboard! 

What are pivot tables?

A pivot table is a table that summarizes data from another table and is made by applying an operation such as sorting, averaging, or summing to data in the first table, typically including grouping of the data.

You can use pivot tables to narrow down a large data set or see relationships between data points. For example, you could use a pivot table to analyze which salesperson brought the most revenue for a specific month.

Read more about Pivot tables.

For Excel

Geckoboard will automatically interpret pivot tables from your Excel spreadsheets. No extra work is needed!

Example

You can test using this sample Excel sheet which contains a simple pivot table. You'll find it works seamlessly with Geckoboard as is.

Example of pivot tables in Excel working without requiring any additional steps i.e. directly with Geckoboard

For Google Sheets

While we don't have a built-in solution for Google Sheets, we've found an excellent and effective workaround using Google Apps Script. 

Using a Google Apps Script, you can copy the values from a pivot table into a new sheet. These copied values are then used to power your Geckoboard widgets. Using Google Sheets' Triggers, we can also automate this process. Further, this method will import the data automatically (between the two Google Sheets), even when the sheets are closed.

Let's begin by writing a script.

Warning

We recommend opening a new Google Sheet initially as the following steps will overwrite any information you have in an existing Google Sheet.

Step 1: Write a Script

  1. In a new Google Sheet, select the Tools menu and click Script Editor. A new Google Apps Script tab will open.
  2. Replace the default code with the following script:
    function extractPivotTableValues() {
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     
     /* Replace pivot-table with the name of the sheet that contains the original pivot table. If your pivot table is very large you may also need to increase the cell range from A1:Z999 */
     var source = ss.getRange('pivot-table!A1:ZZ9999');
    
     /* Create a new sheet for this script to copy your pivot table values into and replace pivot-table-copy with the name of that sheet. */
    
     source.copyTo(ss.getRange('pivot-table-copy!A1'), {contentsOnly: true});
    }
  3. In the title field, add a name to your project.
  4. Next, select File, then choose Save.Pasting the Geckoboard pivot table code into Google Apps Script Editor

Step 2: Set up a Trigger

  1. In the Script Editor, click the Edit menu and select Current project's triggers.
  2. In the window that appears, click the link No triggers set up. Click here to add one now.
  3. In the Events field, make sure Time-driven is selected.
  4. In the fields to the right, select the time interval you want your script to run on. 
  5. Click Save.Current Project's triggers for Google Sheets Pivot Table in Geckoboard
  6. In order for the script to run, Google requires your authorization and you will be prompted to authorize your app by clicking Review Permissions and Allow. More information on Google Apps authorization can be found in this guide.
  7. Your script is now set up and will update at the time interval you have selected!
  8. You can now close the Script Editor.

Note

We've found that occasionally, about a few times a week, a server error will occur on Google's end and the script will fail to run in that particular try. By default, you'll receive an email to notify you that this has happened.

Depending on the time interval you have selected, this is nothing to concerned about and your script should continue to run on the next scheduled time interval.Google_server_error.png

You should now have your Google Sheets pivot table data displaying on your dashboard! 🎉📊

Was this article helpful?

Awesome! 👍  Thanks so much for your feedback!