Free Tool · Google Apps Script

DataForSEO SERP Tracker
for Google Sheets

A Google Apps Script that pulls desktop and mobile US rankings, AI Overview presence and cited sources, and People Also Ask questions for a full keyword list — straight into a spreadsheet.

Runs on the DataForSEO API. Requires a DataForSEO account with API credentials — free to try the script, but DataForSEO usage is billed by them, not by me.

Download the script How to install →

Overview

What it does

Desktop & mobile rank tracking

Pulls the exact organic rank_group for your domain on both devices for every keyword in your sheet, or ">100" if it's not in the top results.

AI Overview detection

Flags whether an AI Overview exists for each keyword, extracts its full text, and lists every URL cited inside it.

Built-in Sheets menu

Adds a "DataForSEO" menu to the spreadsheet with Run Keywords, Test API Authentication, and Clear Results.

Per-row live status

Column H updates in real time as each keyword is checked — desktop, mobile, AI Overview, PAA — so you can watch progress.

Error handling that doesn't stall the run

A failed keyword gets logged with an error note in the row and the script moves on instead of stopping the whole batch.

Setup

Sheet structure

B1 holds your domain. Keywords start at row 3, column A — the script fills in columns B through H as it runs.

Cell	Column	Contains
A	Keyword	One keyword per row, starting row 3
B	Desktop position	Organic rank_group on desktop
C	Mobile position	Organic rank_group on mobile
D	AI Overview present	true / false
E	AI Overview text	Full extracted AI Overview text
F	URLs cited in AI Overview	One URL per line
G	People Also Ask	One question per line
H	Progress / Status	Live status while the script runs

Setup

How to install

Set up your sheet

Row 1: A1 = "Domain", B1 = your domain (e.g. seoriddler.com). Row 2 is used for status messages. Keywords start at row 3, column A.

Open Apps Script

In your Google Sheet, go to Extensions → Apps Script.

Paste the script

Delete the default code and paste the full script (copy it below, or download the file). Save the project.

Add your DataForSEO credential

Replace PASTE_YOUR_BASE64_CREDENTIAL_HERE with the Base64-encoded login:password from your DataForSEO account. Do not add "Basic " — the script adds that automatically.

Reload the sheet and run

Reload the spreadsheet tab. A new "DataForSEO" menu appears. Use Test API Authentication first, then Run Keywords.

Requires a DataForSEO account

This script calls DataForSEO's serp/google/organic/live/advanced endpoint, which is billed per request by DataForSEO — not free to run at scale. Sign up at dataforseo.com to get your API login and password, then Base64-encode login:password and paste it into DATAFORSEO_AUTH in the script.

Location defaults to the United States (location_code: 2840) and depth to 100 results — change LOCATION_CODE and SERP_DEPTH near the top of the script to adjust.

Source

The full script

Copy it directly into Apps Script, or download the file above. The credential field is blank — paste in your own Base64-encoded DataForSEO login:password.

dataforseo-serp-tracker.gs

/************************************************************
 * DATAFORSEO GOOGLE SHEETS SEO / SERP ANALYZER
 *
 * SHEET STRUCTURE
 *
 * A1 = Domain
 * B1 = seoriddler.com
 *
 * A2 = Keyword
 * B2 = Desktop position
 * C2 = Mobile position
 * D2 = AI Overview present
 * E2 = AI Overview text
 * F2 = URLs cited in AI Overview
 * G2 = People Also Ask
 * H2 = Progress / Status
 *
 * Keywords start at ROW 3.
 ************************************************************/


// ==========================================================
// 1. DATAFORSEO AUTHENTICATION
// ==========================================================
//
// Paste the Base64-encoded login:password value
// from your DataForSEO API credentials email.
//
// DO NOT add "Basic ".
// The script adds it automatically.
//

const DATAFORSEO_AUTH =
  "PASTE_YOUR_BASE64_CREDENTIAL_HERE";


// ==========================================================
// 2. DATAFORSEO SETTINGS
// ==========================================================

const DATAFORSEO_ENDPOINT =
  "https://api.dataforseo.com/v3/serp/google/organic/live/advanced";

const LOCATION_CODE = 2840; // United States

const LANGUAGE_CODE = "en";

const SERP_DEPTH = 100;


// ==========================================================
// 3. SHEET SETTINGS
// ==========================================================

const DOMAIN_CELL = "B1";

const KEYWORD_COLUMN = 1; // A

const DESKTOP_COLUMN = 2; // B

