Submit post
Go to Blog

How To Use the SEMrush API in Google Sheets

Brian Jensen
How To Use the SEMrush API in Google Sheets

SEMrush is my favorite competitive research tool that I use for everything from organic and paid keyword research to understanding market share in a specific region. A few months ago, I was working on a large project where it made sense to look beyond the SEMrush interface and pull data directly from their servers via the SEMrush API. The problem was that I’d never used an API before and wasn’t able to locate any resources for a noob like myself to get started.

As luck would have it, I work with some pretty smart people who were able to point me in the right direction. Now that I understand the basics, I want to pay it forward and create a resource for others who use SEMrush, but not familiar with using the API.

What is an API?

Let’s start at the top. An application programming interface as defined by Wikipedia is “a set of routines, protocols, and tools for building software applications.” APIs allow different applications to communicate with databases and many of the social tools digital marketers use on a daily basis are built using APIs. The SEMrush API allows users to pull data directly from their servers. It can make large scale analysis a breeze.

Locating Your SEMrush API Key

Before we get started with examples, you’ll need to locate your API key and number of remaining credits which can be found by navigation to the API documentation page.

Find your SEMrush API key

Using the SEMrush API in Google Sheets

I use Google Sheets frequently to create and collaboratively edit spreadsheets with other users. To provide you with an example of how to use the application and SEMrush’s database via the API, let’s pretend I’m working on a keyword research project with a co-worker in which we’ll need search volume CPC, competition and total number of Google results for a list of keywords.

SEMrush offers a wide variety of reports that include paid, organic, overview, domain, URL reports and more. We’ll be using the Keyword Overview report that will allow us to pull data for all the metrics I’ll need for my keyword research project. Let’s dissect the request example for this report to provide us with a better understanding of the data we’ll be pulling into our Google spreadsheet.

Parts of the report request example

  1. The specific type of report you’d like to request
  2. Your API key number
  3. The report’s columns
  4. The phrase we are researching
  5. The database that we’ll be pulling in data from

Now that we have our API key, the report we’d like to run and a new spreadsheet we’ve created in Google Sheets, it’s time to review a few spreadsheet functions that will make it possible to retrieve and organize the data.


Just like in Excel, Google Spreadsheets supports a wide array of functions. For this tutorial, we’ll be using IMPORTDATA to retrieve data from SEMrush.


The syntax for the function is IMPORTDATA(url), and the value for url must be enclosed in quotes or reference a cell. The Keyword Overview report request example using IMPORTDATA would look similar to the following:

=IMPORTDATA(" &export_columns=Ph,Nq,Cp,Co,Nr&phrase=keyword&database=us")

Because we’re efficient and we’d like to be able to drag through multiple rows in our spreadsheet, we can replace “keyword” with the cell we’d like to reference in the request i.e. “&A1&”.

Replace the keyword with the cell you'd like to reference

Using the TRANSPOSE Function

By default, SEMrush will populate your data vertically down rows. Using a nested function with the transpose feature will populate your data horizontally across columns. Creating a nested function using TRANSPOSE and IMPORTDATA would look similar to the following request:

=transpose(importdata(" &export_columns=Ph,Nq,Cp,Co,Nr&phrase=keyword&database=us"))

Using the SPLIT Function

Another Google Sheets function that you may find useful for working with your data is Split. An easy way to think about this function is that Split to Google Sheets is basically what Text to Columns is to Excel. The definition provided in Google Sheets is that this function “will help you divide text around a specified character or string and places each fragment into a separate cell in the row.”

The split function

Similar to Excel, there are many ways to accomplish the same task in Google Sheets and a plethora of different ways to organize and format your data in a way that works best for you.

Try a few different reports on your own and experiment with adding or removing export columns to get the data you need for your project.

That’s it, easy right? With the help of the IMPORTDATA function, using the API in Google Sheets can be a major time saver. Please leave any insight or tips you’d like to contribute in the comments below!

Like this post? Follow us on RSS and read more interesting posts:

