Google Sheets
The AgencyAnalytics Google Sheets integration allows you to seamlessly display custom spreadsheet data in your client reports and dashboards.
Simply load your data into a Google Sheet and connect it to a client. You can update the sheet at any time, and we'll automatically fetch those updates whenever a report or dashboard is loaded!
🔌 Connect Google Sheets
To avoid a failed connection or error when integrating Google Sheets to a Client, the person connecting must have:
Editor permissions for the Google Sheets you wish to connect
If the above requirements are met, simply follow these steps to connect:
Open the Client where you'd like to connect Google Sheets.
Click Integrations (plug icon) on the left side menu, then click Connect Integration in the upper right corner of the Integrations page.
Search for Google Sheets in the search bar at the top, then click the Google Sheets card.
Click the gray Connect New Account button below the connections list to launch the authorization window.
In the pop-up, sign in with your Google credentials, then follow and accept the permissions prompts to provide AgencyAnalytics access to Google Sheets.
Add Google Sheets widgets to dashboards and reports
After following the steps above to connect, Google Sheets data can be added to any dashboard or report as a stat widget, line chart, sparkline chart, bar chart, pie chart, or table.
Open a dashboard or report in Edit mode.
Locate and click Google Sheets in the Widget menu on the right.
Click, hold, and drag the widget onto the dashboard or report, then release.
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.
Google Sheet widgets mostly behave like other integration widgets: You can drag and drop them to a different spot, change the title or display, alter the size by dragging the bottom right corner of the widget, and more.
Unlike most widgets, Google Sheet widgets will have additional fields to fill out depending on the dimensions the chart needs and your own formatting requirements.
For example, table widgets can have a sort direction and a table row limit, while a pie chart needs an aggregator and, dimension and metric columns.
Always review the Data and General tabs in the widget menu to ensure the necessary fields are complete and your Google Sheet data displays correctly.
Valid date formats for Google Sheets widgets
For your widgets to change based on the date range you've set in AgencyAnalytics, you'll need to set the cells containing date information to "Date" format.
To format the date column in Google Sheets, select the Column, then click the Format menu > Number > Date.
To use a custom date format, like abbreviated written dates, the cell must be formatted first. Click Format> Number> Custom date and time, then select the custom format from the available list.
Default accepted date formats
The date formats below should work without additional cell formatting in your Google Sheet. Your widgets will update based on the date range you've set in our 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"
❓ Google Sheets FAQ
Can I display a date format different from YYYY-MM-DD?
The format for any dates used within the date column will show as YYYY-MM-DD by default.
To pick up the exact format you're using on a Google Sheet, rename the column to Date: . This should override the default and display using your format instead.
Can I use an Excel spreadsheet that I imported to Google Sheets?
We don't support Excel files directly uploaded to Google Drive; you'll need to convert the Excel file into a Google Sheet first! Learn how to convert them by following this Google guide.
Why can't I connect a Google Sheet to my report template?
Most individual sheets are Client-specific, so we require you to connect the sheet within the report rather than in the template. You can add any Google Sheet widget to a template, and then after applying the template, you'll need to select the specific sheet.
How do I remove empty columns from my Google Sheets table widget?
Hover over the widget, then click the ellipsis menu "..." in the upper right, and click Edit. Then, under the Data tab of the Edit Widget menu, change the Exclude Empty Columns dropdown to yes!
💬 Need additional help?
If you have any questions, please contact our friendly support team by following these instructions! We're available 24/5 to help 😄