const MOBILE_COLUMN = 3; // C

const AIO_PRESENT_COLUMN = 4; // D

const AIO_TEXT_COLUMN = 5; // E

const AIO_URLS_COLUMN = 6; // F

const PAA_COLUMN = 7; // G

const STATUS_COLUMN = 8; // H

const DATA_START_ROW = 3;


// ==========================================================
// 4. GOOGLE SHEETS MENU
// ==========================================================

function onOpen() {

  SpreadsheetApp
    .getUi()
    .createMenu("DataForSEO")
    .addItem(
      "Run Keywords",
      "runDataForSEO"
    )
    .addItem(
      "Test API Authentication",
      "testDataForSEO"
    )
    .addItem(
      "Clear Results",
      "clearResults"
    )
    .addToUi();

}


// ==========================================================
// 5. TEST API AUTHENTICATION
// ==========================================================

function testDataForSEO() {

  const sheet =
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getActiveSheet();


  sheet
    .getRange("H2")
    .setValue(
      "🔐 Testing DataForSEO authentication..."
    );

  SpreadsheetApp.flush();


  try {

    /*
     * Make a real API request.
     */
    const result =
      getSERP(
        "Top SEO Newsletters",
        "desktop"
      );


    const itemCount =
      result.items
        ? result.items.length
        : 0;


    sheet
      .getRange("H2")
      .setValue(
        "✅ API authentication successful"
      );


    SpreadsheetApp.flush();


    SpreadsheetApp
      .getUi()
      .alert(
        "SUCCESS\n\n" +
        "DataForSEO authentication is working.\n\n" +
        "SERP items returned: " +
        itemCount
      );


  } catch (error) {

    const message =
      error.message ||
      String(error);


    sheet
      .getRange("H2")
      .setValue(
        "❌ AUTHENTICATION FAILED"
      );


    sheet
      .getRange("H2")
      .setNote(
        message
      );


    SpreadsheetApp.flush();


    SpreadsheetApp
      .getUi()
      .alert(
        "DATAFORSEO AUTHENTICATION FAILED\n\n" +
        message
      );

  }

}


// ==========================================================
// 6. MAIN FUNCTION
// ==========================================================

