Home ยป Use the SEMRush keyword volume API in Google Spreadsheets

Use the SEMRush keyword volume API in Google Spreadsheets

  • by
  • 3 min read

As I explained in a previous post, the IMPORTDATA function from Google Spreadsheets has its limitations. In this blog post I demonstrate how you can interact with APIs by writing your own functions using the script editor, with a real-life example.

A typical use case for SEMRush is pulling the “search volume” from a ton load of possible keywords. It’s commercially interesting to know what people are looking for, and more specifically: how they look for it. It’s these search terms that you want your website to rank well on.

SEMrush offers this feature also through an API. Allowing you to get the search volume for thousands and thousands of keywords. With Google Spreadsheets you can even to this in a simple table.

One way to do it is interacting with the API through the IMPORTDATA function, but you’ll quickly run into the hard limit of 50 usages per spreadsheet, The following error will be shown:”

“Loading data may take a while because of the large number of requests. Try to reduce the amount of IMPORTHTML, IMPORTDATA, IMPORTFEED or IMPORTXML functions across spreadsheets you’ve created.”

That is why I advise writing your own functions through the script editor. In the following snippet I created a function that you can pass a keyword and a country, to get the search volume for a keyword in a specific country. I’ll walk you through it below.

function SEOVOLUME(keyword, country) {
  var response = UrlFetchApp.fetch('https://' + country + '.api.semrush.com/?action=report&type=phrase_this&key=<INSERT_YOUR_KEY>&display_limit=1&export=api&export_columns=Nq&phrase=' + keyword);
  var responseData = response.getBlob().getDataAsString();
  var data = Utilities.parseCsv(responseData, ',');
  volume = (data == 'Nothing found') ? 0 : Number(data[1]);
  return volume;
}
  • First I load the variable response with the response from the API. Don’t forget to insert your own API Key!
  • Next, the response gets translated to plain text, for you and me to read.
  • Then, we load the variable data, with the plain text translated to something that looks like a very simple tabel.
  • The volume variable can be loaded through one of two ways. If the API returns it found nothing, we set volume to 0. If the keyword actually has some search volume, we select the second (JavaScript is 0-based, so data[1] is the second item) field from the table. The first item in the table are the words “Search Volume” that are returned by the API.
  • Finally, the volume is returned.

You can now use the SEOVOLUME function in your spreadsheet and pass it a keyword and a country code. I hope you will use this script, and let me know if you need some modifications.

Good luck!

Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.

Leave a Reply

Your email address will not be published. Required fields are marked *