Bedrock42

SEO Quickstart Guide: Automate Search Console To Google Sheets Using Python

Most SEO’s will understand the laborious task of exporting a report from Google Search Console, only to upload into google sheets to be analysed, every week, over and over. You might be using something like Supermetrics to get around this problem, good idea, but why not save that license fee and save over £££. This type of task is ripe for a python upgrade and is a great entry point to automating your SEO workflow.

In this guide I’ll be showing you 3 things:

  1. Project & Authentication Setup
  2. Request Search Console Data
  3. Uploading to Google Sheets

If you’ve never used python before, I recommend getting your basics first. There’s a few requisites, knowledge of pip, python modules, and virtual environments. If that sounds unfamiliar, not to worry! I've written about this process previously in our "Querying the Google Analytics API with Python Guide", also here's a handy video tutorial I created as well.

Let's begin!

Project & Authentication Setup

First we need a Google Cloud project with access to your Search Console data and the Google Sheet which we want to pass this data into. If you've never used Google Cloud Console before, it's as simple as going to console.cloud.google.com and creating a new project when prompted.

New Google Cloud Project

Use the navigation menu to get yourself in the APIs & Services Library. You will need to search for and enable both the ‘Google Search Console API’ and ‘Google Sheets API’. Within the API & Services category of cloud console, navigate to credentials and create a Service Account with Owner level project access. You should see a button to create a new JSON key, which we will use to give our script permissions.

JSON Key Creator Screen

Keep in mind where that JSON file is downloaded once created. If you take a peek inside you’ll see this JSON file has a field named ‘client_email’, this is the email account you will be giving search console and google sheets access to.

Search Console: Google Search Console Service Account

Create a new spreadsheet, or take an existing sheet you will be outputting to, and share this with read/write access: Creating New Google Sheet For Google Search Console Python Upload

Now your project has all the access it needs to perform!

Request Search Console Data

Now we’re going to set up our python script, this will be running within a virtual environment which houses our python modules. At this point you’ll want python, pip & virtualenv installed and usable in CMD or Powershell. If you’re confused, reread my introduction.

Within a Development folder, create these folders, one for your scripts, one for your virtual environments.

Folder Structure For Python Project

Take your JSON credentials file, save it within the ‘./SearchConsoleReporting/’ project folder and rename it 'client_secrets.json'.

Now navigate to the ‘./venv/’ folder in CMD/powershell, and create a new virtualenv for this project.

venv> virtualenv searchConsole

Activate your environment.

venv> .\searchConsole\Scripts\activate

We’re now going to install the Google API Python & Authentication module to our virtualenv.

venv(searchConsole) > pip install --upgrade google-api-python-client --upgrade oauth2client

Now that it's all installed, let's navigate up a directory, and into our ‘/SearchConsoleReporting/’ folder where we saved our JSON file. This is where our main script is gonna live too. I like to use Visual Studio Code as my main IDE, you can simply create the script file with the following command.

Search_Console_Reporting(searchConsole) > code main.py

In the main.py file you now have open, paste the following:

from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
import pprint
pp = pprint.PrettyPrinter()

KEY_FILE_LOCATION = 'client_secrets.json'

def initialize_googleSearchConsoleReporting():

    SC_SCOPE = 'https://www.googleapis.com/auth/webmasters.readonly'

    credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SC_SCOPE)
    webmasters_service = build('webmasters', 'v3', credentials=credentials)

    return webmasters_service

def get_report(webmasters_service):

    SITE_URL = 'https://www.bedrock42.com/' #replace with the URL of your website.

    request = {
        'startDate': '2020-10-01',
        'endDate': '2020-10-30',
        'dimensions': ['query', 'page'],
        'searchType': 'Web',
        'rowLimit': 10
    }

    response = webmasters_service.searchanalytics().query(
        siteUrl=SITE_URL, body=request).execute()

    pp.pprint(response) #printing the response for debug
    return response

Let’s chat about what this does. The top three lines pull in our modules to be used by the script. We then point it to the JSON file with our credentials. The function ‘initializegoogleSearchConsoleReporting()’ is defining the scope in which we want to use our API, then gives us our service object to make the requests possible. ‘getreport()’ is defining our URL and actually making the request. The value within the ‘request’ object can be modified to your requirements, I’ve kept them as simple static values so you can see what format they need to be in.

Thus ends our second section.

Uploading to Google Sheets

We’re at the final stretch now. Hopefully you’re still with me. It’s time to put all this fun stuff in a Google Sheet without lifting a finger. You’re going to need the ID of the sheet you want to use, and also the spreadsheet tab name.

We get the ID from the URL of our sheet...

Google Sheets ID

We get the sheet tab name, well, from the tab name…

Google Sheets Tab Name Example

Now paste the following below the stuff you’ve just added to the main.py:

def initialize_googleSheets():

    GS_SCOPE = ['https://www.googleapis.com/auth/spreadsheets']

    credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, GS_SCOPE)
    gsheet_service = build('sheets', 'v4', credentials=credentials)

    return gsheet_service

