Free Google Sheets Rank Tracker Template

This guide is built around a free Google Sheets rank tracker template you copy and run, no coding involved. The template carries three tools in its menu, a Rank Tracker that checks positions on a schedule, a Rank Checker for one-off lookups, and a SERP scraper for raw results, all fed by a SERP API key. The walkthrough below sets it up, and first, why a spreadsheet makes a decent home for rank data at all.

1. Flexibility and Customization

Custom formulas, trend charts, and conditional formatting live next to the raw positions, so the analysis happens where the data lands instead of in an export.

2. Ease of Sharing and Collaboration

A rankings report is a link. Colleagues comment in place, clients get view access, and nobody mails attachments around.

3. Integration with Google Services

Search Console and Analytics data pulls into the same file through their own connectors, so the tracker’s positions sit next to the clicks they produce.

4. Cost-Effective Solution

The spreadsheet is free and the only cost is the SERP API behind it, billed per check rather than per month per tracked keyword, which is the pricing gap our rank-tracking APIs comparison walks through.

Copy the Template and Get an API Key

The template’s data source is the Google SERP API, which returns parsed positions with the proxy pool and rendering on its side, and covers the result types beyond organic (news, shopping, local) if the tracking ever widens. For one-off checks outside the spreadsheet there’s also the Google Rank Checker browser extension on the same API.

To use this tool, first, sign up with HasData to get an API key and 1000 free credits. Once you’re logged in, you’ll find your API key in the “Dashboard” tab of your account. Copy it as you’ll need it later when using the Google Sheets template.

Next, copy our pre-built spreadsheet template, which has all of our custom tools already built into it – no coding needed.

When opening your copied document, there will be an additional menu with three functions available.

Each of the three gets its own section below.

Rank Tracker

Rank Tracker allows you to monitor how your site’s position in Google changes over time. You can customize the tool by selecting your preferred language, specifying a specific Google domain and country, and selecting the device for checks. You can also choose to manually check for ranking changes or automate the process by setting up a schedule with Google Sheets triggers.

The first time you run Rank Tracker, you must confirm permission to run the script. To do this, you need to confirm authorization.

Select the account from which to run the script. It must be identical to the account you are copying the spreadsheet for. And confirm the permission to change the table and access external sources. This is necessary because the script uses Google SERP API for data scraping. The last permission is necessary for the script to launch the sidebar where the settings will be made.

After that, the side menu will start, where you can set the settings.

Let’s understand what each field and button is for:

Configuration & Setup:

1. API Key: Enter the unique API key you obtained earlier.
2. Target Domain: Define the domain whose positions you aim to track.
3. Device Type: Select the type of device for which the positions should be tracked.
4. Language: Choose the language you want to use for your search queries.
5. Google Domain: Determine the specific Google domain to be used for searches.
6. Country: Select the geographical region for your search.
7. Keywords: Input keywords that will be automatically added to the Tracker page. You can also edit them to the list manually later.
8. Auto-Tracking: Decide if the keywords should be set for automatic rank tracking. Later, we’ll guide you through its setup.
9. Save & Create Sheet: This button stores the provided information on the “Settings” sheet.

Rank Tracking Actions:

1. Check Current Sheet: Launch tracking the keyword rankings for the current sheet using the settings from the “Settings” page.
2. Check All Sheets: Initiate the rank tracking for all sheets listed on the “Settings” page.

When you click the “Save & Create Sheet” button, the script copies everything you entered onto the “Settings” and “Rank Tracker” pages. If a Rank Tracker sheet already exists, the script creates a new one and numbers it consecutively. You will then have the flexibility to change any of the information on these pages as needed.

It’s important to say that the name for the Rank Tracker Google sheet needs to be “Rank Tracker #” plus a sequential number.

Now let’s try gathering positions for the selected keywords and see what the result looks like. Go to the page and get the data from the “Check Current Sheet” button.

You can also add new keywords to be considered during the next check.

The script adds each new check’s data to the far right column. This way, it’s easy to see how ranks change over time.

Setting Up Automatic Keyword Ranking Checks

Now let’s see how to set up automatic checking. Remember, the schedule only checks tables with the specific property.

We use triggers to set scheduled checks. These are tools that allow automated actions based on specific conditions or events. With triggers, you automate the monitoring process, eliminating the need for manual checks and saving precious time.

To configure a trigger:

1. Navigate to the “Extensions” tab and select the “AppScript” option.
2. Find and click the clock icon labelled “Triggers” on the left pane.
3. Click “Add Trigger” to start the setup.
4. For the function to run, choose “autoRankScanning”. This function will check all tables that have the appropriate property set.
5. Under the event source, select “Time-driven”. This is where you set how often the function runs, daily or hourly among the options.
6. Once the settings look right, hit the “Save” button to activate the trigger.

