Automate GTM ID and URL Status Checks with Google Apps Script for Martech Optimization

function checkGTMStatus() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GTM Monitoring");
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 7).getValues();
  var gtmResults = [];
  var statusCodes = [];
  var notFoundUrls = [];
  var recheckNeeded = false;
  var currentTime = new Date();
  var istTime = new Date(currentTime.toLocaleString("en-US", { timeZone: "Asia/Kolkata" }));
  var istTimeString = istTime.toString();
  var lastRunTimes = Array(data.length).fill([istTimeString]);

  for (var i = 0; i < data.length; i++) {
    var url = data[i][0];
    var gtmID = data[i][1];
    var statusResult = checkGTM(url, gtmID);
    statusCodes.push([statusResult.statusCode]);
    gtmResults.push([statusResult.status]);
    if (statusResult.statusCode !== 200 || statusResult.status === "Not Found") {
      recheckNeeded = true;
      notFoundUrls.push([url, gtmID, statusResult.statusCode, statusResult.status, istTimeString]);
      if (!data[i][6]) { // if Column G is empty
        data[i][6] = istTimeString; // log the first not found time
      }
    }
  }

  sheet.getRange(2, 3, statusCodes.length, 1).setValues(statusCodes);
  sheet.getRange(2, 4, gtmResults.length, 1).setValues(gtmResults);
  sheet.getRange(2, 6, lastRunTimes.length, 1).setValues(lastRunTimes);
  sheet.getRange(2, 7, data.length, 1).setValues(data.map(row => [row[6]])); // update Column G

  formatResults(sheet, statusCodes, gtmResults, 2, 3, 4);

  if (recheckNeeded) {
    Logger.log("First-level check failed. Recheck needed. Scheduling recheck in 30 minutes.");
    ScriptApp.newTrigger('recheckGTMStatus')
      .timeBased()
      .after(30 * 60 * 1000) // 30 minutes delay
      .create();
  } else {
    Logger.log("First-level check passed. No issues found.");
  }

  if (notFoundUrls.length > 0) {
    sendEmailAlert(notFoundUrls, "Initial Check Alert");
  }
}

function recheckGTMStatus() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GTM Monitoring");
  var data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 7).getValues();
  var recheckResults = [];
  var notFoundUrls = [];
  var missingGTM = [];
  var currentTime = new Date();
  var istTime = new Date(currentTime.toLocaleString("en-US", { timeZone: "Asia/Kolkata" }));
  var istTimeString = istTime.toString();
  var lastRunTimes = Array(data.length).fill([istTimeString]);

  for (var i = 0; i < data.length; i++) {
    var url = data[i][0];
    var gtmID = data[i][1];
    var statusResult = checkGTM(url, gtmID);
    recheckResults.push([statusResult.status]);
    if (statusResult.statusCode !== 200) {
      notFoundUrls.push([url, gtmID, statusResult.statusCode, statusResult.status, istTimeString]);
    }
    if (statusResult.status === "Not Found") {
      missingGTM.push([url, gtmID, statusResult.statusCode, statusResult.status, istTimeString]);
      if (!data[i][6]) { // if Column G is empty
        data[i][6] = istTimeString; // log the first not found time
      }
    }
  }

  sheet.getRange(2, 5, recheckResults.length, 1).setValues(recheckResults);
  sheet.getRange(2, 6, lastRunTimes.length, 1).setValues(lastRunTimes);
  sheet.getRange(2, 7, data.length, 1).setValues(data.map(row => [row[6]])); // update Column G

  formatResults(sheet, recheckResults, recheckResults, 2, 5, 5);

  if (missingGTM.length > 0) {
    Logger.log("Recheck found missing GTM IDs. Sending alert email.");
    sendEmailAlert(missingGTM, "Recheck Alert: GTM ID Missing");
  }

  if (notFoundUrls.length > 0) {
    Logger.log("Recheck found URLs returning non-200 status. Sending alert email.");
    sendEmailAlert(notFoundUrls, "Recheck Alert: URL Status Issue");
  }

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

function checkGTM(url, gtmID) {
  try {
    var response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
    var statusCode = response.getResponseCode();
    if (statusCode === 200) {
      var content = response.getContentText();
      var regex = new RegExp(`\\b${gtmID}\\b`, 'g'); // Ensure full match with word boundaries
      var found = regex.test(content);
      if (found) {
        Logger.log(`Found exact GTM ID match in content from ${url}`);
      }
      return { status: found ? "Found" : "Not Found", statusCode: statusCode };
    } else {
      return { status: "Error", statusCode: statusCode };
    }
  } catch (error) {
    Logger.log("Error fetching " + url + ": " + error);
    return { status: "Error", statusCode: "N/A" };
  }
}

function sendEmailAlert(notFoundUrls, subject) {
  var emailRecipients = "your-email@example.com, colleague1@example.com, colleague2@example.com"; // Add your email addresses here
  var body = "The following issues were detected:\n\n";

  for (var i = 0; i < notFoundUrls.length; i++) {
    body += "URL: " + notFoundUrls[i][0] + "\n";
    body += "GTM ID: " + notFoundUrls[i][1] + "\n";
    body += "HTTP Status Code: " + notFoundUrls[i][2] + "\n";
    body += "GTM Status: " + notFoundUrls[i][3] + "\n";
    body += "First Detected: " + notFoundUrls[i][4] + "\n\n";
  }

  MailApp.sendEmail(emailRecipients, subject, body);
}

function formatResults(sheet, statusCodes, gtmResults, startRow, statusCodeCol, gtmStatusCol) {
  var rangeStatusCodes = sheet.getRange(startRow, statusCodeCol, statusCodes.length, 1);
  var rangeGtmResults = sheet.getRange(startRow, gtmStatusCol, gtmResults.length, 1);

  // Clear existing formatting
  rangeStatusCodes.setBackground(null).setFontColor(null);
  rangeGtmResults.setBackground(null).setFontColor(null);

  for (var i = 0; i < statusCodes.length; i++) {
    if (statusCodes[i][0] === 200 && gtmResults[i][0] === "Found") {
      rangeStatusCodes.getCell(i + 1, 1).setBackground("green").setFontColor("white");
      rangeGtmResults.getCell(i + 1, 1).setBackground("green").setFontColor("white");
    } else {
      rangeStatusCodes.getCell(i + 1, 1).setBackground("red").setFontColor("white");
      rangeGtmResults.getCell(i + 1, 1).setBackground("red").setFontColor("white");
    }
  }
}