Monitoring backlinks with Google Sheets (my guide)
Senior SEO-specialist
Backlinks are one of the most important factors in SEO. But they also lose or change without warning. Monitoring your backlinks structurally prevents surprises and provides insight into growth and loss. In this article, I explain how to set up a simple, scalable monitoring system yourself in Google Sheets – without expensive tools.
1. Why monitor backlinks yourself?
Tools like Ahrefs and SEMrush are powerful, but expensive. In addition, sometimes you want to control the process yourself, for example, for:
- Specific campaigns or link building actions
- Customer Reports
- Periodic checks on link status
- Proprietary dashboarding in Looker Studio
A custom Google Sheet offers flexibility and can be combined with other data sources at no cost.
2. What exactly do you want to monitor?
First, determine which fields you need:
| Column | Function |
| URL (backlink) | The page where the link is |
| Link to | Your target page |
| Link type | Follow / nofollow / sponsored |
| Anchor text | What text is linked |
| Status | 200 / 404 / 301 / no more link |
| Last checked | Date of last check |
3. Setting up the basics in Google Sheets
Create a sheet with the columns above. Fill in your existing backlinks manually or via an export (e.g. from Ahrefs, GSC, Majestic or manually).
Then use a script to periodically automatically check if the backlinks are still working.
Aan de slag met SEO? Neem gerust contact op.
4. Automatically check backlinks via Apps Script
Through a Google Apps Script, you can automatically check the link status.
Script example:
javascript
function checkBacklinks() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Backlinks');
const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues(); // kolom A = backlink URL, kolom B = doel-URL
data.forEach((row, i) => {
const sourceUrl = row[0];
const targetUrl = row[1];
try {
const html = UrlFetchApp.fetch(sourceUrl, {muteHttpExceptions: true}).getContentText();
const linkPresent = html.includes(targetUrl);
const status = linkPresent ? "✔ Link actief" : "✖ Link ontbreekt";
sheet.getRange(i + 2, 6).setValue(status); // kolom F
sheet.getRange(i + 2, 7).setValue(new Date()); // kolom G = laatst gecontroleerd
} catch (e) {
sheet.getRange(i + 2, 6).setValue("Fout of 404");
sheet.getRange(i + 2, 7).setValue(new Date());
}
});
}
Setting:
- Open your Google Sheet
- Go to Extensions > Apps Script
- Paste the script, save
- Set a trigger (e.g. daily, via Triggers > Time-driven)
This automatically checks to see if your backlinks are still live, without the intervention of tools.
5. Expanding with GA4 or Looker Studio.
You can link the Sheet to other data sources:
- Traffic from backlink pages (GA4 via UTM or referer)
- Authority or DR score via API (e.g., Moz or Ahrefs)
- Integration into Looker Studio for visual dashboard
Combine this with your SEO dashboard for complete overview.
6. Best practices
- Work with filters: by domain, status or action needed
- Color code statuses (e.g., green = active, red = broken)
- Plot actions on lost links: try contact or replace
- Keep historical status in separate tab (optional with Apps Script log)
In conclusion
With a simple Google Sheet and a script, you can perfectly monitor manually obtained or valuable backlinks. No expensive software needed – just insight, structure and a little automation.
