I do SEO and link-building work at seobysubham.com, and one task kept feeling unnecessarily repetitive: checking whether the pages containing backlinks were actually indexed by Google.
The process is simple in theory. Take a list of backlink URLs, search for each one, and determine whether Google has the page in its index. Doing that manually for a large list is slow, especially when the URLs are already sitting in a Google Sheet. That was the main reason I decided to build the checker directly inside Sheets rather than as a separate app.
I didn't want another small web application with its own login, database, hosting, and UI. The people using the data already had a spreadsheet. Putting the results there meant there was no extra interface to learn, no separate auth flow, and the rest of the team could see results in the same document they already worked from.
Why Google Apps Script?
Apps Script was a natural fit because it sits directly inside Google Workspace. A spreadsheet can have an Apps Script project attached to it, and that script can read and write cells using the Sheets API available through the Apps Script runtime. It also lets me add a custom menu to the spreadsheet, so the checker starts from the UI instead of running as a completely separate application.
The architecture is deliberately small:
Google Sheet
|
| Backlink URLs
v
Apps Script
|
| HTTP request
v
SerpApi
|
| Search response
v
Apps Script
|
| Parse result
v
Google Sheet
|
+-- Indexed
+-- Not Indexed
+-- Error
No dedicated server involved. The spreadsheet is effectively the interface, and Apps Script handles the application logic. Backlink URLs live in one column, indexation results get written into another.
The core logic
The production version has more validation and error handling, but the basic implementation is fairly straightforward. For every backlink URL, the script sends a request to SerpApi. The response is JSON, so the script can inspect the returned search results and decide whether the target URL appears in Google's results.
A simplified version looks something like this:
function checkBacklinkIndexation() {
const sheet = SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName("Backlinks");
const apiKey = PropertiesService
.getScriptProperties()
.getProperty("SERPAPI_KEY");
const rows = sheet.getDataRange().getValues();
// Assume column A contains backlink URLs
for (let i = 1; i < rows.length; i++) {
const backlinkUrl = rows[i][0];
if (!backlinkUrl) {
continue;
}
const query = `"${backlinkUrl}"`;
const endpoint =
"https://serpapi.com/search.json" +
"?engine=google" +
"&q=" + encodeURIComponent(query) +
"&api_key=" + encodeURIComponent(apiKey);
try {
const response = UrlFetchApp.fetch(endpoint, {
muteHttpExceptions: true
});
const data = JSON.parse(response.getContentText());
const indexed = data.organic_results?.some(result =>
result.link === backlinkUrl
);
sheet.getRange(i + 1, 2).setValue(
indexed ? "Indexed" : "Not Indexed"
);
} catch (error) {
sheet.getRange(i + 1, 2).setValue("Error");
}
}
}
This is intentionally simplified. In a real implementation, I wouldn't rely on an exact string comparison alone, since URLs can differ by trailing slashes, query parameters, redirects, encoding, or canonical versions. The important part is the data flow rather than this exact comparison: read the rows, skip empty URLs, build a search request, send it to SerpApi, parse the JSON response, determine whether the target appears in the returned results, and write the status back into the spreadsheet.
I also keep the API key in Apps Script's script properties rather than hardcoding it into the source. That makes configuration easier to manage and avoids accidentally leaving the key inside a function that might get copied around.
Why I intentionally made it manual-only
This was probably the most deliberate engineering decision in the project.
Apps Script supports time-driven triggers, so technically I could make the checker run automatically every hour, every day, or on some other interval. I decided not to. The reason is API cost.
Every backlink check results in an external API query, and SerpApi charges per search. An automatic trigger can quietly consume the available quota. Imagine a spreadsheet with 500 URLs — if an automated process checks all of them daily, that's potentially 15,000 searches a month, and the user might not notice until the quota is already gone.
For this particular tool, automation isn't necessarily an improvement. So instead, I added a custom menu and the user explicitly starts the process:
Open spreadsheet
|
v
Review backlink list
|
v
Run "Check Indexation"
|
v
API requests are made
|
v
Results appear in the sheet
This makes API usage visible, and it gives me better control when testing. I can add a few URLs, run the checker, inspect the output, and stop there. The trade-off is obvious: manual execution is less convenient than a scheduled job. But here I preferred predictable API usage over invisible automation.
The technical problem I ran into
One issue appeared once I started testing with larger backlink lists. The first implementation treated the spreadsheet almost like a normal JavaScript array and made an individual request for every row, followed immediately by an individual spreadsheet write. That works fine for a small test. It becomes inefficient with hundreds of URLs, because there are two different external operations happening repeatedly — a sheet read, an API request, a sheet write, over and over.
Apps Script has execution time limits, and every network request adds latency. Writing to Sheets cell-by-cell also creates unnecessary overhead.
The fix was to read the spreadsheet data once and collect results in memory instead of writing on every iteration:
const results = [];
for (const url of urls) {
const status = checkUrl(url);
results.push([status]);
}
Then write the complete result range in a single operation:
sheet
.getRange(2, 2, results.length, 1)
.setValues(results);
Small change, but it matters once the spreadsheet gets larger. I also learned that API response handling needs to be defensive — a successful HTTP request doesn't automatically mean the expected JSON structure exists. An API can return an error, a quota message, or a response missing the expected fields. So the production version checks the HTTP response and expected fields before deciding a URL is "Not Indexed." An API failure and a confirmed non-indexed URL are two completely different states, and conflating them silently would've made the data untrustworthy.
What I'd improve next
A few things I'd change if I keep developing this.
Batching. Large lists should be processed in controlled chunks rather than treating the entire spreadsheet as one job.
Caching. If I checked a URL yesterday and it hasn't changed, there's little reason to spend another API query re-checking it. Storing the last-checked date and result would let me re-check only URLs that actually need another lookup.
Better error reporting. Instead of just writing "Error," the sheet could store the actual HTTP status, a quota message, or a note on the malformed response — useful context instead of a dead end.
A progress indicator. Since API requests take time, something like "42 / 200 checked" would make a manual run easier to follow.
The basic version is already useful because it removes a repetitive lookup process from the browser and puts the result directly next to the backlink data it's describing. The bigger lesson for me was that a small internal tool doesn't always need a complicated architecture — Google Sheets, Apps Script, and one external API were enough. The more interesting engineering decisions were around API cost, execution limits, error states, and knowing when automation actually helps versus when it just hides a cost you'll notice later.
Making the tool manual-only wasn't a limitation I forgot to solve. It was the feature that kept the system predictable.
Top comments (0)