I won’t waste a lot of time, and if you know why you’re here, feel free to move down to setting up your API access. If not, read on…
I love using APIs and I wanted a good way to query API data and drop the data directly into Excel for reporting. I found some good free and paid tools. The free ones worked, but didn’t have everything I wanted, or didn’t work on both Macintosh and Windows platforms. Paid versions usually relied on someone else’s server, so all your data is running through someone else’s pipe. Wasn’t interested in that either if I could help it.
So I did a lot of digging and came across Bruce Mcpher’s incredible site on the different uses of Excel and Google Docs. He created some VBA classes and Apps Scripts “DB silos” that allowed me to query Google APIs through a Google Drive script directly from Excel (on both Windows and Macintosh). Technically, I could have kept everything working directly within Excel, but for me I wanted to setup a way to allow other team members access to the data without sharing API client IDs and secret keys if I didn’t have to. Plus, VBA is a terrible language to write in, and moving as much out of Excel as possible is a bonus.
Each user will have to enable their access in the Google API console, but all requests to the Analytics API will be made on behalf of the account of our choosing. So essentially every team member will be able to create reports directly within Excel based on whatever access the master account has, but you do not have to share passwords, accounts, or API keys in order for them to do it. Everyone has access to the same data in a read only state. There will be a few steps, but mostly it’s a lot of clicking and/or copying & pasting. It’s a one time setup, and if you have the time to set it up once, you will be pulling Google Analytics data directly into Excel via API without the need to export data.
Did I mention it works for Windows and Macs?
And it’s free?
Figured you might be interested, so let’s break up the steps we need to get through to create our new “server” environment within Google Docs.
- Create API key & access (users & admin)
- Create & authorize Apps Script access in Google Drive (admin only)
- Setup account on parse.com for storing custom Analytics reports to share among team members (optional).
Before we begin, I will make one more note. This is VERY beta. I am releasing this as I want to find out more about how people will use it before I go too far down setting this up in a certain way. Having said that, I think a few of you might find this useful. Here is a typical way you could use this setup.
- analytics@yourcompany.com
- A master account that has all the Google Analytics profiles configured for it
- This is where we should put the Google Drive scripts created in part two of this post
- person@yourcompany.com (Each person has their own API key)
- User that needs access to the above data
- They create their API key below and enter it into the Excel spreadsheet. If everything is configured correctly, they will be able to access and query all profiles under the analytics@yourcompany.com account
Create API Key (For Users)
First thing you should do is sign into the Google account that you will be using to access the data. If you are setting up the Drive scripts for other people to access, then make sure you are logged into the account that has all of the Analytics profiles you will be sharing. If you were directed here, then make sure you are logged into your Google Apps account, or whatever account you were instructed to be authenticated with in order to query Analytics. The API key we are creating is going to be put into the Excel spreadsheet (will be available in the next post). It will allow communication between Excel and the Google APIs.
PLEASE NOTE:
- While technically you could share the API key with each person who you want to give access to, you shouldn’t. These API instructions should be followed by each person who is going to be using this tool.
Moving on…Visit the Google API Console. If you have never used their API, then you might see a screen like this. Go ahead and click the “create project” button.
Once you do that, a little refreshing happens, and they ask you to accept their terms of service. Once you do that, we’ll be ready to continue.
In case it’s not already selected, in the left hand column click “APIs” under “API & Auth”. Now, depending on how you are setting this up, will determine what APIs you need to enable. These are the APIs we need to enable for the spreadsheet to work end to end. During this process, we will be enabling both. There’s no harm if you enable both, but to keep your setup clean as you go, enable only what you need.
- Analytics API (Admin only)
- Drive API (User and Admin)
So, if you are setting this up for a team, you should have both of these APIs enabled showing at the top of the window like so. If you are an end user, you will only have the Drive API enabled.
Now that is done, click “Credentials” in the left hand navigation menu. Again, if you have never created an API key before, you should see a lot of white space with the following options.
If we don’t already have a “Client ID For Application” showing, click “Create New Client ID”. See below for what it should look like.
Once you click create, you should see something like this in your view now. Keep this window open, copy and paste the Client ID & Client secret somewhere else temporarily (and safe); we’ll need this information later on. These are two of the three pieces of data we need to give to Excel later on. Just make sure you can get back to the Google Code Console to retrieve this data when it’s time.
The last thing you need to do before leaving the API Code Console, is create your consent screen. This is done in the left hand nav under APIs & Auth –> Consent screen. This is pretty basic. Just add a website, a URL to a logo if you like, but most importantly, make sure you choose an Product Name. I named mine “RankHammer Excel API Proxy”. Once you do that, click save. This is how mine looks.
If you are a user who was directed here to create your API key, someone else has already created the Google Drive script. They should provide you with the URL you will need to enter into Excel to query Google Analytics. Get that URL; you’re done here. In my next post (soon to be published), we’ll step through inputting this data into Excel and running our first query. If you are setting this up for the first time, read on!
Setup Scripts in Google Drive (Admin Only)
Now that we have our API access setup and ready to go, there is one script we need to created. Stay logged into the same account we enabled API access on. You are? Good. Visit your Google Drive account.
- Open this link
- Make a copy of it to your local Google Drive
- At the top, click Resources –> Advanced Google Services
- Make sure Google Analytics is turned on, and on v3.
If you have never created a script in Google Drive before, you may see a window like the one below when you try and open this script. If so, just click the blue connect button and then try and open the script again to make your own copy.
Still with me? Good! In order for us to be able to use this script from excel, we need to authorize it within our account. We do this by running a method within the script first so it can ask us to authorize access. We do this by opening the Analytics.gs script (click the name in the left hand column). You should see something like this at the top.
You can choose whichever method you like. Choose one, and then click the play button to the left of the method name in the top toolbar. Once you click it, it will ask for authorization. When the window pops up showing you the permissions it requires, click accept. Now this script will be allowed to query your Analytics data on your behalf. If you get an error, the first thing I would do is click Resources–>Advanced Google Services in the top navigation. Make sure Google Analytics API is set to on, and on v3. It wouldn’t also hurt to click the link at the bottom of the window for “Google Developer Console” and make sure the Analytics API is turned on there too. If it’s not, turn it on. If that doesn’t fix it, comment below, or send me a message through Twitter or G+ to ask for help and I will help you figure it out.
Storing Custom Analytics Report Configs on Parse (Admin Only)
Parse is a really nice way to save your data in a schema-less storage setup in the cloud. This part is optional, but might be useful for you. Let’s say you have all the dimensions, metrics, filters, segments, sort values, etc all setup for a specific type of report you want to create. You use the Excel spreadsheet to set all of these parameters and run your report. There may be an instance where you want to reuse this report again for another Analytics profile/client. Well we use parse.com to store those report configs so you can load them again later. It also allows you to share these report configs with the same people who are using the same API access to Google Drive as you. So if you create 10-20 default reports you want people to use for monthly report, you create them, save them once, and anyone on your team can load those report configs to apply to any monthly reporting you may want to do. If this interests you, let’s get started.
First, sign up for an account.
It will ask you to create a new app. I just named mine Excel API Proxy.
With your new app selected, click “settings” at the top, and on the left hand navigation, click “Application Keys”.
Copy your Application ID and your REST API Key.
Return to the local copy of RankHammer Excel API Proxy script from above. In the left nav, click “parse.gs”. You will see a method at the top when you need to paste in your new Application ID & API Keys.
Wrapping Up (Admin Only)
Now it’s time to deploy your script as a web app. With the same Proxy script open, in the top navigation menu, click Publish–>deploy as web app. You should see a screen like this.
If yours does not show a Project version number, type a name in the input box and create the version. You can call the project version whatever you like. “Initial Setup” is fine.
Execute the app as:
I have it set to me. This is so I can share my Analytics profiles with other team members, without the need of adding each person to the actual profiles in the Analytics admin.
Who has access to the app:
Since we use Google Apps in our office, it’s easy to allow access to people. Only people with an account here can access it. So if you are using GMail, and want to share it with people, you will have to set this property to ‘Anyone’, and share the deploy URL with these members. In the future, I may add some authentication within the script, so you can validate a specific list of people that can execute this script. Of course, if you are the only one that’s going to be using this script, then you can set it to “Only Me”.
Once you click ‘Deploy’, a window pops up and shows you a URL to use this script. Copy it. We need it for Excel later on as well as the client ID and client secret we created earlier. These are the credentials that will allow Excel to authenticate properly to query data from Analytics.
The final step is to make sure the RankHammer Excel API Proxy file is shared with the people you want to be able to use this script. I thought the above instructions would enable this, but I kept getting “Access Denied” error until I did this step. If anyone has suggestions on how to make this work without this step, please let me know in the comments. This may be how we can have additional security for people outside of Google Apps. If you set the access above to “Anyone”, if you didn’t share it with them below, they shouldn’t be able to execute the script.
As you can see above, I made it available in a view only state (After clicking ‘Save’, change permissions from ‘can edit’ to ‘can view’). If you are not on Google Apps, you can share with specific people, and type in their GMail addresses, and set each person individually to ‘can view’.
And that should be it! Seems like a lot to do right? Not really, and also remember that we only do this setup once. In the next post, I will make available the Excel spreadsheet I created to query data directly into Worksheets for creating reports. Let me know what you think or if there are any questions in the comments below.