Google Sheets Integration Overview

Everything you need to know about our Google Sheets integration.

Matthew Davis avatar
Written by Matthew Davis
Updated over a week ago

Google Sheets Integration Overview

Customers often need to show either in-house, spreadsheet based data, or data from third-party tools that aren't natively supported, in their AgencyAnalytics reporting and dashboards. 

We make this quick and easy with our Google Sheets integration.

The Google Sheets integration allows you to automatically pull in any custom data you like, and easily show it in your reports and custom dashboards. 

All you'll need to do is get your data into a Google Sheet, then connect it to a campaign. You can update the Google Sheet at any time, and we'll automatically fetch those updates whenever a report or dashboard is loaded.

Using Google Sheets

First, ensure you've integrated Google Sheets within your campaign (see here for a step-by-step guide).

Once integrated, you can create table widgets and stat widgets showing your data in reports and on custom dashboards.

All of the Google Sheets integration widgets have built-in date range functionality. This means that if you change the date range in your AgencyAnalytics reports or dashboards, these widgets will automatically change the data they display. See the list of valid date range formats for line and sparkline widgets.

Here's an overview of each of these widgets:

Google Sheets Table Widgets

Table widgets will show the entire spreadsheet from a tab of your choice in a Google Sheet (there is a selector to choose the tab in the widget's settings). These widgets are vertically scrollable on the custom dashboard, so you can resize them as needed.

Data in the first row of your Google Sheet will be treated as headings for each column, and any column width changes you make in the Google Sheet will be reflected in the widget.

The output of any calculations will be shown in the table, and a cell's contents will become a clickable hyperlink if it begins with "http://" or "https://". All hyperlinks will open in a new window.

Here's how to set up Google Sheets table widgets:

Google Sheets Stat Widgets

Stat widgets will show data from any single cell within a Google Sheet.

Google Sheets stat widgets can display numbers, symbols, or short sentences, and they'll show the output of any calculations being performed in the cell.

Here's how to set up Google Sheets stat widgets:

Google Sheets Pie Chart & Bar Chart Widgets

Pie and bar chart widgets allow you to show visual representations of your Google Sheets data.

These widgets pull data from two columns in your Sheet: One column with your dimension (the x-axis, or the label for your data), and one column with your metric (the y-axis, or the actual data you want to show).

Google Sheets Line Chart and Sparkline Widgets

Line and Sparkline Google Sheets widgets also give visual representations of your Sheets data.

These widgets pull data from two columns in your Google Sheet: One column with your date ranges, and another with the data you want to show.

Pivot Table Support

Setting up multiple Google Sheet tabs with large amounts of data can be time consuming. To help make things more efficient, you can use Google Sheets pivot tables.

Pivot tables let you create a new table that summarizes all your data by grouping it into different categories, then calculating statistics for each group, such as sums, averages, counts, and so on. This allows you to take large and complex datasets and break it down into smaller, more manageable pieces, making it easier to see patterns and trends in your data, help you identify outliers and anomalies, and save you valuable time by summarizing your data. We support different types of Google Sheets pivot tables.

Pivot tables are treated like regular Google Sheets tables, where data in the first row of your Google Sheet will be treated as headings for each column, and any column width changes you make in the Google Sheet will be reflected in the widget. Once you have setup your pivot table, you can show this on your dashboard or report by configuring a Google Sheets table widget on your dashboard or by configuring a table widget on your report.

Automating Your Workflow With Zapier

We automatically fetch data from Google Sheets, but you can also automatically send your third-party data into a Sheet using Zapier does this by connecting almost any third-party platform to Google Sheets.

Once set up, Zapier will "zap" the data into a Google Sheet, and we'll then pull it from the Sheet into our platform. This means you can fully automate the process of getting outside data into your AgencyAnalytics dashboards and reports.

Please note that we do not support importing Excel files uploaded to Google Drive; you would need to convert the Excel file into a Google Sheet. Here's a quick guide on how to convert Excel files to Sheets.

Did this answer your question?