How to Monitor GTM Scripts on Websites with Google Apps Script

// Constants
const EMAIL_RECIPIENTS = "your_email@example.com"; // Email recipients for alerts
const SHEET_NAME = "GTM Monitoring"; // Name of the Google Sheet where data is stored
const RECHECK_DELAY_MS = 30 * 60 * 1000; // Time delay for recheck in milliseconds (30 minutes)

// Function to perform the initial check of GTM status and URL status
function checkGTMStatus() {
  // Get the sheet and data to process
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 7).getValues(); // Read data starting from row 2
  const currentTime = new Date();
  const istTimeString = getISTTimeString(currentTime); // Get the current time in IST
  const lastRunTimes = Array(data.length).fill([istTimeString]); // Create a timestamp for the check
  let recheckNeeded = false; // Flag to determine if a recheck is required
  const notFoundUrls = []; // Store URLs with missing GTM IDs or errors

  // Iterate through each row of data
  const results = data.map((row, i) => {
    const [url, gtmID] = row; // Extract URL and GTM ID from the row
    if (!url || !gtmID) {
      return ["N/A", "N/A"]; // Skip rows with missing data
    }
    const statusResult = checkGTM(url, gtmID); // Check the GTM status for the URL
    if (statusResult.statusCode !== 200 || statusResult.status === "Not Found") {
      recheckNeeded = true; // Mark as recheck needed if GTM is not found or there is an error
      notFoundUrls.push([url, gtmID, statusResult.statusCode, statusResult.status, istTimeString]);
      if (!row[6]) {
        row[6] = istTimeString; // Log the first detection time for issues
      }
    }
    return [statusResult.statusCode, statusResult.status];
  });

  // Update the sheet with the results
  updateSheet(sheet, results, lastRunTimes, data);
  formatResults(sheet, results); // Apply formatting for clarity

  // Schedule a recheck if needed
  if (recheckNeeded) {
    Logger.log("First-level check failed. Recheck needed. Scheduling recheck in 30 minutes.");
    deleteExistingTriggers('recheckGTMStatus'); // Ensure no duplicate triggers are created
    ScriptApp.newTrigger('recheckGTMStatus').timeBased().after(RECHECK_DELAY_MS).create(); // Schedule the recheck
  } else {
    Logger.log("First-level check passed. No issues found.");
  }
}

// Function to perform the recheck of GTM status and URL status
function recheckGTMStatus() {
  // Get the sheet and data for rechecking
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 7).getValues(); // Read data starting from row 2
  const currentTime = new Date();
  const istTimeString = getISTTimeString(currentTime); // Get the current time in IST
  const lastRunTimes = Array(data.length).fill([istTimeString]); // Create a timestamp for the recheck
  const notFoundUrls = []; // Store URLs with missing GTM IDs or errors

  // Iterate through each row of data for recheck
  const recheckResults = data.map((row, i) => {
    const [url, gtmID] = row; // Extract URL and GTM ID from the row
    if (!url || !gtmID) {
      return ["N/A"]; // Skip rows with missing data
    }
    const statusResult = checkGTM(url, gtmID); // Check the GTM status for the URL
    if (statusResult.statusCode !== 200 || statusResult.status === "Not Found") {
      notFoundUrls.push([url, gtmID, statusResult.statusCode, statusResult.status, istTimeString]);
      if (!row[6]) {
        row[6] = istTimeString; // Log the first detection time for issues
      }
    }
    return [statusResult.status];
  });

  // Update the sheet with the recheck results
  updateRecheckSheet(sheet, recheckResults, lastRunTimes, data);
  formatResults(sheet, recheckResults, 5); // Apply formatting for clarity

  // Send an email alert if issues are found
  if (notFoundUrls.length > 0) {
    Logger.log("Recheck confirmed missing GTM IDs or URLs returning non-200 status. Sending alert email.");
    sendEmailAlert(notFoundUrls);
  }

  Logger.log("Recheck completed.");
}

