Update a Google Sheet with Semrush Position Tracking API Using Python

If you want to export your keyword position data from Semrush using their (paid) API and send it to a Google Sheet with Python, this tutorial is for you. I’ll also show two ways to automate the process. It’s surprisingly simple and requires very little code. I use the Semrush API here, but the script can be adjusted for any tracking platform that exposes an API. This tutorial assumes some prior Google Cloud Platform (GCP) setup; I may create a separate GCP setup guide later. Until then, this guide is intended for readers comfortable with GCP.

Requirements and Assumptions

• Python 3 is installed and you understand basic Python syntax
• Access to a Linux installation (I recommend Ubuntu) or Google Colab
• Semrush API access with a Position Tracking project configured (the script can be adjusted to use a different platform)
• Google Cloud Platform account (GCP)
• Google Drive API and Google Sheets API enabled in GCP
• A service account created in GCP and its JSON credentials file downloaded
• Service account email shared with the Google Sheet (open the JSON credentials file to find it; it contains an @)

Import Modules and Set Authentication

Start by installing the gspread module, which helps us use the Google Sheets API. Run the following in your terminal. Google Colab includes gspread but often provides an older version—if you’re using Colab, add an exclamation mark at the start of the command.

pip3 install gspread==3.6

Next we import the modules we’ll be using.

• gspread: interacts with the Google Sheets API
• ServiceAccountCredentials: Google authentication
• requests: for making the API call over HTTP
• json: handling the API response (JSON format)
• pandas: organizing the keyword data
• date: getting today’s date

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import requests
import json
import pandas as pd
from datetime import date

Next set some variables. Your project ID (several digits) can be found in the Semrush project dashboard URL. LIMIT controls how many keywords the API returns. I’m using a minimal set of properties—see the Semrush API documentation to customize the response. These variables are used to construct the API URL.

Setup Key Variables

today = str(date.today())

APIKEY = ""
PROJECT_ID = ""
LIMIT = ""
DOMAIN = "" #example: domain.com
creds_file = "" #example: cred.json
sheet_name = ""

Next we build the API URL. Note the URL parameter at the end contains wildcards you may want to customize. I use the default pattern that accepts variations of the core domain and its pages. This is a required field. We execute the call and store the JSON response. Then we create a DataFrame to hold keyword data and read the total keyword count from the response. This total should match your LIMIT parameter.

Build API Call and Handle Response

url = "https://api.semrush.com/reports/v1/projects/"+PROJECT_ID+"/tracking/?key="+APIKEY+"&action=report&type=tracking_position_organic&display_limit="+LIMIT+"&url=*."+DOMAIN+"/*"
payload = {}
headers= {}

response = requests.request("GET", url, headers=headers, data = payload)
kdata = json.loads(response.text.encode('utf8'))

df = pd.DataFrame(columns = ['Date', 'Keyword', 'Current Rank', 'Last Rank','Diff','Diff30','Volume']) 

total = kdata['total']

We then iterate through the JSON response. Semrush’s key names can be unusual—review the API documentation for field definitions and additional options. For this tutorial we extract the keyword, current rank, last rank (relative to the API’s start date or default), one-day difference, 30-day difference, and keyword volume.

After assigning values to variables, we append them to the DataFrame and sort by volume. Be careful: keywords without a rank are returned as hyphens and may cause sorting errors.

Store Keyword Data from API Response

for x in range(total):
  keyword = kdata['data'][str(x)]["Ph"]
  current_rank = kdata['data'][str(x)]["Fi"]['*.' + domain + '/*']
  last_rank = kdata['data'][str(x)]["Be"]['*.' + domain + '/*']
  diff = kdata['data'][str(x)]["Diff"]['*.' + domain + '/*']
  diff30 = kdata['data'][str(x)]["Diff30"]['*.' + domain + '/*']
  volume = kdata['data'][str(x)]["Nq"]

  df = df.append({'Date':today,'Keyword':keyword, 'Current Rank':current_rank, 'First Rank':last_rank,'Diff':diff,'Diff30':diff30,'Volume':volume}, ignore_index = True)

df.sort_values(by=['volume'], inplace=True, ascending=False)

With the keyword data in a DataFrame, we’ll send it to the Google Sheet. The gspread library provides several options—see its documentation. You can update cells individually, but the Google Sheets API enforces limits (500 calls per 100 seconds), which can be problematic for many keywords. Sending the entire dataset at once (hence the DataFrame) is usually best.

Update Google Sheet

scope = ['https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("cred-file", scope)
client = gspread.authorize(creds_file)
sheet = client.open(sheet_name).sheet1

sheet.update([df.columns.values.tolist()] + df.values.tolist())

When the script runs, the Google Sheets API responds confirming success. The transfer is nearly instant and visible if you have the sheet open. Note: this operation overwrites the sheet; see the gspread documentation for append options.

The real benefit of this method is that you can automate it to run daily or weekly. You can use cron jobs on a Linux machine, or run it in the cloud with Google Cloud Functions and Google Cloud Scheduler. GCF and GCP setup are straightforward but beyond the scope of this tutorial.

Automate the Process

If your script runs correctly when executed manually, it’s time to automate it. Linux provides crontab for scheduling scripts. The crontab stores scheduled entries where you can specify execution times (hour, day, weekday, month, etc.). To edit the crontab, run:

crontab -e

This will likely open the crontab file in the vi editor. On a blank line at the bottom of the file, type the line below. This will run the script at midnight on Sundays. Use a cron time editor such as crontab.guru to customize the schedule. Replace the path with your script’s path.

0 0 * * SUN /usr/bin/python3 PATH_TO_SCRIPT/filename.py

If you want a log file to record each run, use this instead (customize the path):

0 0 * * SUN /usr/bin/python3 PATH_TO_SCRIPT/filename.py > PATH_TO_FILE/FILENAME.log 2>&1

Save the crontab file and you’re set. Remember that the machine must be on when the cron job is scheduled to run.

Conclusion

That’s it—you’ve exported keyword data from Semrush using their API. From here you can analyze the data in Google Sheets, import it into another platform, or use it for reporting. This approach can be adapted with little effort to other APIs such as Moz, ahrefs, Majestic, or Brightlocal. Now try it out and see what you can build. Follow me on Twitter and share your applications and ideas!

Special thanks to @TechWithTimm and @jdegbau for the inspiration.

Google Sheets and Semrush API FAQ

How can I update a Google Sheet using the Semrush Position Tracking API with Python?

Python scripts can make requests to the Semrush Position Tracking API and update a Google Sheet with the returned position-tracking data, enabling seamless integration for SEO analysis.

What Python libraries are commonly used for interacting with the Semrush Position Tracking API and updating Google Sheets?

Python’s requests library is typically used to call the Semrush API, and gspread facilitates interactions with Google Sheets to update spreadsheet data.

What specific data can be obtained from the Semrush Position Tracking API and updated in a Google Sheet using Python?

Python scripts can extract various position-tracking metrics from the Semrush API, including keyword rankings, search visibility, and related metrics. These can be written into specific cells or ranges in a Google Sheet.

Are there any considerations or limitations to be aware of when using Python to update a Google Sheet with Semrush Position Tracking data?

Consider API rate limits, proper authentication, and the structure of your Google Sheet. Ensure compliance with Semrush API usage policies when retrieving data.

Where can I find examples and documentation for using Python to update a Google Sheet with Semrush Position Tracking API data?

Refer to the official Semrush Position Tracking API documentation for detailed guides. Also consult the gspread documentation and Python resources for practical examples of updating Google Sheets with Semrush data.