Google Apps Script to Extract SERP Data with AI Overviews Using SerpAPI

update_serp_info_with_ai_overview.gs

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('SERPAPI Tools')
    .addItem('Get AI Overviews', 'getAiOverviews')
    .addToUi();
}

function getAiOverviews() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var ui = SpreadsheetApp.getUi();
  
  // Prompt user to enter the output column letter
  var result = ui.prompt('Enter Output Column', 'Please enter the column letter where you want the output to start (e.g., F):', ui.ButtonSet.OK_CANCEL);
  
  // Process the user's response
  if (result.getSelectedButton() != ui.Button.OK) {
    ui.alert('Operation cancelled.');
    return;
  }
  
  var outputColumnLetter = result.getResponseText().toUpperCase();
  var outputColumnIndex = columnLetterToIndex(outputColumnLetter);
  
  if (isNaN(outputColumnIndex)) {
    ui.alert('Invalid column letter. Please try again.');
    return;
  }
  
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(2, 1, lastRow - 1, 1); // Column A
  var data = dataRange.getValues();
  
  // Clear previous outputs in the output columns
  sheet.getRange(2, outputColumnIndex, lastRow - 1, 4).clearContent(); // Adjust the number of columns as needed
  
  for (var i = 0; i < data.length; i++) {
    var query = data[i][0];
    if (query == "") {
      continue;
    }
    SpreadsheetApp.getActiveSpreadsheet().toast('Processing query ' + (i + 1) + ' of ' + data.length);
    try {
      var response = makeSerpApiRequest(query);
      var outputText = "";
      var aiOverview = response["ai_overview"];
      if (aiOverview && aiOverview["text_blocks"]) {
        // Parse AI Overview
        outputText = parseAiOverview(aiOverview);
      } else {
        outputText = "No AI Overview available.";
      }

      // Output the AI Overview text into the specified output column
      sheet.getRange(i + 2, outputColumnIndex).setValue(outputText.trim()); // AI Overview
      sheet.getRange(i + 2, outputColumnIndex + 1).setValue(new Date()); // Timestamp

    } catch (e) {
      sheet.getRange(i + 2, outputColumnIndex).setValue("Error: " + e.message);
      sheet.getRange(i + 2, outputColumnIndex + 1).setValue(new Date()); // Timestamp
    }
    // Pause to avoid hitting rate limits
    Utilities.sleep(1500);
  }
  SpreadsheetApp.getActiveSpreadsheet().toast('Processing complete.');
}

function makeSerpApiRequest(query) {
  var apiKey = "YOUR_SERPAPI_KEY"; // Replace with your SERPAPI key
  var apiUrl = "https://serpapi.com/search.json";
  var params = {
    "q": query,
    "engine": "google",
    "api_key": apiKey,
    "gl": "us", // Set country to US
    "hl": "en", // Set language to English
    "device": "desktop", // Ensure consistent results
    "no_cache": "true" // Disable cache to get fresh results
  };
  var url = apiUrl + "?" + Object.keys(params).map(function(k) {
    return encodeURIComponent(k) + '=' + encodeURIComponent(params[k]);
  }).join('&');
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  return json;
}

function parseAiOverview(aiOverview) {
  var output = "";
  var references = aiOverview["references"] || [];

  // Create a mapping of reference indexes to reference details
  var referenceMap = {};
  for (var i = 0; i < references.length; i++) {
    var ref = references[i];
    referenceMap[ref.index] = ref;
  }

  var textBlocks = aiOverview["text_blocks"];
  for (var j = 0; j < textBlocks.length; j++) {
    var textBlock = textBlocks[j];
    output += parseTextBlock(textBlock, referenceMap) + "\n";
  }
  return output;
}

function parseTextBlock(textBlock, referenceMap) {
  var output = "";
  var type = textBlock["type"];

  if (type === "paragraph") {
    output += textBlock["snippet"] || "";
    var refs = getReferences(textBlock["reference_indexes"], referenceMap);
    if (refs) {
      output += " " + refs;
    }
  } else if (type === "list") {
    var listItems = textBlock["list"];
    for (var i = 0; i < listItems.length; i++) {
      var item = listItems[i];
      output += "- " + item["snippet"];
      var refs = getReferences(item["reference_indexes"], referenceMap);
      if (refs) {
        output += " " + refs;
      }
      output += "\n";
    }
  }
  return output.trim();
}

function getReferences(indexes, referenceMap) {
  if (!indexes || indexes.length === 0) {
    return "";
  }
  var refs = indexes.map(function(index) {
    var ref = referenceMap[index];
    if (ref) {
      return "[Ref " + (index + 1) + "]";
    } else {
      return "";
    }
  });
  return refs.join(" ");
}

// Helper function to convert column letter to index
function columnLetterToIndex(letter) {
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++) {
    column += (letter.toUpperCase().charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
  }
  return column;
}