AppSScript Code

Copy Code

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Scan and Check')
    .addItem('Scan and Check', 'scanAndCheck')
    .addToUi();
}




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


  var successUrls = [];
  var failedUrls = [];
  var invalidUrls = [];


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


  // 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");
  }


  var currentDate = new Date();
  var year = Utilities.formatDate(currentDate, "GMT", "yyyy");
  var month = Utilities.formatDate(currentDate, "GMT", "MM");
  var day = Utilities.formatDate(currentDate, "GMT", "dd");
  var formattedDate = day + "-" + month + "-" + year;


  // 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 "Status on Current Date" column if not already set
  if (!dateAlreadySet) {
    sheet.getRange(1, statusColumnIndex).setValue(formattedDate);
  }


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


  for (var i = 1; i < data.length; i++) {
    var url = data[i][0];


    // Check if the URL is empty
    if (!url) {
      continue; // Skip empty rows
    }


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


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


  // Display results in an alert
  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");


  // Show alert in the browser
  var ui = SpreadsheetApp.getUi();
  ui.alert('Scan Results', resultMessage, ui.ButtonSet.OK);
}


function checkLinkValidity(url, rowIndex, statusColumnIndex, statusTextColumnIndex, sheet, successUrls, failedUrls, visitedDomains) {
  try {
    var response = UrlFetchApp.fetch(url);
    var statusCode = response.getResponseCode();
    if (statusCode == 200) {
      // Task 1: Scan URL
      // Perform actions for scanning the URL (you can replace this with your own logic)


      // Task 2: Check if the URL is from the self website or outside website
      var isSelfWebsite = url.startsWith("https://outright") || hasBacklink(url, "outrightcrm.com");
     
      // Store the results
      if (isSelfWebsite) {
        successUrls.push(url);
        // Update the "Status on Current Date" column for success with today's date
        sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Targeted Url").setFontColor("green");
        // Update the new "Status" column with "Successful"
        sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("backlink found").setFontColor("green");
      } else {
        failedUrls.push(url);
        // Update the "Status on Current Date" column for failure with today's date
        sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Outside Website").setFontColor("red");
        // Update the new "Status" column with "Failed"
        sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("No backlink found").setFontColor("red");
      }


      // Mark the domain as visited
      var domain = getDomain(url);
      visitedDomains.push(domain);
    } else {
      // Handle non-200 status code
      failedUrls.push(url);
      // Update the "Status on Current Date" column for failure with today's date
      sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Failed (Non-200)").setFontColor("red");
      // Update the new "Status" column with "Failed"
      sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("Failed (Non-200)").setFontColor("red");
    }
  } catch (error) {
    // Handle fetch error
    failedUrls.push(url);
    // Update the "Status on Current Date" column for failure with today's date
    sheet.getRange(rowIndex + 1, statusColumnIndex).setValue("Failed (Error)").setFontColor("red");
    // Update the new "Status" column with "Failed"
    sheet.getRange(rowIndex + 1, statusTextColumnIndex).setValue("Failed (Error)").setFontColor("red");
    Logger.log('An error occurred while checking the link validity: ' + error);
  }
}


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


// Function to check if the URL has a backlink to outrightcrm.com
function hasBacklink(url, domain) {
  // Implement logic to check if the URL has a backlink to the specified domain
  // You may use regular expressions or other methods
  var urlContent = getContentFromUrl(url);
  var regex = new RegExp('outrightcrm\\.com', 'i');
  return regex.test(urlContent);
}


// Function to get content from a URL
function getContentFromUrl(url) {
  try {
    var response = UrlFetchApp.fetch(url);
    return response.getContentText();
  } catch (e) {
    // Handle any errors or return an appropriate value
    Logger.log("Error fetching content from URL: " + url);
    return '';
  }
}


// Function to validate URLs
function isValidUrl(url) {
  var urlRegex = /^(https?|ftp):\/\/[^\s\/$.?#].[^\s]*$/i;
  return urlRegex.test(url);
}


// Function to fetch tasks from ChatGPT using its API
const OPENAI_API_KEY = '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';  // Replace with your actual API key


// Function to call OpenAI API
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;
}


// Function to execute script code
function executeScript(scriptCode) {
    try {
        const result = eval(scriptCode);
        return { success: true, result: result };
    } catch (error) {
        return { success: false, error: error.toString() };
    }
}


// Function to handle the web app's GET request
function doGet() {
    const htmlOutput = HtmlService.createHtmlOutputFromFile('Index');
    return htmlOutput.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}




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


    if (!executionResult.success) {
        Logger.log('Error encountered: ' + executionResult.error);
        // Additional error handling and feedback mechanism should be implemented here
    } else {
        Logger.log('Execution successful: ' + executionResult.result);
        // Handle successful execution
    }
}