Google Apps Script Weekly SERP Ranking Tracker
A Google Workspace-first ranking tracker built directly inside Google Sheets with Apps Script. The workflow reads company search terms from the sheet, calls a SERP API with UrlFetchApp, compares this week's results with the prior week's baseline, writes clickable results back into the same workbook, color-codes movement, and maintains the trend tab on a weekly schedule.
The Problem
The reporting process already lived in Google Sheets, but ranking checks still depended on manual searching, copy-pasting results, and error-prone week-over-week comparisons. That slowed the team down and kept the logic disconnected from the spreadsheet they actually worked in.
The Solution
I built a native Apps Script automation that runs inside the workbook itself. It detects or creates the current week's block, fetches fresh rankings from a SERP API, compares them against the previous week's stored baseline, writes rich-text results and comments into the January tab, and updates the Trend tab with movement colors on a recurring trigger.
Tech Stack
Key Features
- Reads company search terms directly from the Google Sheet
- Calls a SERP API with UrlFetchApp and normalizes organic results
- Compares the latest rankings against the previous week's baseline
- Writes clickable results, comments, and JSON notes back into the workbook
- Color-codes movement across the January and Trend tabs automatically
- Runs in safe mode with optional overwrite and a Monday weekly trigger
Impact & Results
Replaced manual weekly rank checks with a spreadsheet-native workflow. The reporting process now stays inside Google Workspace, so fresh rankings, movement context, and trend visibility update in the same sheet the team already uses.
Who Benefits
Interested in this solution?
Let's discuss how this can be customized for your business needs.