How to Automatically Track Rankings of Keywords in Google Sheets Every Day

Are you looking for an easy way to track your website’s keyword rankings automatically, without spending hours manually updating them every day? In this tutorial, I’ll show you how to set up a system in Google Sheets that pulls keyword rankings and updates them automatically every day. With the power of Google Sheets and Google Apps Script, you’ll never have to worry about manually tracking rankings again!

Step 1: Set Up Your Google Custom Search API

Before we dive into the Google Sheets automation, you need to have access to Google’s Custom Search API. Follow these steps to set up your API key and Custom Search Engine ID (CSE ID):

1. Get Your API Key:
   • Go to the Google Cloud Console.
   • Create a new project (or use an existing one).
   • Navigate to APIs & Services > Credentials.
   • Under API Keys, click Create Credentials and copy the API Key generated. This is your API_KEY.
2. Set Up Custom Search Engine (CSE):
   • Go to Google Custom Search.
   • Create a new search engine, and make sure to configure it for the region you want (e.g., India for Indian search results).
   • After creating the CSE, grab the CSE ID (this is unique to your custom search engine).
3. Store Your API Key and CSE ID:
   • Open your Google Spreadsheet.
   • In the script editor, store these values using the following functions (which we’ll call in the code later).

Step 2: Set Up Your Google Spreadsheet

Create a New Spreadsheet:

• Open Google Sheets and create a new spreadsheet where you’ll track your keyword rankings.
• The first row should have the list of dates for which rankings will be recorded, starting from B1. The keywords should be listed in column A, starting from A2.

Add the Script:

• Open the script editor by navigating to Extensions > Apps Script.
• Copy and paste the following code into the script editor. This code fetches the keyword rankings from Google Custom Search and updates the rankings in the spreadsheet.

function getKeywordRanking() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Keyword Rankings');
  var today = new Date();
  var dateString = Utilities.formatDate(today, Session.getScriptTimeZone(), "MM/dd/yyyy"); // format today's date
  
  // Get the header row (1) where dates are listed
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var headers = headerRange.getValues()[0]; // Get the row of headers as an array
  
  // Check if today's date is already present in the header row
  var columnIndex = -1;
  for (var i = 0; i < headers.length; i++) {
    // Compare the actual date (not string) with today's date
    if (Utilities.formatDate(new Date(headers[i]), Session.getScriptTimeZone(), "MM/dd/yyyy") === dateString) {
      columnIndex = i;
      break;
    }
  }
  
  // If today's date is not found, add it to the next available column
  if (columnIndex === -1) {
    columnIndex = headers.length;
    sheet.getRange(1, columnIndex + 1).setValue(dateString); // Set today's date as the header in the new column
  }
  
  // Loop through the keywords and fetch rankings
  var keywords = sheet.getRange('A2:A21').getValues();
  
  for (var i = 0; i < keywords.length; i++) {
    var keyword = keywords[i][0];
    var rank = fetchRanking(keyword); // Fetch the rank using the API
    sheet.getRange(i + 2, columnIndex + 1).setValue(rank);  // Update rank in the correct column for today
  }
}

function fetchRanking(keyword) {
  var apiKey = getApiKey();
  var cseId = getCseId();
  var url = 'https://www.googleapis.com/customsearch/v1?q=' + encodeURIComponent(keyword) + '&key=' + apiKey + '&cx=' + cseId + '&gl=in';
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());

  if (json.items) {
    for (var i = 0; i < json.items.length; i++) {
      var resultUrl = json.items[i].link;
      if (resultUrl.includes('funwithai.in')) {  // Replace 'funwithai.in' with your domain
        return i + 1;  // Return the position of your website in the search results
      }
    }
    return 'Not Found';  // If your website is not in the top results
  } else {
    return 'No Results';
  }
}

function getApiKey() {
  return PropertiesService.getScriptProperties().getProperty('API_KEY');
}

function getCseId() {
  return PropertiesService.getScriptProperties().getProperty('CSE_ID');
}

// Set a time-driven trigger to run the script every day at 8 AM
function setDailyTrigger() {
  ScriptApp.newTrigger("getKeywordRanking")
    .timeBased()
    .everyDays(1)
    .atHour(8)  // Change this hour to your desired time (24-hour format)
    .create();
}

Step 3: Set Up API Key and CSE ID in Script Properties

Before running the script, store your API key and CSE ID in the script’s properties:

1. Open Apps Script Editor.
2. Go to File > Project Settings and under Script Properties, add two properties:
   • API_KEY – your Google API Key
   • CSE_ID – your Custom Search Engine ID

Step 4: Automate Keyword Ranking Updates

To make sure your rankings are updated every day:

1. Set the Trigger:
   • In the Apps Script editor, go to Triggers > Add Trigger.
   • Set the function to run getKeywordRanking daily at the time you prefer (e.g., 8 AM).

Now, every day, the rankings will be updated in your spreadsheet automatically.

Step 5: Monitor Your Rankings

Your spreadsheet will now contain a daily update of keyword rankings. It’s a great way to monitor SEO progress over time and see how your site is performing in search results.

Important Note:

• Don’t Share Your API Key or CSE ID: For security reasons, make sure to keep your API key and CSE ID private. Use dummy data for testing before filling in real data.
• Use Your Own Domain: The script checks if your website appears in the search results using funwithai.in. Make sure to replace it with your own domain to track your own rankings accurately.

This simple setup in Google Sheets allows you to track your keyword rankings every day with minimal effort. You can adjust the script to include additional features, like email notifications or additional data from the API. Don’t forget to share this with your friends and family so they can track their keyword rankings as well!

If you found this helpful, don’t forget to share it and subscribe to my blog for more tech tutorials!