This tutorial shows how to automate SEO keyword rank fetch in Google Sheets with a small, auditable Apps Script instead of a fragile chain of copied formulas.
The workflow is designed for search operations teams that already keep keyword data in a Sheet and need a reliable rank position column for reporting or follow-up work.
The script reads the Keyword Rankings tab, maps headers by name, calculates each row, and writes the output in a single batch so the sheet remains responsive.
Everything is static in this page: one tutorial object, one Apps Script example, and one deployment checklist that can be copied into a bound Apps Script project.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Column | Purpose | Example |
|---|---|---|
| Keyword | Primary row identity used by the automation | Sample keyword |
| Source fields | Inputs required to calculate rank position | Dates, amounts, statuses, or lookup keys |
| current rank | Script-owned output for SEO keyword rank fetch | Rank Position |
| Status | Optional review state for exceptions | Ready, Needs review, Posted |
Model the SEO keyword rank fetch fields
Start with one row per keyword in the Keyword Rankings sheet. The script expects stable headers for the source fields and writes the calculated rank position back to a dedicated output column so formulas, pivots, and reviewers can separate source data from automation output.
- Keep keyword immutable after import.
- Store rank position as the system-owned column.
- Use ISO dates or real Sheet date values for trigger-safe comparisons.
Validate inputs before the trigger runs
For search operations teams, bad source data usually costs more time than the Apps Script calculation. Add required-field validation on the columns referenced by this tutorial and filter blank rows before a scheduled run is enabled.
- Reject empty identifiers.
- Normalize currency and percentage columns as numbers.
- Add a Status column for rows that need manual review.
Calculate rank position deterministically
The example code keeps the SEO keyword rank fetch rule inside the script instead of spreading it across hidden Sheet formulas. That makes each run reproducible, easier to review in version history, and safer when rows are inserted by imports or form submissions.
Write results in one batch
The script reads the full Keyword Rankings range once, computes every row in memory, and writes one result matrix. This avoids slow row-by-row calls and keeps the tutorial suitable for hundreds or thousands of search operations records.
Route exceptions to humans
Not every SEO keyword rank fetch case should be auto-approved. Use explicit statuses such as Needs review, Pending documentation, Missing, or Alert legal owner so downstream users know whether the row is final or requires intervention.
Keep an audit trail
For production use, add Last Calculated At and Calculated By columns beside rank position. Those fields make it obvious when the Apps Script last touched a row and help reconcile Sheet output against source systems.
Choose the right trigger
Use an hourly trigger when SEO keyword rank fetch depends on imported data, and an on-edit trigger only when users type rows directly. Time-driven triggers are easier to monitor because every run processes a complete snapshot of the Keyword Rankings sheet.
Apps Script: SEO keyword rank fetch for the Keyword Rankings sheet
This example calculates rank position from named Sheet headers and writes the result back to the current rank column. Rename the headers to match your workbook before deploying the trigger.
function updateSeoKeywordRankFetch() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Keyword Rankings');
var values = sheet.getDataRange().getValues();
var headers = values.shift();
var keywordCol = headers.indexOf('Keyword');
var targetUrlCol = headers.indexOf('Target URL');
var outputCol = headers.indexOf('current rank') + 1;
var results = values.map(function(row, i) {
var score = fetchRank(row[keywordCol], row[targetUrlCol]);
return [score];
});
if (results.length) {
sheet.getRange(2, outputCol, results.length, 1).setValues(results);
}
}
function fetchRank(keyword, targetUrl) {
var endpoint = 'https://www.googleapis.com/customsearch/v1?q=' + encodeURIComponent(keyword);
var payload = UrlFetchApp.fetch(endpoint, { muteHttpExceptions: true }).getContentText();
var items = JSON.parse(payload).items || [];
for (var p = 0; p < items.length; p++) {
if ((items[p].link || '').indexOf(targetUrl) >= 0) return p + 1;
}
return 'Not in first page';
}- Line 2: Targets the Keyword Rankings tab so test data and production data stay separated.
- Line 4: Reads the header row once and resolves columns by name instead of hard-coded letters.
- Line 8: Applies the domain rule for SEO keyword rank fetch; this is the part you customize for policy changes.
- Line 12: Writes all calculated values with one setValues call for speed and quota safety.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Production checklist for SEO keyword rank fetch
- 1Confirm every required Keyword Rankings header exists before enabling a trigger.
- 2Test the rank position output with normal, blank, and exception rows.
- 3Protect source columns that should not be overwritten by editors.
- 4Run once manually and compare a sample of rows against a hand calculation.
- 5Add a time-driven trigger only after the first authorized run succeeds.
Frequently asked questions
Yes. Use a time-driven trigger after the import finishes. The script reads the current Keyword Rankings snapshot and rewrites only the current rank column.
Header lookup survives inserted columns and makes the script easier to review. If a required header is renamed, the failed lookup is easier to diagnose than a silent wrong-column write.
Return an explicit text status for rows that need a human decision, then filter or conditional-format those statuses in the Sheet.
The example uses one read and one write for the main range, which is the quota-friendly pattern. Very large sheets should archive closed rows or process only recently changed records.
Yes. Add alert logic after the results array is calculated, but send one summary message per run rather than one message per row.
Log run time, row count, exception count, and the active user or trigger account. For sensitive search operations data, avoid logging raw personal or financial values.