Mastering SEO : Free Apps Script for Bulk Audit Spreadsheet Backlinks

// ------------------------------
// Create menu on spreadsheet open
// ------------------------------
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Scan and Check')
    .addItem('Scan and Check', 'scanAndCheck')
    .addToUi();
}

// ------------------------------
// Main scan and check workflow
// ------------------------------
function scanAndCheck() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();

  var successUrls = [];
  var failedUrls = [];
  var invalidUrls = [];
  var visitedDomains = []; // Store visited domains to skip redundant checks

  // Add a column for "Status on Current Date" if it doesn't exist
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var statusColumnIndex = headers.indexOf("Status on Current Date");
  if (statusColumnIndex === -1) {
    statusColumnIndex = headers.length + 1;
    sheet.getRange(1, statusColumnIndex).setValue("Status on Current Date");
  }

  // Format current date
  var currentDate = new Date();
  var formattedDate = Utilities.formatDate(currentDate, "GMT", "dd-MM-yyyy");

  // Check if the date has already been set
  var dateAlreadySet = false;
  for (var i = 1; i < data.length; i++) {
    var statusValue = sheet.getRange(i + 1, statusColumnIndex).getValue();
    if (statusValue && statusValue.includes(formattedDate)) {
      dateAlreadySet = true;
      break;
    }
  }

  // Set the date at the top of the column if not already set
  if (!dateAlreadySet) {
    sheet.getRange(1, statusColumnIndex).setValue(formattedDate);
  }

  // Add a new column for "Status"
  var statusTextColumnIndex = statusColumnIndex + 1;
  sheet.getRange(1, statusTextColumnIndex).setValue("Status");

  // Iterate through all rows
  for (var i = 1; i < data.length; i++) {
    var url = data[i][0];

    // Skip empty rows
    if (!url) continue;

    // Skip invalid URLs
    if (!isValidUrl(url)) {
      invalidUrls.push(url);
      sheet.getRange(i + 1, statusTextColumnIndex).setValue("Invalid domain").setFontColor("blue");
      continue;
    }

    // Check link validity and backlink
    checkLinkValidity(url, i, statusColumnIndex, statusTextColumnIndex, sheet, successUrls, failedUrls, visitedDomains);
  }

  // Display results
  var resultMessage = "Successful URLs with outrightcrm.com backlink:\n" + successUrls.join("\n") +
                      "\n\nFailed URLs:\n" + failedUrls.join("\n") +
                      "\n\nInvalid URLs:\n" + invalidUrls.join("\n");

  SpreadsheetApp.getUi().alert('Scan Results', resultMessage, SpreadsheetApp.getUi().ButtonSet.OK);
}

// ------------------------------
// Check link validity and backlinks
// ------------------------------
function checkLinkValidity(url, rowIndex, statusColumnIndex, statusTextColumnIndex, sheet, successUrls, failedUrls, visitedDomains) {
  try {
    var response = UrlFetchApp.fetch(url);
    var statusCode = response.getResponseCode();

    if (statusCode == 200) {
      var isSelfWebsite = url.startsWith("https://outright") || hasBacklink(url, "outrightcrm.com");

      if (isSelfWebsite) {
        successUrls.push(url);
        sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Targeted Url").setFontColor("green");
        sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("backlink found").setFontColor("green");
      } else {
        failedUrls.push(url);
        sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Outside Website").setFontColor("red");
        sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("No backlink found").setFontColor("red");
      }

      // Mark the domain as visited
      var domain = getDomain(url);
      visitedDomains.push(domain);
    } else {
      failedUrls.push(url);
      sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Failed (Non-200)").setFontColor("red");
      sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("Failed (Non-200)").setFontColor("red");
    }
  } catch (error) {
    failedUrls.push(url);
    sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Failed (Error)").setFontColor("red");
    sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("Failed (Error)").setFontColor("red");
    Logger.log('Error checking link: ' + error);
  }
}

// ------------------------------
// Extract domain from URL
// ------------------------------
function getDomain(url) {
  var match = url.match(/^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n?]+)/);
  return match ? match[1] : null;
}

// ------------------------------
// Check for backlink to a domain
// ------------------------------
function hasBacklink(url, domain) {
  var urlContent = getContentFromUrl(url);
  var regex = new RegExp(domain.replace('.', '\\.'), 'i');
  return regex.test(urlContent);
}

// ------------------------------
// Get HTML content from URL
// ------------------------------
function getContentFromUrl(url) {
  try {
    var response = UrlFetchApp.fetch(url);
    return response.getContentText();
  } catch (e) {
    Logger.log("Error fetching content from URL: " + url);
    return '';
  }
}

// ------------------------------
// Validate URL format
// ------------------------------
function isValidUrl(url) {
  var urlRegex = /^(https?|ftp):\/\/[^\s\/$.?#].[^\s]*$/i;
  return urlRegex.test(url);
}

// ------------------------------
// Call OpenAI API
// ------------------------------
const OPENAI_API_KEY = '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';

function callOpenAI(prompt) {
  const url = 'https://api.openai.com/v1/engines/davinci-codex/completions';
  const payload = { prompt: prompt, max_tokens: 150 };
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: { 'Authorization': `Bearer ${OPENAI_API_KEY}` },
    payload: JSON.stringify(payload)
  };
  const response = UrlFetchApp.fetch(url, options);
  const responseJson = JSON.parse(response.getContentText());
  return responseJson.choices[0].text;
}

// ------------------------------
// Execute JavaScript code dynamically
// ------------------------------
function executeScript(scriptCode) {
  try {
    const result = eval(scriptCode);
    return { success: true, result: result };
  } catch (error) {
    return { success: false, error: error.toString() };
  }
}

// ------------------------------
// Web app GET handler
// ------------------------------
function doGet() {
  const htmlOutput = HtmlService.createHtmlOutputFromFile('Index');
  return htmlOutput.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

// ------------------------------
// Main workflow for OpenAI
// ------------------------------
function mainWorkflow(initialPrompt) {
  let scriptCode = callOpenAI(initialPrompt);
  let executionResult = executeScript(scriptCode);

  if (!executionResult.success) {
    Logger.log('Error encountered: ' + executionResult.error);
  } else {
    Logger.log('Execution successful: ' + executionResult.result);
  }
}