function runDataForSEO() {

  const sheet =
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getActiveSheet();


  // --------------------------------------------------------
  // READ DOMAIN FROM B1
  // --------------------------------------------------------

  let targetDomain =
    String(
      sheet
        .getRange(DOMAIN_CELL)
        .getValue()
    ).trim();


  if (!targetDomain) {

    SpreadsheetApp
      .getUi()
      .alert(
        "No domain found.\n\n" +
        "Please enter your domain in B1.\n\n" +
        "Example:\n" +
        "seoriddler.com"
      );

    return;

  }


  /*
   * Normalize domain.
   *
   * Converts:
   * https://www.seoriddler.com/
   *
   * into:
   * seoriddler.com
   */

  targetDomain =
    normalizeDomain(
      targetDomain
    );


  // --------------------------------------------------------
  // LAST ROW
  // --------------------------------------------------------

  const lastRow =
    sheet.getLastRow();


  if (
    lastRow <
    DATA_START_ROW
  ) {

    SpreadsheetApp
      .getUi()
      .alert(
        "No keywords found.\n\n" +
        "Keywords should start in row " +
        DATA_START_ROW +
        "."
      );

    return;

  }


  // --------------------------------------------------------
  // VERIFY API FIRST
  // --------------------------------------------------------

  sheet
    .getRange("H2")
    .setValue(
      "🔐 Verifying DataForSEO API..."
    );

  SpreadsheetApp.flush();


  try {

    getSERP(
      "Top SEO Newsletters",
      "desktop"
    );


    sheet
      .getRange("H2")
      .setValue(
        "✅ API authentication successful"
      );

    SpreadsheetApp.flush();


  } catch (error) {

    const message =
      error.message ||
      String(error);


    sheet
      .getRange("H2")
      .setValue(
        "❌ API authentication FAILED"
      );


    sheet
      .getRange("H2")
      .setNote(
        message
      );


    SpreadsheetApp.flush();


    SpreadsheetApp
      .getUi()
      .alert(
        "API AUTHENTICATION FAILED\n\n" +
        message +
        "\n\n" +
        "No keywords were processed."
      );


    return;

  }


  // --------------------------------------------------------
  // READ KEYWORDS
  // --------------------------------------------------------

  const keywordCount =
    lastRow -
    DATA_START_ROW +
    1;


  const keywords =
    sheet
      .getRange(
        DATA_START_ROW,
        KEYWORD_COLUMN,
        keywordCount,
        1
      )
      .getValues();


  let successful = 0;

  let failed = 0;


  // --------------------------------------------------------
  // PROCESS KEYWORDS
  // --------------------------------------------------------

  for (
    let i = 0;
    i < keywords.length;
    i++
  ) {

    const row =
      DATA_START_ROW + i;


    const keyword =
      String(
        keywords[i][0]
      ).trim();


    // ------------------------------------------------------
    // SKIP BLANK
    // ------------------------------------------------------

    if (!keyword) {

      sheet
        .getRange(
          row,
          STATUS_COLUMN
        )
        .setValue(
          "⏭️ Skipped — blank keyword"
        );

      continue;

    }


    try {

      // ====================================================
      // DESKTOP
      // ====================================================

      updateStatus(
        sheet,
        row,
        "🖥️ Checking desktop US..."
      );


      const desktopResult =
        getSERP(
          keyword,
          "desktop"
        );


      const desktopPosition =
        findDomainPosition(
          desktopResult,
          targetDomain
        );


      sheet
        .getRange(
          row,
          DESKTOP_COLUMN
        )
        .setValue(
          desktopPosition
        );


      // ====================================================
      // MOBILE
      // ====================================================

      updateStatus(
        sheet,
        row,
        "📱 Checking mobile US..."
      );


      const mobileResult =
        getSERP(
          keyword,
          "mobile"
        );


      const mobilePosition =
        findDomainPosition(
          mobileResult,
          targetDomain
        );


      sheet
        .getRange(
          row,
          MOBILE_COLUMN
        )
        .setValue(
          mobilePosition
        );


      // ====================================================
      // AI OVERVIEW
      // ====================================================

      updateStatus(
        sheet,
        row,
        "🤖 Checking AI Overview..."
      );


      const aio =
        extractAIOverview(
          desktopResult
        );


      sheet
        .getRange(
          row,
          AIO_PRESENT_COLUMN
        )
        .setValue(
          aio.exists
        );


      sheet
        .getRange(
          row,
          AIO_TEXT_COLUMN
        )
        .setValue(
          aio.text
        );


      sheet
        .getRange(
          row,
          AIO_URLS_COLUMN
        )
        .setValue(
          aio.urls
        );


      // ====================================================
      // PAA
      // ====================================================

      updateStatus(
        sheet,
        row,
        "❓ Extracting PAA..."
      );


      const paa =
        extractPAA(
          desktopResult
        );


      sheet
        .getRange(
          row,
          PAA_COLUMN
        )
        .setValue(
          paa
        );


      // ====================================================
      // COMPLETE
      // ====================================================

      successful++;


      updateStatus(
        sheet,
        row,
        "✅ Complete"
      );


      Utilities.sleep(300);


    } catch (error) {

      failed++;


      const message =
        error.message ||
        String(error);


      sheet
        .getRange(
          row,
          STATUS_COLUMN
        )
        .setValue(
          "❌ ERROR"
        );


      sheet
        .getRange(
          row,
          STATUS_COLUMN
        )
        .setNote(
          message
        );


      /*
       * Put the error in B as well,
       * so it is obvious which keyword failed.
       */

      sheet
        .getRange(
          row,
          DESKTOP_COLUMN
        )
        .setValue(
          "ERROR"
        );


      sheet
        .getRange(
          row,
          DESKTOP_COLUMN
        )
        .setNote(
          message
        );


      SpreadsheetApp.flush();

    }

  }


  // --------------------------------------------------------
  // FINISHED
  // --------------------------------------------------------

  SpreadsheetApp
    .getUi()
    .alert(
      "Finished!\n\n" +
      "Domain: " +
      targetDomain +
      "\n\n" +
      "Successful: " +
      successful +
      "\n" +
      "Failed: " +
      failed
    );

}


// ==========================================================
// 7. UPDATE STATUS
// ==========================================================

function updateStatus(
  sheet,
  row,
  message
) {

  sheet
    .getRange(
      row,
      STATUS_COLUMN
    )
    .setValue(
      message
    );

  SpreadsheetApp.flush();

}


// ==========================================================
// 8. DATAFORSEO API REQUEST
// ==========================================================

