Build a Google Analytics dashboard 🖥️ with an Apps Script, a Sheets add-on, and Google Sites

If you help manage websites for your organization, and use Google Analytics, you can have data synced directly into a Google Spreadsheet on a routine basis (aka daily at 9am), by installing the Google Analytics add-on in your Sheet.

In addition to using the add-on, you can record your steps using a macro recorder and convert that into a custom menu that automates the setup of your report and creates a summary in chart form. I have included the code recorded from a macro within the sheet’s script below, but you can record your own steps to customize your script.

In this article I walk you through how to build a process to gather unique pageviews for this week, last week, this year, last year, regional location of audience, and top browsers used.

Follow me on Twitter / Source code in GitHub

Why macrorecording?

If you do not feel comfortable writing JavaScript, macrorecording can help you get the job done in many aspects. This is why I have opted to share the macro code specifically, and illustrate how you can paste multiple recordings, and execute them within a custom menu.

Table of content:

1. Technical highlights
2. What you need
3. Steps to run the code
4. Optionally creating a dashboard
5. Friendly learning tips
6. Helpful documentation

Technical highlights

• Google Analytics add-on already syncs your accounts with Sheets without code.
• Can setup Google Analytics report to run as often as desired automatically.
• Can display charts created into a website called Google Sites so you don’t have to share the raw Spreadsheet to stakeholders.
• A custom menu called ‘Analytics Helper’ has logic written in Apps Script in the Sheet’s script editor that was built from recording steps via a macro when formatting the summaries from the Google Analytics
• Script to automate sheet’s configuration is available upon opening sheet due to the onOpen trigger.

What you need:

• Google Analytics account (free or premium version)
• Google Sheet (need to be logged into the Sheet with the same user that has access to Google Analytics. You can ask the account manager to give your Google account rights to see the data).
• Import the free Analytics-Add On
• Optional- create a new Google Site

Steps:

1. Copy Google Sheet with script

• Make a copy of this sheet and give it a name. Conversely you can watch this Google Analytics add-on tutorial and record your own macro.
• Your sheet includes a copy of the code (visit Tools > Script editor).

The code is also pasted all the way at the bottom of this article.

2. Install Google Analytics add-on

• Click menu toolbar: Add-ons > Get add-ons
• Search and select ‘Google Analytics.’
• Click ‘install’ button

3. Create new report

• Click menu toolbar: Add-ons > Google Analytics > Create new report
• In the right sidebar that pops up 1) Give your report the name ‘This week ’ 2) Select an ‘Account’ from the drop down (you must be signed into your Google Sheet with the same email address that log into your Google Analytics account to see your websites in that drop-down) 3) Enter ‘Unique Pageviews’ in ‘Metrics’ and ‘Page Title’ in ‘Dimensions.’
• Click ‘Create report’ button.

4. Setup report

• Automate the populating of data needed to run a report on the following types of dates and values to view pageviews from this week, last week, this year, last year, browsers, and regions.
• Click custom menu ‘Analytics Helper’ > ‘Setup data’ (which grabs the data you setup to create the report)

5. Run report using the Analytics add-on

• Add-ons menu > Google Analytics > Run reports.
• You should now have 8 tabs total in your sheet.

6. Setup summary page

• In order to automate the summarizing of this data into custom charts make sure to click on the ‘Summary’ tab in your Sheet.
• Select cell A1 in the Summary tab.
• Again select the custom menu ‘Analytics helper,’ but this time choose second option ‘Create summary.’

• And now you have new charts summarizing all your data.

7. [OPTIONAL] Schedule report

• Finally schedule the report to run as frequently as you wish, in this case let’s say daily by going to Add-ons > Google Analytics > Schedule reports.

• New pop-up box shows up and click ‘Enable reports to run automatically.’
• Select ‘everyday’ at ‘9am’ (or as desired).

• In order to cancel scheduling return to Add-ons Google Analytics > Schedule reports and remove the check-box on ‘Enable reports to run automatically.’

8. [OPTIONAL] Create an analytics dashboard with charts on a new Google Site (drag-n-drop website)

• Visit Sites.new in your browser.
• Give the site a name at the top left corner ‘Google Analytics dashboard’
• Select a theme by locating on the top right corner: Theme > Simple > Gray color (or any color of your choice).

• Hover over the top header and click Change image > Select Image > Search (uses free images licenses for reuse on the internet) > type in rainbow and select a rainbow image you like (this is just an example so if you prefer to pick another thing other than a rainbow, feel free).

• The color of the image you select will readjust upon being selected, as it will automatically align with your theme.

• [OPTIONAL] if you do not like the adjustment simply hover over the bottom right corner of the header and click the stars icon (click it again to revert back to the adjustment).

• Insert a text box by locating on top right corner Insert > text box.

• Change text boxes color to gray by hovering over the text box > select the color canvas icon > change color to Emphasis 2.

• Insert charts from sheet by again locating Insert > then Chart > Select your spreadsheet > Select all 6 charts.

Note: the integration between Google Sheets and Google Sites allows for any changes made to the charts in the sheet to update in the site automatically.

• Arrange the charts to display in two columns so you end up having 3 rows of charts (everything can be dragged and resized).

• Change permissions in the top right corner, like a Google Doc.
• Publish Site by clicking Publish > enter desired URL text, that will be the link to your site to embed (which can also be copied by clicking the hyperlink icon at the top of the site in edit mode 🔗) > Publish button.

• A bar at the bottom shows up for a few seconds with a link to ‘view’ your website in it’s public view, click it.

• Published view of your site:

Friendly learning tips

• Code was generated from a macro that copies your steps in formatting the sheet.
• The macro includes the term “.activate” in the code, for example: `spreadsheet.getRange(‘G3’).activate();` which indicates in the script that a specific spreadsheet, tab in the sheet, range, or even a cell is the current one the user wants to take action on.
• You can create custom menu in order to execute multiple different scripts generated by your macro.

Helpful documentation

• Macro best practices
• YouTube video on Google Analytics add-on
• Google Analytics article on dimensions vs metrics

Code — 2 files

appscript.json

Manifest file for the App Script.

Code.gs

Includes custom menu and functions built from the macros recorded.

• © 2020 GitHub, Inc.