A custom menu, one script and a column of domains. Click once and the ratings fill in beside each row. Free actions need no key at all.
Deeplinkify research team ·
Why not just IMPORTDATA or IMPORTXML?
Because our answers are JSON. IMPORTDATA reads CSV and IMPORTXML reads markup, so neither can parse the response.
People then reach for add-ons, which is fine, but a tiny script is easier to audit and costs nothing. Apps Script runs inside your Google account, can read your sheet, call a URL with UrlFetchApp and write the answer back into cells.
A menu function beats a custom cell formula for one reason: it runs once on demand. A formula that recalculates whenever a sheet reloads can fire hundreds of requests you never asked for, and with paid actions that means real spend.
Google Workspace accounts get higher Apps Script quotas than free consumer accounts, but the principle holds either way: fewer, bigger calls are better than many small ones.
The script
Open Extensions, Apps Script, paste this and save. Put your key in Project Settings, Script properties, as DEEPLINKIFY_KEY. Free actions work even if the property is empty.
javascript
function onOpen() {
SpreadsheetApp.getUi().createMenu('Backlinks')
.addItem('Fill overview for column A', 'fillOverview').addToUi();
}
function fillOverview() {
const sh = SpreadsheetApp.getActiveSheet();
const key = PropertiesService.getScriptProperties().getProperty('DEEPLINKIFY_KEY') || '';
const rows = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues();
rows.forEach((r, i) => {
const domain = String(r[0]).trim();
if (!domain) return;
const url = 'https://deeplinkify.com/api.php?action=overview&q=' + encodeURIComponent(domain);
const res = UrlFetchApp.fetch(url, {
headers: { Authorization: 'Bearer ' + key },
muteHttpExceptions: true
});
const code = res.getResponseCode();
if (code !== 200) { sh.getRange(i + 2, 2).setValue('HTTP ' + code); return; }
const j = JSON.parse(res.getContentText());
// Check one real response and point this at the rating field.
sh.getRange(i + 2, 2).setValue(JSON.stringify(j));
Utilities.sleep(500);
});
}
Using it
1
Lay out the sheet
Header in row 1, bare domains from A2 down. No protocol, no trailing slash.
2
Run it once and authorise
Reload the sheet, open the Backlinks menu and run the item. Google asks for permission to call external URLs; that is the UrlFetch scope.
3
Read one result
The script first dumps the whole JSON into column B. Look at one row, find the field you care about and change the setValue line to read just that.
4
Add a paid column later
Copy the function, change action=overview to refdomains, and add a confirm dialog first. It is 2 credits per row, and a sheet of 5,000 rows is 10,000 credits, $1.
A few Sheets habits that save credits
Keep results in values, not formulas. Pasted values do not recalculate.
Add a timestamp column and have the script skip any row checked in the last 30 days, because our index refreshes roughly monthly and a second look inside that window mostly returns the same answer. For ratings only, collect the whole column and send it to bulk_dr once, as the outreach list recipe does.
One more thing. If several people use the sheet, store the key in Script properties rather than in a cell. A key in a cell travels with every copy of the file, and a copy shared outside the company takes your balance with it. The error runbook lists the symptoms when that goes wrong.
Finally, colour your output column with conditional formatting. Errors should stand out. A sheet that quietly mixes real numbers with 'HTTP 402' text is the classic way to get a wrong chart, so sort the errors to the top, fix the cause, and rerun only those rows.
Limits you will meet on the Google side
Apps Script quotas change; check Google's current quota page before you size a job.
Limit
What it means for this script
Workaround
Script run time (around 6 minutes on consumer accounts)
A long list with a pause per row can time out
Process in chunks and store progress in a cell
Daily URL Fetch calls
A very large sheet may hit the day's cap
Use bulk_dr instead: 2,000 domains per fetch
Custom function limits
Slow, and re-runs on reload
Prefer menu functions
Authorisation scope
Needs consent once per user
Share a copy, not the original
Sheet too heavy?
When a Sheet holds tens of thousands of rows and several lookups, it becomes slow to open. Move the lookups to n8n and keep Sheets as the place you read results. The n8n recipe does exactly that, and the outreach list recipe shows the one-call bulk version.