function getSERP(
  keyword,
  device
) {

  // --------------------------------------------------------
  // CHECK CREDENTIAL
  // --------------------------------------------------------

  if (
    !DATAFORSEO_AUTH ||
    DATAFORSEO_AUTH ===
      "PASTE_YOUR_BASE64_CREDENTIAL_HERE"
  ) {

    throw new Error(
      "DataForSEO Base64 credential has not been entered."
    );

  }


  // --------------------------------------------------------
  // REQUEST
  // --------------------------------------------------------

  const payload = [

    {

      keyword:
        keyword,

      location_code:
        LOCATION_CODE,

      language_code:
        LANGUAGE_CODE,

      device:
        device,

      os:
        device === "desktop"
          ? "windows"
          : "android",

      depth:
        SERP_DEPTH,

      load_async_ai_overview:
        true,

      people_also_ask_click_depth:
        1

    }

  ];


  // --------------------------------------------------------
  // HTTP OPTIONS
  // --------------------------------------------------------

  const options = {

    method:
      "post",

    contentType:
      "application/json",

    payload:
      JSON.stringify(
        payload
      ),

    headers: {

      Authorization:
        "Basic " +
        DATAFORSEO_AUTH

    },

    muteHttpExceptions:
      true

  };


  // --------------------------------------------------------
  // SEND REQUEST
  // --------------------------------------------------------

  const response =
    UrlFetchApp.fetch(
      DATAFORSEO_ENDPOINT,
      options
    );


  const httpStatus =
    response.getResponseCode();


  const responseText =
    response.getContentText();


  // --------------------------------------------------------
  // HTTP ERROR
  // --------------------------------------------------------

  if (
    httpStatus !== 200
  ) {

    throw new Error(
      "HTTP " +
      httpStatus +
      "\n\n" +
      responseText
    );

  }


  // --------------------------------------------------------
  // JSON
  // --------------------------------------------------------

  let data;

  try {

    data =
      JSON.parse(
        responseText
      );

  } catch (error) {

    throw new Error(
      "Invalid JSON returned by DataForSEO:\n\n" +
      responseText
    );

  }


  // --------------------------------------------------------
  // DATAFORSEO ERROR
  // --------------------------------------------------------

  if (
    data.status_code &&
    data.status_code !== 20000
  ) {

    throw new Error(
      "DataForSEO API error\n\n" +
      "Code: " +
      data.status_code +
      "\n\n" +
      "Message: " +
      data.status_message
    );

  }


  // --------------------------------------------------------
  // TASK
  // --------------------------------------------------------

  if (
    !data.tasks ||
    data.tasks.length === 0
  ) {

    throw new Error(
      "DataForSEO returned no tasks."
    );

  }


  const task =
    data.tasks[0];


  if (
    task.status_code &&
    task.status_code !== 20000
  ) {

    throw new Error(
      "DataForSEO task error\n\n" +
      "Code: " +
      task.status_code +
      "\n\n" +
      "Message: " +
      task.status_message
    );

  }


  // --------------------------------------------------------
  // RESULT
  // --------------------------------------------------------

  if (
    !task.result ||
    task.result.length === 0
  ) {

    throw new Error(
      "DataForSEO returned no SERP result."
    );

  }


  return task.result[0];

}


// ==========================================================
// 9. FIND TARGET DOMAIN POSITION
// ==========================================================

function findDomainPosition(
  result,
  targetDomain
) {

  if (
    !result ||
    !Array.isArray(
      result.items
    )
  ) {

    return ">100";

  }


  const normalizedTarget =
    normalizeDomain(
      targetDomain
    );


  for (
    let i = 0;
    i < result.items.length;
    i++
  ) {

    const item =
      result.items[i];


    // Only organic results

    if (
      item.type !==
      "organic"
    ) {

      continue;

    }


    if (!item.url) {
      continue;
    }


    const resultDomain =
      normalizeDomain(
        item.url
      );


    if (
      resultDomain ===
        normalizedTarget ||
      resultDomain.endsWith(
        "." +
        normalizedTarget
      )
    ) {

      if (
        item.rank_group !==
          undefined &&
        item.rank_group !==
          null
      ) {

        return item.rank_group;

      }

    }

  }


  return ">100";

}


// ==========================================================
// 10. NORMALIZE DOMAIN
// ==========================================================

function normalizeDomain(
  domain
) {

  return String(domain)
    .toLowerCase()
    .trim()
    .replace(
      /^https?:\/\//,
      ""
    )
    .replace(
      /^www\./,
      ""
    )
    .split("/")[0]
    .split("?")[0]
    .split("#")[0];

}


