Google Sheets Extract the Top 3 results Title

gistfile1.txt

var api_key = "YOUR_SERPAPI_KEY_HERE"; // Replace with your actual SerpAPI key
var fixedLocation = 'United States'; // Replace with your location

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('SERP Update')
    .addItem('Update SERP Info', 'updateSerpInfo')
    .addToUi();
}

function updateSerpInfo() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var dataRange = sheet.getDataRange();
  var data = dataRange.getValues();

  var headers = data[0];
  var queryCol = headers.indexOf("Top queries");
  if (queryCol == -1) {
    throw new Error("Required column ('Top queries') not found.");
  }

  // Columns for the top 3 SERP titles
  var titleCols = ['SERP Title 1', 'SERP Title 2', 'SERP Title 3'].map(function(title) {
    var index = headers.indexOf(title);
    if (index === -1) {
      throw new Error("Required column ('" + title + "') not found.");
    }
    return index;
  });

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var query = row[queryCol];

    if (query) {
      var serpTitles = fetchTop3SerpTitles(query, fixedLocation);
      serpTitles.forEach(function(title, index) {
        sheet.getRange(i + 1, titleCols[index] + 1).setValue(title || "Not found");
      });
    }
  }
}

function fetchTop3SerpTitles(query, location) {
  var url = 'https://serpapi.com/search'
    + '?q=' + encodeURIComponent(query)
    + '&google_domain=google.com'
    + '&num=100'
    + '&location=' + encodeURIComponent(location)
    + '&api_key=' + api_key;

  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  try {
    var json = JSON.parse(response.getContentText());
    var titles = json.organic_results.slice(0, 3).map(function(result) {
      return result.title;
    });
    return titles;
  } catch (e) {
    Logger.log('Error parsing JSON: ' + e.toString());
    Logger.log('Response content: ' + response.getContentText());
    return Array(3).fill(null); // Return an array of nulls if there's an error
  }
}