// Function to check the GTM ID presence in the URL
function checkGTM(url, gtmID) {
  try {
    const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true }); // Fetch the URL content
    const statusCode = response.getResponseCode(); // Get the HTTP status code
    if (statusCode === 200) {
      const content = response.getContentText(); // Get the content of the page
      const regex = new RegExp(`\\b${gtmID}\\b`, 'g'); // Create a regex to match the GTM ID
      const found = regex.test(content); // Check if the GTM ID is found in the content
      Logger.log(`Checked ${url} - GTM ID ${found ? "Found" : "Not Found"}`);
      return { status: found ? "Found" : "Not Found", statusCode: statusCode };
    }
    return { status: "Error", statusCode: statusCode };
  } catch (error) {
    Logger.log(`Error fetching ${url}: ${error}`);
    return { status: "Error", statusCode: "N/A" };
  }
}

// Function to send an email alert for issues found
function sendEmailAlert(notFoundUrls) {
  // Iterate through the issues and send an email for each
  notFoundUrls.forEach(([url, gtmID, statusCode, status, firstDetected]) => {
    const subject = `Alert: ${url} - GTM ID Missing or URL Status Issue`;
    const body = `The following issue was detected:\n\nURL: ${url}\nGTM ID: ${gtmID}\nHTTP Status Code: ${statusCode}\nGTM Status: ${status}\nFirst Detected: ${firstDetected}\n\n`;
    MailApp.sendEmail(EMAIL_RECIPIENTS, subject, body); // Send the email
  });
}

// Function to format the results in the sheet
function formatResults(sheet, results, statusCodeCol = 3, gtmStatusCol = 4) {
  const statusCodes = results.map(result => [result[0]]); // Extract status codes
  const gtmResults = results.map(result => [result[1]]); // Extract GTM statuses
  const rangeStatusCodes = sheet.getRange(2, statusCodeCol, statusCodes.length, 1); // Define the range for status codes
  const rangeGtmResults = sheet.getRange(2, gtmStatusCol, gtmResults.length, 1); // Define the range for GTM results

  // Reset formatting to default for the defined ranges
  rangeStatusCodes.setBackground("white").setFontColor("black");
  rangeGtmResults.setBackground("white").setFontColor("black");

  // Apply new formatting based on the results
  results.forEach((result, i) => {
    const statusCodeCell = rangeStatusCodes.getCell(i + 1, 1);
    const gtmResultCell = rangeGtmResults.getCell(i + 1, 1);

    // Format for HTTP status code
    if (result[0] === 200) {
      statusCodeCell.setBackground("green").setFontColor("white");
    } else if (result[0] !== "N/A") {
      statusCodeCell.setBackground("red").setFontColor("white");
    }

    // Format for GTM status
    if (result[1] === "Found") {
      gtmResultCell.setBackground("green").setFontColor("white");
    } else if (result[1] !== "N/A") {
      gtmResultCell.setBackground("red").setFontColor("white");
    }
  });
}

// Function to convert current time to IST string
function getISTTimeString(currentTime) {
  return new Date(currentTime.toLocaleString("en-US", { timeZone: "Asia/Kolkata" })).toString(); // Convert to IST time
}

// Function to update the sheet with the initial check results
function updateSheet(sheet, results, lastRunTimes, data) {
  const statusCodes = results.map(result => [result[0]]); // Extract status codes
  const gtmResults = results.map(result => [result[1]]); // Extract GTM statuses
  sheet.getRange(2, 3, statusCodes.length, 1).setValues(statusCodes); // Update status codes column
  sheet.getRange(2, 4, gtmResults.length, 1).setValues(gtmResults); // Update GTM statuses column
  sheet.getRange(2, 6, lastRunTimes.length, 1).setValues(lastRunTimes); // Update last run times column
  sheet.getRange(2, 7, data.length, 1).setValues(data.map(row => [row[6]])); // Update first detection column
}

// Function to update the sheet with the recheck results
function updateRecheckSheet(sheet, recheckResults, lastRunTimes, data) {
  sheet.getRange(2, 5, recheckResults.length, 1).setValues(recheckResults); // Update recheck results column
  sheet.getRange(2, 6, lastRunTimes.length, 1).setValues(lastRunTimes); // Update last run times column
  sheet.getRange(2, 7, data.length, 1).setValues(data.map(row => [row[6]])); // Update first detection column
}

// Function to delete existing triggers for a specific function
function deleteExistingTriggers(functionName) {
  const triggers = ScriptApp.getProjectTriggers(); // Get all triggers in the project
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === functionName) {
      ScriptApp.deleteTrigger(trigger); // Delete the trigger matching the function name
    }
  });
}