Rank Checker

While the Rank Tracker is designed for continuous monitoring of positions, the Rank Checker serves a different purpose. It’s designed to check the positions of your keywords from time to time. Although it shares many fields with the Rank Tracker, it doesn’t have its own settings page.

The view in which the data is returned is also different. Let’s run Rank Checker to look at the resulting columns.

As you can see, we have got the following data:

1. Target Domain. The domain for which the position was checked.
2. Keyword. The search query is used to check the position.
3. Link. URL of the page that appeared in the search results.
4. Position. Indicates the ranking of the page on the SERP.
5. Date. The day, month, and year of the check. The exact time is stored too.
6. Google Snapshot URL. The link to the SERP.

The keyword Rank Checker is especially useful for one-time evaluation of results. In addition to the standard data, it offers the Google Snapshot URL. This provides a direct link to the exact state of the SERP at the time of the check, allowing you to examine the specific search results from that moment closely. This level of detail can be critical in understanding shifts and trends in search results.

Google SERP Scraper

And the final tool is the Google SERP Scraper. This versatile tool enables you to extract data from various sections of the SERPs, including the primary search results, images, videos, shopping, news, and local.

The Google SERP Scraper and the Rank Tracker/Checker have slightly different input fields, so here is them. Remember that this tool doesn’t use a settings sheet, so fields like API key and Keyword are required.

While many of the settings remain consistent with those found in Rank Tracker and Rank Checker, let’s focus on the specifics of three distinct fields:

1. Start Position: Determine the position in the search results from which data extraction should commence. This is particularly valuable when you require information from a specific point in the results or beyond the initial page.
2. Number of Results: Specify the number of search results you want to extract and place in your sheet. This number can range from 10 to 100, catering to your specific needs.
3. SERP Type: Choose the specific content type you want to extract, such as basic search results, images, local listings, news, shopping, or videos. The column names and table views will change depending on the selected type.

The Google SERP Scraper pulls whole results pages into the sheet. It reads primary search, images, videos, shopping, news, and local listings alike, which covers the surfaces a keyword can appear on beyond the ten blue links.

Tracking AI Overview Visibility

An AI Overview above the results changes what position 1 is worth, so a position on its own describes less than it used to. The same SERP API responses the template consumes carry an aiOverview field whenever Google shows an Overview for the query, so the signal is already in the data. A keyword can be marked AIO-present or AIO-free on every check. For the step further, whether your domain is among the Overview’s cited sources, the AI Overview endpoint takes the token from that field and returns the block with its references. That token is valid for four minutes, so the second call has to follow the first inside the same run. A tracker that stores the token overnight and fetches the Overview on the next schedule gets nothing back.

Adding a column for whether an Overview appeared, and a second for whether your domain was cited in it, turns that into something you can read per keyword next to the position. A keyword that holds rank 3 and gains an Overview it isn’t cited in has lost visibility without moving in the rankings at all.

Potential Use Cases for SEO Specialists

Using these tools can greatly help an SEO specialist’s work. They offer a range of functions that can help you with different tasks.

Analyzing Competitors

You can use monitoring tools not just to track your site’s positions but also to watch how competitors’ keywords are ranking. This approach helps spot opportunities to get ahead or improve your site’s position.

Keyword Data Research

Analyzing how positions change over time helps you understand how well keywords work in results. This helps determine if your site is moving up or down in rank and adjust your strategy accordingly. Keyword research can also help discover new keyword possibilities.

On-page SEO Optimization

You can use the collected data to optimize meta tags and audit several sites on a schedule against the same optimization checklist. By using these tools, you can gather important information about how your website is performing. This information will help you understand which areas of the website need improvement.

Local SEO

This tool can be used to evaluate the position of your website in local search results. With the ability to customize localization, you can get a more accurate score for specific regions. This can be useful, especially for businesses targeting a specific geographic region.

Instant Position Checks

Sometimes you need to quickly see how your site ranks for specific keywords. This quick info is critical for adjusting your optimization efforts in real time.

Advantages Over Traditional Rank Trackers

The sections above made the case for Sheets. Here it is side by side with dedicated rank tracking services.

Google Sheets costs less, bends further, and takes less setup than the alternatives above.

Conclusion

This article explored the power of using Google Sheets to rank tracking. Using this tool and extending it with API integration gives you a cost-effective, customizable, and efficient way to monitor how well your site ranks.

With Google Sheets, you adapt the tracking to the questions you actually have. And the tool itself is the spreadsheet your team already knows.

Whether you’re a seasoned SEO expert or a newcomer with a small site, this free tool offers a simplified rank-tracking approach that can cater to your needs.