def gsheet_upload(gsheet_service, searchConsoleData):

    SPREADSHEET_ID = '1_132vOmArYDmXu1lVdNHAtd56WFJkGjcDllrtsbqtxQ' #replace with your sheet ID
    SHEET_TAB = 'Sheet1' #replace with your tab name

    #Setting the column headers
    values = [['query', 'page', 'clicks', 'ctr', 'impressions', 'position']]

    #Looping through each row of the search console results, and formatting them for gsheets.
    rows = searchConsoleData['rows']
    for i in rows:
        query = i['keys'][0]
        page = i['keys'][1]
        this_row = [query, page, i['clicks'], i['ctr'], i['impressions'], i['position']]
        values.append(this_row)

    #Using the value array, we work out the size of the range we need to paste into the sheet.
    rangeEnd = str(len(values))
    range_name = SHEET_TAB + '!A1:F' + rangeEnd
    body = {
    'values': values
    }

    result = gsheet_service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID, range=range_name,
        valueInputOption='USER_ENTERED', body=body).execute()

webmasters_service = initialize_googleSearchConsoleReporting()
searchConsoleData = get_report(webmasters_service)
gsheet_service = initialize_googleSheets()
gsheet_upload(gsheet_service, searchConsoleData)

I’ve added some comments to this but lets chat about it anyway. You’ll recognise the first function, ‘initialize_googleSheets()’, is pretty similar to the one used for search console, just changing a couple values to point it to the right scope and api.

Our ‘gsheet_upload()’ function gets a little more complicated. After setting our sheet ID and tab name, we’re setting our column names in order. It then ‘for’ loops through each row of the Search Console data we just requested, transforming it into the row format we need for Google Sheets. This is then appended to the value array for each sheet row.

Once complete, we work out how many rows we have so we can get the size of the range we need to paste into Google Sheets. I’m pasting into A:F as we have 6 columns. The final step is wrapping this up into a body object, and executing the update to our sheet. I’ve used ’USER_ENTERED’ as the input option, which just means it’ll paste it in like a human would.

The final four lines of this are running our functions in the correct order to make this work. You can now go back to your CMD/Powershell window, with the virtual environment still active, and run this python file.

Search_Console_Reporting(searchConsole) > python .\main.py

If everything has gone well, you should see your request response is printed to the console, and your Google Sheet is immediately updated with the Search Console data.

Celebrate as you see fit...

If you’re having problems, go back through the steps above crossing your t’s and dotting your i’s, make sure your service account email has the right access, if you have some errors in your console after running the code try google as your first port of call or get in touch.

Here’s the full script in it’s unbroken majesty...

from apiclient.discovery import build
from oauth2client.service_account import ServiceAccountCredentials
import pprint
pp = pprint.PrettyPrinter()

KEY_FILE_LOCATION = 'client_secrets.json'

def initialize_googleSearchConsoleReporting():

    SC_SCOPE = 'https://www.googleapis.com/auth/webmasters.readonly'

    credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, SC_SCOPE)
    webmasters_service = build('webmasters', 'v3', credentials=credentials)

    return webmasters_service

def get_report(webmasters_service):

    SITE_URL = 'https://www.bedrock42.com/' #replace with the URL of your website.

    request = {
        'startDate': '2020-10-01',
        'endDate': '2020-10-30',
        'dimensions': ['query', 'page'],
        'searchType': 'Web',
        'rowLimit': 10
    }

    response = webmasters_service.searchanalytics().query(
        siteUrl=SITE_URL, body=request).execute()

    pp.pprint(response) #printing the response for debug
    return response

def initialize_googleSheets():

    GS_SCOPE = ['https://www.googleapis.com/auth/spreadsheets']

    credentials = ServiceAccountCredentials.from_json_keyfile_name(KEY_FILE_LOCATION, GS_SCOPE)
    gsheet_service = build('sheets', 'v4', credentials=credentials)

    return gsheet_service

def gsheet_upload(gsheet_service, searchConsoleData):

    SPREADSHEET_ID = '1_132vOmArYDmXu1lVdNHAtd56WFJkGjcDllrtsbqtxQ' #replace with your sheet ID
    SHEET_TAB = 'Sheet1' #replace with your tab name

    #Setting the column headers
    values = [['query', 'page', 'clicks', 'ctr', 'impressions', 'postion']]

    #Looping through each row of the search console results, and formatting them for gsheets.
    rows = searchConsoleData['rows']
    for i in rows:
        query = i['keys'][0]
        page = i['keys'][1]
        this_row = [query, page, i['clicks'], i['ctr'], i['impressions'], i['position']]
        values.append(this_row)

    #Using the value array, we work out size of the range we need to paste into the sheet.
    rangeEnd = str(len(values))
    range_name = SHEET_TAB + '!A1:F' + rangeEnd
    body = {
    'values': values
    }

    result = gsheet_service.spreadsheets().values().update(
        spreadsheetId=SPREADSHEET_ID, range=range_name,
        valueInputOption='USER_ENTERED', body=body).execute()

webmasters_service = initialize_googleSearchConsoleReporting()
searchConsoleData = get_report(webmasters_service)
gsheet_service = initialize_googleSheets()
gsheet_upload(gsheet_service, searchConsoleData)

Hopefully this helps you on your way to automating your SEO reports using python, there’s nothing worse than a repetitive export import task, or paying out the wazoo for an automation you can develop internally. If you have any questions or feedback, email me at richard@bedrock42.com, find me on LinkedIn, and even Twitter.

What You'll Get in a Free Proposal


Analytics Audit Result

Analytics Audit Result

Highlevel overview of your analytics health

Testing Strategy

Testing Strategy

Sample A/B test and Areas of Focus

What We Charge

What We Charge

Transparent costs and ROI calculations


GET YOUR FREE PROPOSAL