The Google Sheets integration lets you display custom spreadsheet data in your client reports and dashboards. Load your data into a Google Sheet, connect it to a Client, and update the sheet whenever you like.

AgencyAnalytics automatically fetches those updates whenever a report or dashboard loads.

To avoid a failed connection or error when connecting Google Sheets to a Client, the person connecting must have Editor permissions on the Google Sheets they wish to connect to.

Connect Google Sheets to a Client from the Data Sources tab. Follow these steps:

Step 1: Open the Client where you'd like to connect Google Sheets, then click the Data Sources tab at the top.

Step 2: Click the blue Connect Data Source button in the upper right corner of the page.

Step 3: Click the gray Connect New Account button below the connections list to launch the authorization window.

Step 4: In the pop-up, sign in with your Google credentials, then follow the prompts and accept the permissions to grant AgencyAnalytics access to Google Sheets.

Once Google Sheets is connected to a Client, you can add its data to any dashboard or report as a stat widget, line chart, sparkline chart, bar chart, pie chart, or table.

Step 1: Open a dashboard or report in Edit mode.

Step 2: Locate and click Google Sheets in the Widget menu on the right.

Step 3: Click, hold, and drag the widget onto the dashboard or report, then release.

Step 4: Click the widget, then fill in the fields in the General and Data tabs of the Widget menu on the right to finalize the setup and display the data.

Under the Data tab, click the spreadsheet selector and choose the appropriate spreadsheet to pull from. If needed, use the second drop-down to select a specific spreadsheet tab.

Once Google Sheets is connected to a Client, you can add its data to any dashboard or report as a stat widget, line chart, sparkline chart, bar chart, pie chart, or table.

Step 1: Open a dashboard or report in Edit mode.

Step 2: Locate and click Google Sheets in the Widget menu on the right.

Step 3: Click, hold, and drag the widget onto the dashboard or report, then release.

Step 4: Click the widget, then fill in the fields in the General and Data tabs of the Widget menu on the right to finalize the setup and display the data.

Unlike most widgets, Google Sheets widgets have additional fields to fill out depending on the chart's dimensions and your own formatting requirements. For example, table widgets can have a sort direction and a table row limit, whereas a pie chart requires an aggregator, as well as dimension and metric columns.

Always review the Data and General tabs in the widget menu to ensure the necessary fields are complete and that your Google Sheets data displays correctly.

For date-range filtering to work, your date column must be titled Date.

You'll also need to set the cells containing date information to "Date" format in Google Sheets.

Step 1: Select the date column in your Google Sheet.

Step 2: Click the Format menu, then choose Number, then choose Date.

To use a custom date format, like abbreviated written dates, format the cell first.

Click Format, then Number, then Custom date and time, and select the custom format from the available list.

The date formats below should work without additional cell formatting in your Google Sheet. Your widgets update based on the date range you've set in the AgencyAnalytics platform

mm-dd-yyyy
For example: "04-26-2024" for April 26th, 2024

yyyy-mm-dd
For example: "2024/04/26" for April 26th, 2024

dd-Mon-yyyy

For example: "26/Apr/2024" for April 26th, 2024

Written month, day, year
For example: "July 1st, 2024" or "July 1, 2024"

Written month, year
For example: "December, 2024"

Dates in the date column show as YYYY-MM-DD by default. To display the exact format you're using in Google Sheets, rename the column to "Date:" (with a colon). This overrides the default and displays using your format instead.

Excel files uploaded directly to Google Drive aren't supported. You'll need to convert the Excel file into a Google Sheet first.

Learn how by following this Google guide.

Most individual sheets are Client-specific, so you need to connect the sheet within the report rather than in the template. You can add any Google Sheets widget to a template, and then, after applying the template, select the specific sheet.

Empty columns can prevent a widget from displaying data. To remove them, hover over the widget, click the Ellipsis menu in the upper-right corner (this menu only appears on hover), then click Edit.

Under the Data tab of the Edit Widget menu, change the Exclude Empty Columns dropdown to Yes.

Yes, there is a row limit of 2,500 rows, set by Google. AgencyAnalytics pings the Google Sheets API each time data is requested. Increasing the row limit would mean more API calls, which could cause timeouts once the 2,500-row threshold is exceeded. This limit prevents timeouts and protects against data retrieval failures.

Empty columns in your sheet can cause widgets to stop displaying data. Remove empty columns or make sure they're populated.

403 errors typically occur due to account mismatches, permission issues, or browser problems. To resolve them, try the following:

If you have any questions, please contact our friendly support team by following these instructions! We're available 24/5 to help 😄