Linking Affiliate Window and Google Sheets

June 29, 2020 |  Categories:  digital media  

Automating data transfer from your Affiliate Window account to Google Sheets is relatively straightforward. However, the awin documentation isn't great and when I first did this, I couldn't find much in the way of good examples of the whole process so I figured I'd share. Why would you want to do this? Lots of companies use Google Sheets to combine channel data for their weekly reporting - it's pretty straightforward to pull in data from AdWords and Analytics and Bing and Facebook also have APIs that you can work through by following their documentation, which is better (this is extensively covered elsewhere so I'll not add to this particular mountain; if it is something you wish to discuss then please get in touch).

To Link a Google Sheet to Affiliate Window you will need three things - A Google Sheet, to enable the API in your awin account and finally your advertiser ID.

To enable the API, follow the steps set out in the awin wiki - you will need the OAuth2 token later so copy and paste into a notepad file, along with your advertiser ID.

Next, set up a Google Sheet that is going to take the data. When run the code will import date, commission and sales amount, so you will need these three columns. The script needs the location of these three columns; I have them as A, B and C - you may wish to have them elsewhere but if so you'll need to change the location in the code (it's marked). You will also need three more fields, for the from and to dates you want to pull, and the OAuth2 token (you may prefer to put this straight into the code - it doesn't make it any more secure either way so you should be careful about who has access to this particular sheet).

You should end up with a sheet that looks like this:

Add the OAuth2 token in F4 and then go to Tools and open up script editor. You'll need to create a new script for this spreadsheet, so name it something nice.

Now, you can download the code from my Github page. Paste this into your Google Sheets script, but before you run anything scroll down to the bottom of the page to this part:

When run, this will create two menu buttons in your Sheet, so you don't need to jump into the code each time. If you want to change the names on the button, edit the names in quotation marks, but NOT the 'pullJSON' part.

Having done this, go up to line 21:

And replace the XXXXX with your awin ID.

You can then save the code. In the menu bar, click the select function dropdown and select onOpen; run this by pressing the play button. Google will likely ask you at this point to grant access to this script - accept all these unless you see something that really looks amiss. Check in the Sheet that the menu item has appeared. If it has, enter dates in the fields next to the field names (NOTE - you need to escape these dates with a ' in order for this to work, and they must be in the format YYYY-MM-DD).

Then you can select your function from the menu drop down - this should execute the script and pull in all transactions between the dates you have specified. You can then copy these across into your accounts as you wish.

If you have any questions about setting up and running this code, please get in touch using the contact box below. Hope you find this useful!