Note: the new version of DataForSEO API Connector for Google Sheets is available.

Building a piece of SEO software can be difficult. Pulling together all the data points, reasoning out the architecture, and designing the interface takes an awful lot of time and effort. To make things worse, the investment can turn out completely unnecessary if you don’t plan to take your software project to the market. Think in-house SEO teams, digital marketing agencies and researchers: having a solution that is doing its job without spending extra on things like UI and infrastructure would be enough for a lot of them. We can’t say if that would be a better solution for everyone, but it’s definitely more cost-effective.

There are quite a few workarounds for collecting and visualizing data you get via DataForSEO APIs without having to write a single line of code: you can run a simple script on a server, use Postman with ready-made examples, or turn to Google Sheets and Excel. While the first two are useful for testing the results of the APIs, Excel and its Google’s counterpart provide a great way to not only collect but also process, analyze, and visualize data.

In this article, we’ll show you how to use Google Sheets to get all the essential SEO data right into your spreadsheet.

API Connector

First things first, you’d need to connect DataForSEO to Google Sheets. Fortunately enough, there’re several API connectors available for free. In this article, we’ll be using the API Connector for Google Sheets from Mixed Analytics. However, note that its free plan is limited and will allow sending up to ten API requests a day only.

Click here to get the Google Sheets Add-on.

Let’s start by creating a new blank Google Sheet and renaming it: DataForSEO API Example. Click on the Add-Ons tab at the top menu and select the API Connector. Then click Create New API Request.

The next step is creating an API request in the right-hand window of your Google Sheet. But first, you’d need to decide which API endpoint to use. DataForSEO supports two modes of data retrieval: Live Mode for getting instant responses through the POST method and the queue-based mode, which requires making both POST and GET requests for setting a task and collecting results. Let’s start with a more straightforward Live mode of the SERP API.

1 Open up the DataForSEO Docs and find the Live SERP endpoint. Then copy the POST request URL and paste it into the API URL Path field of the API connector. Make sure you selected the POST method.

2The next step is a little bit more complicated. You should type in the headers, indicating the content type and the authorization token.

• The first row should contain the duplicate authorization header which will be overwritten when you make a request — type in “Authorization” in the first column and
  “Basic bG9naW46cGFzc3dvcmQ=” in the second.
• In the second row, you’d have to specify the Content-Type. Again, type in “Content-Type” in the first column and “application/json” in the second.

The last row sets out the authorization header, which is unique to your DataForSEO account. Essentially, your user name and password are encoded using the Base64 format. You can generate it yourself using a simple encoder: put your username/email and password in a single line separated by the colon.
For example, here’s how the input and output would look like for our random sample account:

API Login: [email protected]
API Password: YqI5klFOaMpn7ir
 
Input: [email protected]:YqI5klFOaMpn7ir
Encoded: am9obmRvZUBkYXRhZm9yc2VvLmNvbTpZcUk1a2xGT2FNcG43aXI=

You can then post the encoded login and password into the Value column of the third row, right after specifying the “Basic” authorization type. The “Authorization” goes as a “Key” for that.

3 After figuring out the headers, you can set up the body of your Post Request in the JSON format. In this example, we used a sample from Postman:

{
	"data":
		{
			"here is supposed to be your post ID 1":
				{
					"se_name":"google.co.uk",
					"se_language":"English",
					"loc_name_canonical":"London,England,United Kingdom",
					"key":"rank checker"
				}
		}
}

4 Now we have to choose the destination tab, which is identical to the name of the spreadsheet and make up a name for our request. Also, note that the output type should be set to “Overwrite.”

You can either Run the created request right now or Save it for later.

5 After clicking Run, you’ll see the structured results sorted out into the relevant cells.

Here’s a link to this spreadsheet so that you can compare the information included against that of Postman, for instance. As you can see, the data is identical, but Google Sheets is more structured and thus easier to analyze and work with in general. For example, you can sort and filter your data using the Google Sheet’s built-in feature.

Furthermore, this API Connector makes it possible to schedule requests and automatically run them every hour, every day, or every week. To create a new trigger, switch to the Schedule tab and create a new trigger by selecting the API request name and choosing the request’s time and frequency. Let’s schedule it to 8-9 a.m. every Monday. Don’t forget to push the Save button in the end. You can also see all the created triggers, edit or delete them from within the Manage Triggers tab.

How to manage endpoints that require both POST and GET methods?

We’ve already figured out how to use the API connector for Google Sheets for processing live data requests. However, many DataForSEO APIs require POST and GET requests to be sent separately, using the task_id parameter.

Let’s take the Bulk Keyword Search Volume of the Keyword Data API, for example. This endpoint provides for two methods of data retrieval – live and queue-based, with the latter one requiring both POST and GET requests.

1Head to the Search Volume endpoint of the DataForSEO Docs and find the POST URL for setting the task. Copy it and paste it into the API URL path field of the API connector.

2 Repeat the second step from the previous part to fill out the Headers table.

3 Set up the body of your Post Request in the JSON format. See our documentation to learn more about structuring your request. Alternatively, you may rely on Postman examples.

Here’s the sample we used:

{
	"data":
		{
			"your post_id parameter here":
				{
					"language":"en",
					"loc_name_canonical":"United States",
					"keys":
						[
							"repeat customers",
							"best sleeping wireless earbuds",
							"staniel cay day trip",
							"iota hoodie",
							"monero hat"
						]
				}
		}
}

Then, type in the name of the spreadsheet you’re working in and assign a name to the created request.

4Save your request and try running it. You’d need a cell containing the task_id to retrieve data – it’s the E2 in our case.

5 Now, create another spreadsheet for running the GET request. Headers would remain the same. The API URL path can be copied from the corresponding section of the docs – the tricky part is that here you should append the cell containing the task_id. Here’s how the URL looks like in our example:

GET https://api.dataforseo.com/v3/keywords_data/google_ads/search_volume/task_get/$id

6 Run the created request after saving it.

As you see, now we have all the keyword data broken down into the relevant cells of a spreadsheet, where we can format, analyze, and visualize it.

Try it for yourself!

API Connector provides a convenient way to use our service without having to write a single line of code. Combining the powerful features of Google Sheets with DataForSEO APIs, you can take your SEO analytics to a whole new level.

However, the API Connector we used in this article is provided by a third-party and hence may cause some inconveniences when it comes to framing and structuring data. Our development team is already working on the new, custom-tailored version of Google Sheets, which would be much easier to work with.

In the meantime, we invite you to try out the available сonnector and submit your feature requests to [email protected].