Analyze Backlinks in One Click with DataForSEO and Google Sheets Apps Script
Backlink analysis is one of the pillars of successful SEO. Understanding your website’s backlink profile is essential for maintaining strong search engine rankings, finding toxic links, and discovering ways to improve off-page SEO. Additionally, analyzing your competitors’ backlinks can help you spot gaps in your link-building strategy, identify valuable linking domains, and understand the factors influencing competitors’ search rankings.
However, manually performing backlink analysis is tedious and time-consuming, especially when dealing with multiple domains. While conventional SEO tools offer robust backlink tracking features, they often come with high subscription costs that may not be ideal for everyone.
Fortunately, you can create a custom backlink analysis tool with DataForSEO APIs and get backlink data on multiple domains straight into Google Sheets.
In this tutorial, Antonio Blago, an experienced SEO expert, explains how to create a simple yet effective backlink tool in Google Sheets that can analyze multiple domains with one click.
Antonio uses the following technologies to create this tool:
1. Backlinks Summary endpoint of Backlinks API: provides actual information on backlinks for any domain, including domain rank, backlinks quantity, and backlink types.
2. Google Sheets: a Google service for creating customizable, multi-purpose datasheets. It serves as backlink data storage for this tool.
3. Google Apps Script: a cloud-based JavaScript extension by Google that lets you use custom scripts to automate tasks in Google products.
By combining these technologies, you can build a fully functional backlink analysis tool with customization options. DataForSEO Backlinks API analyzes specified domains and delivers backlink data to Google Sheets. The Apps Script extension serves as the backbone of this tool, enabling automatic data retrieval from the Backlinks API and organizing the data properly in the sheet.
How to create a backlink tool with DataForSEO API and Google Sheets Apps Script
Step 1: Create a DataForSEO account and get API credentials
First, create an account at DataForSEO to get your API credentials. After registration, you’ll receive an email with a confirmation link to activate your account. Additionally, you’ll get $1 to your account, enough to test the backlink tool you’re about to build.
After logging in, navigate to the API Access section of the DataForSEO Dashboard. Here, you can find your API username (your email address) and automatically generated password. Additionally, you can send your credentials via email.
To use your API credentials in external services, like Google Apps Script, you need to encode them into Base64 format. In this format, your username and password are encoded into a string , which is used in authorization services. To encode your credentials, you can use any available Base64 encoder. Remember that credentials, even encoded, give full access to your DataForSEO resources, so don’t expose them publicly.
Step 2: Test your DataForSEO API access
To test access to DataForSEO APIs, you can use an API Playground directly in the DataForSEO app or make a request using cURL. Let’s see an example of a cURL request to the Backlinks Summary endpoint, which is used in the backlink tool we create.
curl --location --request POST 'https://api.dataforseo.com/v3/backlinks/summary/live' \
--header 'Authorization: Basic bG9naW46cGFzc3dvcmQ=' \
--header 'Content-Type: application/json' \
--data-raw '[{
"target": "beispielseite.de",
"backlinks_status_type": "live",
"include_subdomains": true,
"exclude_internal_backlinks": true,
"include_indirect_links": true
}]'
The cURL POST request consists of:
- Target URL: URL of the target endpoint for making a request;
- Authorization header: contains your API credentials, encoded in Base64 format;
- ‘Content-Type: application/json’ header: signals to the server that you are sending JSON data;
- ‘data-raw’ payload: an array of objects in which you specify your request parameters (i.e., your domain and filtering options).
You can execute this request using a terminal, like PowerShell (for Windows), or using Postman application, for example. You can also test the Backlinks Summary and other endpoints for free using the Sandbox feature. To make a request in this mode, change the domain name in the request URL to sandbox.dataforseo.com. Learn more about the Sandbox feature in this Help Center Article.
After execution, you’ll receive the response in JSON:
{
"version": "0.1.20241227",
"status_code": 20000,
"status_message": "Ok.",
"time": "0.1678 sec.",
"cost": 0.02003,
"tasks_count": 1,
"tasks_error": 0,
"tasks": [
{
"id": "02252145-1535-0265-0000-f0239068af0f",
"status_code": 20000,
"status_message": "Ok.",
"time": "0.0131 sec.",
"cost": 0.02003,
"result_count": 1,
"path": [
"v3",
"backlinks",
"summary",
"live"
],
"data": {
"api": "backlinks",
"function": "summary",
"target": "dataforseo.com",
"internal_list_limit": 10,
"backlinks_status_type": "live",
"include_subdomains": true,
"exclude_internal_backlinks": true,
"include_indirect_links": true
},
"result": [
{
"target": "dataforseo.com",
"first_seen": "2019-01-15 23:54:14 +00:00",
"lost_date": null,
"rank": 336,
"backlinks": 6653737,
"backlinks_spam_score": 25,
"crawled_pages": 5103,
"info": {
"server": "nginx/1.10.1 (Ubuntu)",
"cms": "wordpress",
"platform_type": [
"cms",
"blogs"
],
"ip_address": "138.201.30.30",
"country": "US",
"is_ip": false,
"target_spam_score": 25
},
"internal_links_count": 764788,
"external_links_count": 33803,
"broken_backlinks": 1814,
"broken_pages": 64,
"referring_domains": 3132,
"referring_domains_nofollow": 833,
"referring_main_domains": 2773,
"referring_main_domains_nofollow": 658,
"referring_ips": 2173,
"referring_subnets": 1536,
"referring_pages": 2545774,
"referring_pages_nofollow": 3214,
"referring_links_tld": {
"de": 2224378,
"jetzt": 218924,
"com.br": 80756,
"com": 10472,
"top": 3589,
"org": 1199,
"dreamhosters.com": 1188,
"eco": 1041,
"net": 761,
"pl": 551
},
"referring_links_types": {
"anchor": 1601375,
"image": 713375,
"redirect": 229571,
"canonical": 1453
},
"referring_links_attributes": {
"noopener": 4009,
"nofollow": 3212,
"noreferrer": 1387,
"external": 956,
"ugc": 667,
"tag": 15,
"sponsored": 6,
"author": 3,
"bookmark": 1
},
"referring_links_platform_types": {
"organization": 1423930,
"unknown": 882021,
"blogs": 8795,
"cms": 8707,
"news": 3434,
"message-boards": 326,
"wikis": 287,
"ecommerce": 255
},
"referring_links_semantic_locations": {
"": 2532564,
"article": 5102,
"figure": 3196,
"aside": 1359,
"section": 1267,
"footer": 1207,
"main": 870,
"header": 158,
"details": 23,
"nav": 22
},
"referring_links_countries": {
"DE": 2224492,
"": 235053,
"BR": 80766,
"US": 1297,
"YT": 801,
"PL": 551,
"CC": 533,
"WW": 436,
"UA": 300,
"IO": 263
}
}
]
}
]
}
The result array of the response contains structured data on analyzed domain, such as backlinks quantity, backlinks spam score, types of referring domains and more.
Now that you have obtained API credentials and tested your API access, let’s move on to Google Sheets to assemble the backlink tool.
Step 3: Create a datasheet and configure the API connection
Now, we will create and configure a datasheet where you will put domains to analyze and receive backlink data from the Backlink Summary endpoint. Log in to your Google account to get access to Google Sheets.
Then, make a copy of a ready-made backlink tool datasheet template by Antonio Blago.
The empty datasheet looks like this. It has Domain and Target columns, where you specify the domains you want to analyze, and the Fetch Backlink Data button to run the analysis script. Besides, it has separate columns for backlink data and metrics, such as domain rank, number of backlinks, spam score, broken pages, etc. After the analysis, these columns will be filled with the respective data for each domain.
Next, click the Extensions button in the menu above and tap on the Apps Script button to enter the Google Apps Script extension.
The blank project template will appear when you enter the Apps Script extension.
➤ Here, navigate to the left and click the Project Settings tab;
➤ In the Project Settings tab, scroll down to the Script Properties section and click Add script property;
➤ Write API_KEY in the Property field;
➤ In the Value field, copy your API credentials formatted to Base64 string;
➤ Click Save script properties.
Workflow example:
In this way, you created an API connection to Google Apps Script, which will be used to activate a script to analyze domains and get backlink data.
Step 4: Create a script to enable the backlink tool
The next step is to create a script to analyze domains and pull the backlink data using the Backlinks Summary endpoint. The complete script consists of two separate scripts:
- The execution script interacts with the Backlinks Summary endpoint to retrieve data and organize it in the datasheet;
- The heatmap script highlights the cells with shades of red depending on the data value in each cell. This script helps better identify the differences in data for various domains.
1. Add the execution script
➤ In Apps Script, select the Editor tab;
➤ Click the Plus button in the Files field to create a script file. Give the file a meaningful name;
➤ In the blank space, paste the following execution script;
function getBacklinksForAllDomains() {
// 1) Your active spreadsheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// 2) Determine the last row with data
var lastRow = sheet.getLastRow();
// If there are domains in rows 5 to lastRow, <5 means no entry
if (lastRow < 5) {
Logger.log("No domains found in column A (below row 5).");
return;
}
// 3) Enter headers in one row above (e.g., row 4)
// WARNING: This will overwrite existing data in row 4, column B, etc.
var headers = [
"Target",
"Rank",
"Backlinks",
"Spam Score",
"Crawled Pages",
"Internal Links Count",
"External Links Count",
"Broken Backlinks",
"Broken Pages",
"Referring Domains",
"Referring Domains Nofollow",
"Referring Main Domains",
"Referring Main Domains Nofollow",
"Referring IPs",
"Referring Subnets",
"Referring Pages",
"Referring Pages Nofollow"
];
// Headers in row 4 starting from column B
sheet.getRange(4, 2, 1, headers.length).setValues([headers]);
// 4) Load API key from the script properties
var apiKey = PropertiesService.getScriptProperties().getProperty('API_KEY');
if (!apiKey) {
Logger.log("Error: No API_KEY found in the script properties.");
return;
}
// 5) Iterate over all rows from row 5
// Column A contains the domains/URLs
for (var row = 5; row <= lastRow; row++) {
// A) Get domain/URL from column A (row)
var domain = sheet.getRange(row, 1).getValue();
if (!domain) {
Logger.log("Row " + row + ": No domain found, skipping.");
continue;
}
// B) Prepare API request
var url = "https://api.dataforseo.com/v3/backlinks/summary/live";
var payload = [
{
"target": domain,
"internal_list_limit": 10,
"backlinks_status_type": "live",
"include_subdomains": true,
"exclude_internal_backlinks": true,
"include_indirect_links": true
}
];
var options = {
method: "post",
contentType: "application/json",
headers: {
"Authorization": "Basic " + apiKey
},
payload: JSON.stringify(payload),
muteHttpExceptions: true
};
// C) Execute request
var response;
try {
response = UrlFetchApp.fetch(url, options);
} catch (error) {
Logger.log("Error in row " + row + ": " + error);
sheet.getRange(row, 2).setValue("Error: " + error);
continue;
}
// D) Check HTTP code
var httpCode = response.getResponseCode();
var responseText = response.getContentText();
if (httpCode !== 200) {
Logger.log("Row " + row + ": HTTP-" + httpCode + " | " + responseText);
sheet.getRange(row, 2).setValue("HTTP-" + httpCode + " | " + responseText);
continue;
}
// E) Parse response
var data = JSON.parse(responseText);
// DataForSEO structure: data.tasks[0].result[0]
if (!data.tasks || !data.tasks[0] || !data.tasks[0].result || !data.tasks[0].result[0]) {
Logger.log("Row " + row + ": No valid data in tasks[0].result[0].");
sheet.getRange(row, 2).setValue("No data");
continue;
}
var resultItem = data.tasks[0].result[0];
// F) Compile values (matching the headers array)
var rowValues = [
resultItem.target || "",
resultItem.rank || "",
resultItem.backlinks || "",
resultItem.backlinks_spam_score || "",
resultItem.crawled_pages || "",
resultItem.internal_links_count || "",
resultItem.external_links_count || "",
resultItem.broken_backlinks || "",
resultItem.broken_pages || "",
resultItem.referring_domains || "",
resultItem.referring_domains_nofollow || "",
resultItem.referring_main_domains || "",
resultItem.referring_main_domains_nofollow || "",
resultItem.referring_ips || "",
resultItem.referring_subnets || "",
resultItem.referring_pages || "",
resultItem.referring_pages_nofollow || ""
];
// G) Write to column B and onward in the same row
sheet.getRange(row, 2, 1, rowValues.length).setValues([rowValues]);
Logger.log("Row " + row + ": " + domain + " successfully queried.");
}
// Done
Logger.log("Done! All rows up to row " + lastRow + " processed.");
// 2) Then apply the heatmap
applyHeatmapCtoR();
}
2. Add the heatmap script
➤ Click the Plus button in the Files field again to create another script file. Give the file a meaningful name;
➤ In the blank space, paste the following heatmap script;
function applyHeatmapCtoR() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// 1) Determine the last row with data
var lastRow = sheet.getLastRow();
if (lastRow < 5) {
Logger.log("No data found below row 5.");
return;
}
// 2) Define the columns (C=3 to R=18).
// You can also create this list dynamically using a loop.
var columns = [3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18];
// 3) Load existing conditional formats
// If you want to remove all old formats, use:
// var rules = [];
var rules = sheet.getConditionalFormatRules();
// 4) Iterate over each column
columns.forEach(function(col) {
// A) Define range: from row 5 to lastRow, 1 column wide
var numRows = lastRow - 4;
var range = sheet.getRange(5, col, numRows, 1);
// B) Define a simple 2-color rule
// Sheets automatically determines the minimum and maximum value
// in this cell range and colors accordingly.
var rule = SpreadsheetApp.newConditionalFormatRule()
.setRanges([range])
// Min/Max color, without fixed value limits
.setGradientMinpoint('#ffffff') // e.g., white
.setGradientMaxpoint('#ff0000') // e.g., red
.build();
// C) Push new rule into the array of existing rules
rules.push(rule);
});
// 5) Apply rules to the sheet
sheet.setConditionalFormatRules(rules);
Logger.log("2-color heatmap created from column C to R (row 5 to " + lastRow + ").");
}
➤ Click Save Project to Drive icon above to save the complete script.
Now, you have set the script to retrieve and organize backlink data in your datasheet.
Step 5: Specify target domains in the datasheet and run an analysis
➤ Return to your datasheet and paste the domains you want to analyze into the Domain and Target columns;
➤ Click the Fetch Backlink Data button to run a backlink analysis.
Here is the result. As you can see, the backlink data appears in respective columns in the datasheet and is highlighted depending on the data value. With this information, you can analyze the domain rankings of your website, the spam score of backlinks, and their type. In this way, you can easily fine-tune your off-page SEO. Besides, you can compare your domain and backlink stats with the competitors’ backlink data right in the datasheet. This makes competitor websites’ performance analysis even more convenient.
Wrap-up
Using DataForSEO Backlinks API, Google Sheets, and Google Apps Script, you’ve built a fully functional backlink analysis tool. With just one click, you can analyze dozens of domains and retrieve up-to-date backlink data for each domain. This innovative solution, inspired by Antonio Blago, can streamline backlink analysis, eliminating manual work and freeing you from expenses for costly SEO tools. You can read the original guide by Antonio Blago in German on his website.
Sign up now and create your backlink analysis tool effortlessly!