Search API Connector Documentation

Print

Import Ahrefs Data to Google Sheets

In this guide, I’ll get you started on pulling data from the awesome Ahrefs SEO tool suite into Google Sheets, using the API Connector add-on for Sheets. Targeted around backlinks, you can use the Site Explorer, Content Explorer, Keywords Explorer, Rank Tracker, and Site Audit to compare your website to competitors. All in all, it’s a pretty sweet tool and we can integrate its native API with Google Sheets! Let’s jump in and see how to get started plugging out data in.

Note: Ahrefs is not a free tool. Further, if you want to access the Ahrefs API, you have to pay an additional subscription fee. You can read more about that here: https://ahrefs.com/api/subscription.

The Ahrefs API requires authentication for access, through either your own API token or an OAuth2 integration. This article primarily discusses connecting through API Connector’s paid OAuth2 Connection Manager, which handles authentication for you. If you’d prefer to manage the process manually using the free version of API Connector, please skip to the Appendix for step by step instructions on connecting with an API token.

CONTENTS

BEFORE YOU BEGIN

Click here to install the API Connector add-on from the Google Marketplace.

PART 1: CONNECT TO THE AHREFS API

If you haven’t connected to the Ahrefs API before, you’ll first need to initiate the connection.

  1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
  2. In the list of available connections, find Ahrefs and click Connect.
    ahrefs-img1
  3. You will see a modal asking you to approve the connection. Click Allow.
    ahrefs-img2
  4. You’ll then be returned to your Google Sheet, and can verify that your Ahrefs connection is active in the Connections screen.
    ahrefs-img3

PART 2: CREATE YOUR AHREFS API REQUEST URL

Ahrefs is nice enough to avoid a lot of the headache of piecing together an API request URL by providing us with this API request builder tool. You can access it by clicking “API Request Builder” in the left sidebar.
ahrefs-img4

From here, it’s super easy! Set up what data you want to get in the From drop-down, set your Limit, and copy the API URL Request output below. You can leave out the API token if you’re using the built-in OAuth2 connection.

https://apiv2.ahrefs.com?token=(ENTER-TOKEN-HERE)&target=ahrefs.com&limit=1000&output=json&from=ahrefs_rank&mode=subdomains

PART 3: PULL AHREFS API DATA INTO SHEETS

We can now enter these values into API Connector and import Ahrefs data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create Request tab, enter the Request URL we just created
  3. Leave the Headers section empty as we don’t need them for this request
  4. Under OAuth2 Authentication, choose Ahrefs from the dropdown menu.
  5. Create a new tab and click “Set current” to use that tab as your data destination.
  6. Name your request and click Run! A moment later you’ll see some Ahrefs data populate your spreadsheet:
ahrefs-img7

PART 4: MORE EXAMPLE API URLS

As previously mentioned in the last part, Ahrefs provides a handy API tool for creating ready-to-paste API requests. Great! Let’s check out the documentation and see what kind of data we can get from our website. A few other example endpoints that may be useful for you would be:

  • Top 10 backlinks:
https://apiv2.ahrefs.com?from=backlinks&target=YOUR_DOMAIN&mode=domain&output=json
  • Top 10 referring domains ordered by Domain Rating:
https://apiv2.ahrefs.com?from=refdomains&target=YOUR_DOMAIN&mode=domain&order_by=domain_rating:desc&output=json
  • New backlinks on 2020-07-20
https://apiv2.ahrefs.com?from=backlinks_new_lost&target=YOUR_DOMAIN&where=type:"new",date:"2020-07-20"&mode=domain&output=json

APPENDIX: CONNECT WITH AN API TOKEN

Ahrefs requires that all API requests are authenticated. This is the step-by-step process to authenticate with an Ahrefs API token:

This section is provided as an alternative to the method described above. Instead of clicking Ahrefs in the Connections manager, you will retrieve your key yourself. Once you have it, you can use it by appending token=YOUR_TOKEN to any API URL, e.g. https://apiv2.ahrefs.com?target=ahrefs.com&limit=1000&output=json&from=ahrefs_rank&mode=subdomains&token=YOUR_TOKEN. Since you’re manually including an API key, leave OAuth2 authentication set to None.
  1. If you’re connecting with an API key, you’ll need to log in and start a project. Go ahead and put in your website’s address here.
    ahrefs-img8
  2. Just hit continue and go through the rest of the advanced steps if you don’t know what you are doing (like me). After finishing the setup for your selected website, you should be presented with a screen like below. Go ahead and click on More -> Ahrefs API.
    ahrefs-img9
  3. Now check out the left corner and click on Your API Profile.
    ahrefs-img10
  4. Go ahead and grab that API token!

Previous Import AfterShip Data to Google Sheets
Next Import Airtable Data to Google Sheets

2 thoughts on “Import Ahrefs Data to Google Sheets”

Leave a Comment