APIGoogle Apps ScriptGoogle Docs

Semrush API Library in Google Sheets (Google Scripts)

By April 23, 2019 No Comments

Updated! Finally 😉 V2 is here and solves a lot of issues, adds caching and extra API calls.

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.

Get the script right away – just make a copy of the sheet and in the Helper Functions menu, choose Add Key  

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)

 

Installation (less than 2 minutes)

Official Github repository

Instructions:

  1. Make a copy of the Semrush Library Spreadsheet
  2. Wait until the script finished loading, you’ll get a little prompt
  3. In the top menu, click on Helper Functions and choose Add API Key
  4. Add your key and you’re ready to go
  5. To see the formula reference, in the Helper functions menu, click “show formula reference”


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 4 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”.
  • cache: this is a custom caching function that will store the result of your query for 25 minutes. Default is on, but you can force NO CACHE by using FALSE as a parameter


Understanding the caching function – Important!

There’s a new caching function that automatically caches each result from the API for 25 minutes.  The reason for this is to avoid consuming API credits for results you’ve already obtained or when you’re simply moving cells around.

Unless you’re explicitly setting the “cache” parameter to FALSE, all results will be cached for 25 minutes.

To clear the cache, in the Helper Functions menu select “Flush cache”

If you want to increase or decrease the cache time, in the script editor look for “cacheDefaultTime” and remember that this value is in seconds.

 

See the video for the full explanation:


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


Formula Reference

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

You can also access the formula reference directly from the Helper functions menu.

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

  • Semrush API reference: DOMAIN ORGANIC SEARCH KEYWORDS
  • Price: 10 API units per line Historical Price: 50 API units per line NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • This report lists keywords that bring users to a domain via Google’s top 20 organic search results.

Parameters:

NameExample ParameterDescription
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
date201601OPTIONAL Leave this blank for current data. YYYYMM format for historical reports, note: always reports on the 15th of the month.
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

2) DOMAIN_OVERVIEW_SEMRUSH(query, excludeHeaders, db, cache)

  • Semrush API reference: DOMAIN_RANK
  • Price: 10 API units per line, NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • This report provides live or historical data on a domain’s keyword rankings in both organic and paid search in a chosen regional database.

Parameters:

NameExample ParameterDescription
domain“example.com”REQUIRED The root domain, example: “nytimes.com”, DO NOT include protocol (http/https)
db“us”OPTIONAL The country database you want to search from. Default is US
date201601OPTIONAL Leave this blank for current data. YYYYMM format for historical reports, note: always reports on the 15th of the month.
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

3) KEYWORD_DIFFICULTY_SEMRUSH(query, excludeHeaders, db, cache)

  • Semrush API reference: KEYWORD DIFFICULTY
  • Price: 50 API units per line NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • 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:

NameExample ParameterDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
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.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

4) KEYWORD_VOLUME_SEMRUSH(query, excludeHeaders, db, cache)

  • Semrush API reference: KEYWORD OVERVIEW (ALL DATABASES)
  • Price: 10 API units per line, NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • This report provides a summary of a keyword, including its volume, CPC, competition, and the number of results in all regional databases.

Parameters:

NameExample ParameterDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
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.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

5) RELATED_QUERIES_SEMRUSH(query, excludeHeaders, limit, db, cache)

  • Semrush API reference: RELATED KEYWORDS
  • Price: 40 API units per line, NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • This report provides an extended list of related keywords, synonyms, and variations relevant to a queried term in a chosen database.

Parameters:

NameExample ParameterDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
limit10OPTIONAL The number of results. Default is 1
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.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

6) SERPS_SEMRUSH(query, limit, db, cache)

  • Semrush API reference: ORGANIC RESULTS
  • Price: 10 API units per line, NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • This report lists domains that are ranking in Google’s top 20 organic search results with a requested keyword.

Parameters:

NameExample ParameterDescription
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
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

7) PHRASE_QUESTIONS_SEMRUSH(query, limit, db, filterBySearchVolume, searchVolumeFilter, searchVolume, excludeHeaders, cache)

  • Semrush API reference:PHRASE QUESTIONS
  • Price: 40 API units per line, NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • The report provides a list of phrase questions relevant to a queried term in a chosen database.
NameTypeDescription
query“apartments”REQUIRED The keyword you want information for. Example: “brown shoes”.
limit10OPTIONAL The number of results. Default is 1
db“us”OPTIONAL The country database you want to search from. Default is US
filterBySearchVolumetrueOPTIONAL If you want to filter by search volume, use TRUE, default is FALSE
searchVolumeFilter“>”OPTIONAL Filters the search volume parameter (next). Use “>” for Greater than, or “<” for Less than. Default is “>” greater than
searchVolume1000OPTIONAL A search volume number (per month) you want to filter results by. Both previous parameters need to be set for this to work.
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

8) URL_ORGANIC_KEYWORDS_SEMRUSH(url, excludeHeaders, limit, db, cache)

  • Semrush API reference: URL ORGANIC SEARCH KEYWORDS
  • Price: 10 API units per line, NOTE: Prices are subject to change and you should visit the official Semrush API page to find out pricing.
  • This report lists keywords that bring users to a URL via Google’s top 20 organic search results.

Parameters:

NameExample ParameterDescription
url“http://example.com”REQUIRED The exact URL you want data for, MUST include protocol (http/https)
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
excludeHeaderstrueOPTIONAL true to EXCLUDE column headers or false to include. Default is false.
cache“TRUE”OPTIONAL use FALSE if you DO NOT want to cache these results or DO NOT want to return cached results, default is TRUE (cache enabled)

FAQ

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

You can’t get to the underlying code in an Add-on, I really dislike that. I want you to be able to manipulate, change, share, do whatever you want.  Apps Script and their applications are awesome, especially when they’re open source.

2) Are you going to add more API calls?

I did – Domain overview and Phrase Related, although I typically only use the ones I’ve already included. I didn’t include API calls to Paid search APIs because I’m an SEO 😉  Also, there’s also a bunch of other API calls that are available, but they’re expensive and I don’t think they’re very useful for SEOs.  In other cases, the keyword gap tool has an API call, but it’s so expensive that you’re far better off using the interface to do this, mainly because you’d have to play with the filters quite a bit to get what you want.  In the interface, that’s free, if I were to build the API call, you’d drain your credits in a heartbeat. 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 @ me on Twitter (@dsottimano).

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_SEMRUSH() 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 already have 95% of the code for a super special project that will turn Google Sheets into an SEO tool beast.  The problem is time folks, I need more hours in a day to continue learning and building this kind of stuff.  Hang tight, I’m getting there 😉

 

David Sottimano

About David Sottimano

Trying to make OpensourceSeo.org the best free information hub for the SEO industry. Personal Website here