Worked example
Join GSC pages to referring domains in Google Sheets
One tab holds your Performance export, a second holds link data, and a lookup column ties them together. This walkthrough uses an example site, clearly invented, so the numbers are safe to copy.
The setup, with made-up numbers
Picture a small store selling plumbing parts. It is an example, not a real client, with 60 indexable pages, and the Pages tab of the Performance report lists all of them with clicks, impressions and average position. Nothing more is needed.
The goal is simple. You want to check domain links for each of those 60 URLs and find the ones that get impressions but have no referring domains at all.
Keep two tabs. Tab one, gsc, is the raw export, and tab two, links, is whatever the API returns. Never edit either by hand, because next month you will overwrite both and rerun the same formulas on fresh data.
Prefer the logic first? The report-vs-index page explains why the two sources disagree, since Google's data is a capped sample and ours is a monthly snapshot, each incomplete in a different way. Read it, then come back.
Five moves, in this order
Store the key safely
In Apps Script open Project Settings and add a script property named API_KEY. Sheets cells get shared, copied and screenshotted; script properties do not.
Run the pull
Paste the script below and run it once. toppages costs 2 credits, so $0.0002, and returns up to 1,000 rows in one call. Check the logged _credits value to confirm the bill.
Read the real columns
Open the links tab and look at the header row the API produced. Use those names in your formulas, because this page does not promise any particular field order.
Add a clean key column
In both tabs build a helper column: lower-case the URL, strip the fragment, remove or add the trailing slash, and drop www. if you redirect it. Use the same recipe on each side.
Look it up
In gsc, add =IFERROR(XLOOKUP(A2,links!E:E,links!F:F),0) where column E is the clean key and F is the link count. Pages that come back as 0 are your gaps.
The pull, as Apps Script
It writes whatever columns the API returns, so it keeps working if you later switch to refdomains or anchors.
function pullTopPages() {
const key = PropertiesService.getScriptProperties().getProperty('API_KEY');
const url = 'https://seotoolsforgoogle.com/api.php?action=toppages&q=example.com&per=1000';
const res = UrlFetchApp.fetch(url, {headers: {Authorization: 'Bearer ' + key}});
const data = JSON.parse(res.getContentText());
if (!data.ok) throw new Error(res.getContentText());
// first top-level array in the response holds the rows
const rows = Object.values(data).find(Array.isArray);
const cols = Object.keys(rows[0]);
const out = [cols].concat(rows.map(r => cols.map(c => r[c])));
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('links') || ss.insertSheet('links');
sh.clear();
sh.getRange(1, 1, out.length, cols.length).setValues(out);
Logger.log(JSON.stringify(data._credits));
}What the joined sheet might look like (example values)
| Page | Impressions | Avg position | Referring domains | Read |
|---|---|---|---|---|
| /copper-fittings/ | 9,400 | 11.2 | 0 | Gap: ranks, has no links |
| /pex-crimp-rings/ | 6,100 | 8.7 | 3 | Thin, watch it |
| /shutoff-valves/ | 3,800 | 19.4 | 14 | Links fine; check content |
| /about/ | 210 | 31.0 | 22 | Ignore, brand page |
A zero means our index did not list a linking domain for that URL in its last monthly pass. The link may exist and be newer than the snapshot, or sit on a redirected address. Check before you act, and see the triage thresholds.
Three traps that break the lookup
Trailing slashes come first. /pex-crimp-rings and /pex-crimp-rings/ look identical to a person and different to XLOOKUP. Pick one rule and apply it to both tabs before you run any formula, then write that rule in a cell for next month. Slashes bite.
Second, protocol and host. Search Console data may carry the full address with the www host, while the API may return the address the linking site used, which could be older. Strip both down to a path first. Forgetting this is common.
Third, row limits. The Search Console interface exports 1,000 rows, and a site with more pages than that needs the API at up to 25,000 rows per request. Use Looker Studio or the BigQuery bulk export if you need the full history.
Finally, remember the monthly refresh. A page launched two weeks ago will show zero in our index even if five sites already link to it. Add a column for publish date and filter out anything younger than 45 days.