APIGoogle Apps ScriptGoogle Docs

Semrush API Library in Google Sheets (Google Scripts)

Update as of July 7, 2017: Changed naming convention to all CAPS for all formulas, improved error handling for API errors

Semrush.com in my opinion, is one of the best SEO tools on the market. I especially love their API and the data they provide.  I also live in Spreadsheets, so I decided to combine the two in this project.

From now on, you’re only an Excel (Google sheets) formula away from some of the best data in the industry. This is amazing, get excited!

Get the script right away  

Note: This requires a paid account or API subscription, if you already have an account with API access, you can use this library. If not, sign up to Semrush (disclosure: that’s an affiliate link. Please and thank you)

Guide (quick links)

Installation (less than 5 minutes)

Official Github repository

Instructions:

  1. Copy Github script and paste into new Google sheet script editor (from the Tools menu, choose script editor)
  2. Replace “apikey” with your Semrush.com API key
  3. Hit save, name your new script
  4. Go back to the spreadsheet, refresh the page
  5. In the top menu of the Spreadsheet, you’ll see “Semrush Functions”, click on it, choose “check api balance”
  6. Authorize with your Google account
  7. All done!


Your first formula

Here, we’re using the =KEYWORD_VOLUME_SEMRUSH() formula to return Semrush.com’s keyword volume statistics for a specific keyword

=KEYWORD_VOLUME_SEMRUSH takes in 3 arguments:

  • query: the keyword you want data for. This is the only required parameter
  • excludeHeaders: this displays the column headings from the API, set it to True if you want to exclude the column header
  • database: search in any semrush.com database, for example, “es” for Spanish, “it” for Italian. Default is “us”.

 


A deeper explanation using the most complicated formula, DOMAIN_ORGANIC_KEYWORDS_SEMRUSH()


Tips and tricks


Formula Reference

You can also see the reference on the Github repository here: https://github.com/dsottimano/semrush-api-in-google-sheets

1) DOMAIN_ORGANIC_KEYWORDS_SEMRUSH(domain, filterBy, matchType, query, limit, db, excludeHeaders, date)

  • Semrush API reference: DOMAIN ORGANIC SEARCH KEYWORDS
  • Price: 10 API units per line Historical Price: 50 API units per line
  • This report lists keywords that bring users to a domain via Google’s top 20 organic search results.

Parameters:

NameExampleDescription
domain“example.com”REQUIRED The root domain, example: “nytimes.com”, DO NOT include protocol (http/https)
filterBytrueOPTIONAL Use true to include the query in the filter or false to remove the query in the filter.
Default is true
matchTypetrueOPTIONAL Use true for partial match, use false for exact match. Default is true, partial match
query“apartments”OPTIONAL The keyword you want to filter by. Relies on previous 2 parameters. Example: “brown
shoes”.
limit10OPTIONAL Number from 1 to 10000
db“us”OPTIONAL The country database you want to search from. Default is US
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
date201601OPTIONAL Leave this blank for current data. YYYYMM format for historical reports, note: always
reports on the 15th of the month.

2) KEYWORD_DIFFICULTY_SEMRUSH(query, excludeHeaders, db)

  • Semrush API reference: KEYWORD DIFFICULTY
  • Price: 10 API units per line
  • This report provides keyword difficulty, an index that helps to estimate how difficult it would be to seize competitors’ positions in organic search within the Google’s top 20 with an indicated search term.

Parameters:

NameExampleDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
db“us”OPTIONAL The country database you want to search from. Default is US
3) KEYWORD_VOLUME_SEMRUSH(query, excludeHeaders, db)
  • Semrush API reference: KEYWORD OVERVIEW (ALL DATABASES)
  • Price: 10 API units per line
  • This report provides a summary of a keyword, including its volume, CPC, competition, and the number of results in all regional databases.

Parameters:

NameExampleDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
db“us”OPTIONAL The country database you want to search from, default is “us”
4) RELATED_QUERIES_SEMRUSH(query, excludeHeaders, limit, db)
  • Semrush API reference: RELATED KEYWORDS
  • Price: 40 API units per line
  • This report provides an extended list of related keywords, synonyms, and variations relevant to a queried term in a chosen database.

Parameters:

NameExampleDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
limit10OPTIONAL The number of results. Default is 1
db“us”OPTIONAL The country database you want to search from. Default is US
5) SERPS_SEMRUSH(query, limit, db)
  • Semrush API reference: ORGANIC RESULTS
  • Price: 10 API units per line
  • This report lists domains that are ranking in Google’s top 20 organic search results with a requested keyword.

Parameters:

NameExampleDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
limit10OPTIONAL Number from 10 to 20, for number of results. Default is 10
db“us”OPTIONAL The country database you want to search from. Default is US
6) URL_ORGANIC_KEYWORDS_SEMRUSH(url, excludeHeaders, limit, db)
  • Semrush API reference: URL ORGANIC SEARCH KEYWORDS
  • Price: 10 API units per line
  • This report lists keywords that bring users to a URL via Google’s top 20 organic search results.

Parameters:

NameExampleDescription
url“http://example.com”REQUIRED The exact URL you want data for, MUST include protocol (http/https)
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
limit“10”OPTIONAL Number from 1 to 10,000, for number of results
db“US”OPTIONAL The database, example “US” for American database. Default is US

FAQ

1) Why did you build it like this, why didn’t you build it as a Google sheet add-on?

Add-ons don’t show the code, I really dislike that. I want you to be able to manipulate, change, share, do whatever. Scripts are awesome, especially when they’re open source.

2) Are you going to add more API calls?

Most likely, although I typically only use the ones I’ve already included. If there’s a strong demand for a specific API call, I’m happy to include it.

3) Something broke, can you fix it?

If you’ve changed the script, then no. If you haven’t changed the script and it’s not working, Tweet @dsottimano right away please.

4) Can I suggest improvements?

Yes, please do. You can submit a pull request on Github or comment below.

5) Can I change the default database to a different country?

Yes, and very easily too. In the script editor, use CTRL + F to look for “DEFAULT_DB”, you will see it’s value set as “us”. Set it to your preferred country as default, example: DEFAULT_DB: “es” for Spain, DEFAULT_DB: “uk” for United Kingdom.

6) When I run the serps() formula for organic search results, what date am I looking at?

The most recent from Semrush.com, whenever that may be. Unfortunately, they don’t return a date stamp when these results were gathered.

7) I’ve seen other people use =importdata and =importxml for APIs, why did you use those?

They are unstable, some days they work, some days they don’t. Also, I don’t have control of the requests, I don’t really know how they work and I wanted to be able to handle each step of the process, including error handling.  I prefer to use the UrlFetchApp class to make requests, because it’s solid. I’m not opposed to importxml, in fact, I wrote a guide a long time ago – it’s just lost it’s touch.

8) Are you going to add more API calls or additional scripts on this site?

Yes, absolutely.  I think the next  few will be the PageSpeed API and Sistrix.

 

David Sottimano

About David Sottimano

Founder of OpensourceSeo.org. Strategist at definemg.com

Leave a Reply