Brian Jensen is the CEO of Congruent Digital, a full-service marketing agency specializing in cross-channel digital marketing solutions. When he’s not working, you can find him reading a book, riding his bike, or playing online chess. Try Congruent Digital's free Tweet Indexation Rate Tool here.
Share this post


2000 symbols remain
Hey there!

For some reason, the only data Google Sheets displays in the cell are the names of the columns entered instead of the data itself.

Any insight would be appreciated!
test comment form
Hello, thanks for your tutorial. Very useful.

However, for me it is not working when I try it on a lot of domains in the same sheet.
I have hundreds of formula with the following query:

When I enter it on the browser, no problem. It gives me results. But when I use it with the IMPORTDATA function, I have got a "Loading" forever.

It seems that when I have a few queries on the Google sheet, it works fine, but with more than 10, it is not working.

Does anybody have an idea of how I could solve this?

Thanks very much.
Gorioux Edouard
Hello Gorioux,

This might to be an issue with the big databases, in cases like this it is recommended to lower the amount of requested data. If you have any other issue please feel free to contact SEMrush support by email to [email protected] or use the contact form.
Boris Sobkovich
Ok i sent an email and it is currently under investigation.
Even with 10 requests at the same time, it does not work
Luis Revuelto
Hi again! I´ve just found the answer, it was my mistake. I was requesting a phrase report but I must request a url organic report. Here is my new query.,Po,Nq,Cp,Co,Tr,Tc,Nr&url="&B1&"&database=us"). Where B1 is the cell where I have the different URLs I want to track. Hope I have explained it well, my english is not good :(
Luis Revuelto
Hi Brian! Thanks for your tutorial. I´m trying semrush api with google sheets. But I found a mistake in the results thrown. I choose the following columns: Ph,Po,Cp,Co,Nr. But the Po colum always throws the number 1. I mean, it is as if my domain always appear in first position when it is not true. This was my full query in Google Sheet =transpose(IMPORTDATA(",Po,Cp,Co,Nr&phrase="&A2&"&"))
Is there a way I can try API on GURU package? I'm trying to figure out if there is a difference between pulling data for https vs http versions like [link removed by moderator] vs [link removed by moderator].
Thank's Brian for the info. I got one issue that I can't figure out. I want to replace the actual domain name "" with a reference to a domain name in a cell. Replacing "" with "domain=A1" does not work. Any tips?
I =transpose(IMPORTDATA(" &type=domain_ranks&"))
We've added SEMrush as a data source in our Supermetrics add-on for Google Drive ( Using the add-on makes it very simple to fetch SEMrush data into Google Sheets.
Gaurav Agarwal
Mikael Thuneberg
Have been trying to use Supermetrics and struggling while using it for SemRush. Have asked in the forums, how can i avail help ?
Gaurav Agarwal
Gaurav Agarwal
I have received a reply on the forum. Thanks. If you have a better way to reach out for support, pls apprise.
Will try the tool and review here !
David Black
If you like this, you should also check out a webinar we (SEMrush) did back in December of 2014 with Sean Malseed. Founder of CircleRank and creator of An entire repository dedicated to this stuff!
Joe R.
Do you have a Google Sheet template with all of this configured that you can share so we can copy?
Brian Jensen
Joe R.
Joe that's a great idea, but unfortunately I don't. The cool thing about the SEMrush API documentation is that it provides request examples below every report with your API key, so most of the legwork is already done for you.

If you want to reference cells, just remember to replace keyword or domain with "&A1&" (assuming A1 is the cell you're referencing). If you get stuck, feel free to DM on Twitter and I'll be happy to help however I can.
Matt LaClear
No doubt, webmasters and site owners work hard to build impressive analyses with their data. Sometimes, it’s still difficult to analyze. Running reports with a new spreadsheet in Google Sheets can make the job easier, that is if we can learn how to organize and retrieve the data better. It’ll take a little while to digest this info, but thanks for making a heavy task a little lighter!
Brian Jensen
Matt LaClear
My pleasure Matt and I couldn't agree more that using SEMrush's API can make a lot of analysis light work!

Send feedback

Your feedback must contain at least 3 words (10 characters).

We will only use this email to respond to you on your feedback. Privacy Policy

Thank you for your feedback!