// ==========================================================
// 11. AI OVERVIEW
// ==========================================================

function extractAIOverview(
  result
) {

  const output = {

    exists:
      false,

    text:
      "",

    urls:
      ""

  };


  if (
    !result ||
    !Array.isArray(
      result.items
    )
  ) {

    return output;

  }


  const aio =
    result.items.find(
      function(item) {

        return (
          item.type ===
          "ai_overview"
        );

      }
    );


  if (!aio) {

    return output;

  }


  output.exists =
    true;


  const textParts = [];

  const urls = [];


  extractAIData(
    aio,
    textParts,
    urls
  );


  output.text =
    [
      ...new Set(
        textParts
          .filter(Boolean)
      )
    ].join(
      "\n\n"
    );


  output.urls =
    [
      ...new Set(
        urls
          .filter(Boolean)
      )
    ].join(
      "\n"
    );


  return output;

}


// ==========================================================
// 12. RECURSIVE AI DATA EXTRACTION
// ==========================================================

function extractAIData(
  object,
  textParts,
  urls
) {

  if (!object) {
    return;
  }


  // Array

  if (
    Array.isArray(object)
  ) {

    object.forEach(
      function(item) {

        extractAIData(
          item,
          textParts,
          urls
        );

      }
    );

    return;

  }


  // Object

  if (
    typeof object !==
    "object"
  ) {

    return;

  }


  // Text

  if (
    typeof object.text ===
    "string"
  ) {

    textParts.push(
      object.text
    );

  }


  // Markdown

  if (
    typeof object.markdown ===
    "string"
  ) {

    textParts.push(
      object.markdown
    );

  }


  // URL

  if (
    typeof object.url ===
    "string"
  ) {

    urls.push(
      object.url
    );

  }


  // Links

  if (
    Array.isArray(
      object.links
    )
  ) {

    object.links.forEach(
      function(link) {

        if (
          link &&
          typeof link.url ===
            "string"
        ) {

          urls.push(
            link.url
          );

        }

      }
    );

  }


  // References

  if (
    Array.isArray(
      object.references
    )
  ) {

    object.references.forEach(
      function(reference) {

        if (
          reference &&
          typeof reference.url ===
            "string"
        ) {

          urls.push(
            reference.url
          );

        }

      }
    );

  }


  // Nested items

  if (
    Array.isArray(
      object.items
    )
  ) {

    object.items.forEach(
      function(item) {

        extractAIData(
          item,
          textParts,
          urls
        );

      }
    );

  }

}


// ==========================================================
// 13. PEOPLE ALSO ASK
// ==========================================================

function extractPAA(
  result
) {

  if (
    !result ||
    !Array.isArray(
      result.items
    )
  ) {

    return "";

  }


  const questions = [];


  result.items.forEach(
    function(item) {

      if (
        item.type !==
        "people_also_ask"
      ) {

        return;

      }


      if (
        !Array.isArray(
          item.items
        )
      ) {

        return;

      }


      item.items.forEach(
        function(paaItem) {

          if (
            typeof paaItem.title ===
            "string"
          ) {

            questions.push(
              paaItem.title
            );

          }


          if (
            Array.isArray(
              paaItem.items
            )
          ) {

            paaItem.items.forEach(
              function(nested) {

                if (
                  typeof nested.title ===
                  "string"
                ) {

                  questions.push(
                    nested.title
                  );

                }

              }
            );

          }

        }
      );

    }
  );


  return [
    ...new Set(
      questions.filter(Boolean)
    )
  ].join(
    "\n"
  );

}


// ==========================================================
// 14. CLEAR RESULTS
// ==========================================================

function clearResults() {

  const sheet =
    SpreadsheetApp
      .getActiveSpreadsheet()
      .getActiveSheet();


  const lastRow =
    sheet.getLastRow();


  if (
    lastRow <
    DATA_START_ROW
  ) {

    return;

  }


  sheet
    .getRange(
      DATA_START_ROW,
      DESKTOP_COLUMN,
      lastRow -
        DATA_START_ROW +
        1,
      7
    )
    .clearContent();


  sheet
    .getRange(
      DATA_START_ROW,
      DESKTOP_COLUMN,
      lastRow -
        DATA_START_ROW +
        1,
      7
    )
    .clearNote();


  SpreadsheetApp
    .getUi()
    .alert(
      "Results cleared."
    );

}

DataForSEO SERP Trackerfor Google Sheets