If you're an avid user of Google Analytics on Google Spreadsheet, you should be familiar with an add-on on Google Spreadsheet called "Google Analytics Add-on." It's an awesome tool that allows users to manually and automatically export data from Google Analytics into Google Spreadsheet. The tool, however, lacks the ability to export large unsampled data from Google Analytics. If the data is too large, it will be sampled, and that's when you find yourself frustrated.
After doing a lot of research about how to automate unsampled reports in Google Spreadsheet, I built a Google Apps Script app that allows users to pull and automate Google Analytics' unsampled reports into Google Spreadsheet.
Below is the script that I use in the Apps Script Editor of the Sheets. You can also have access to this code by making a copy of my GA Unsampled Report on Google Sheets and click on Tools -> Script Editor.
To use the app, follow the instructions below:
Note that this is only available to Google Analytics 365 (premium) users.
- Log into your Google Drive account. If you don't have one yet, you should sign up for it.
- Click here to view the template
- Make a copy of the template into your Google Drive folder
- On the top bar of the spreadsheet, click on Tools -> Script Editor
- The first time you use it, you need to configure it first.
a. Click on Resources -> Advanced Google Services
b. There'll be a pop-up window. Toggle to Google Analytics API and turn it on
c. Click on Google API Console. It will open a new window to API dashboard. Click on "Enable API and Services."
d. Scroll down, look for Analytics API and click on it.
e. Click on "Enable." Close the Window.
f. Back to the pop-up window. Click on "Ok." Make sure that there's a notification that reads "Updating Advanced Google Services settings."
6. Go back to Report Configuration sheet on the GA Unsampled Report. Fill into the rows highlighted in black or with the word "required" in the brackets. Note: to get Account ID, Web Property ID and View ID, go to Google Analytics UI and go to Admin. For privacy concern, I hide these IDs in black.
7. Fill in column C1 the number of reports you want to request starting from column B as the first report.
8. Go back to the Script Editor, and click on Request Report in the drop-down list and click on the triangle icon.
9. The first time you use it, it will ask for permission, follow the steps to give the permission.
10. Go to Unsampled Report Logs sheet, and check that there're reports pending there. Wait for a few minutes for the reports to be ready. The wait can last up to 30mn or hours.
11. After waiting for a few minutes, go to the Script Editor, click on the drop-down list and click on Update AllReports. Then run the script. If the reports are ready, the script will dump the reports into new sheets. Otherwise, nothing happens, and you'll have to wait a little bit more.
Tips: there's a new feature on Google Spreadsheet that you can add--Macro. If you click on Tools -> Macros ->Import, you will able to import the request script and updateAllReports script. The next time you use it, you can use it directly on the Spreadsheet without having to open the Script Editor.
12. To schedule the unsampled reports, go to the Script Editor and click on the clock icon. There'll be a pop-up window that allows you to add a trigger to schedule the report. A good idea is to schedule the request script a few hours earlier than updateAllReports script. You can also add notifications by entering your email so that Google will let you know if there's any errors.
This should be all. If you have questions, comment below or reach out me via the contact form.
Troubleshooting:
If you see an error that reads "ReferenceError: “ui” is not defined", try uncommenting the code on line 4 (//var ui = SpreadsheetApp.getUi();). Just in case you don't know how to uncomment, you need to remove the two slashes and then save it.
Final Note:
If you use this app and found value, it would make my day to drop me line.
Thanks!