July 22, 2024

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


Ever found yourself knee-deep in deployment issues, missing GTM tags, or URLs throwing 404s? Yeah, we've all been there. It's a pain, especially when you're trying to keep your marketing operations running smoothly. But don't worry; I've got a solution that will make your life a lot easier.

Meet Your New Best Friend: Google Apps Script

We're talking about a script that automates the checking of GTM IDs and URL statuses. This isn't just a simple script—it's a game-changer for anyone dealing with deployment-related issues. It runs periodically, logs any problems, colors the cells for quick identification, and sends out detailed email alerts. It's like having a watchdog that never sleeps.

The Magic Script

Here's the script that will save your sanity:

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

Why This Script Rocks

  1. Periodic Checks: It runs at set intervals to keep an eye on all your URLs.
  2. Status Logging: Logs the HTTP status of each URL and checks if the GTM ID is present.
  3. Color Coding: Uses green for success and red for errors, making it easy to spot issues at a glance.
  4. First Detection Time: Logs when an issue was first detected, so you can track how long it’s been a problem.
  5. Email Alerts: Sends detailed email alerts with all the relevant info about any issues detected.

Setting Up Your Google Sheet

First, create a new Google Sheet and name it something relevant, like “GTM Monitoring”. Then set up your sheet like this:

Row 1 Headers:

• Column A: URL

• Column B: GTM ID

• Column C: HTTP Status Code

• Column D: GTM Status

• Column E: Recheck Status

• Column F: Last Run Time

• Column G: First Detected

Fill Column A with the URLs you want to monitor.

Fill Column B with the corresponding GTM IDs.

Setting Up Triggers

To make sure the script runs automatically, you’ll need to set up time-driven triggers:

1. Open Apps Script Editor:

• Go to your Google Sheet.

• Click on Extensions > Apps Script.

2. Add a Trigger:

• In the Apps Script Editor, click on the clock icon (Triggers) in the left sidebar.

• Click on + Add Trigger.

• Select checkGTMStatus from the function dropdown.

• Choose a time-based trigger (e.g., run every hour, day, etc.).

• Click Save.

This will ensure that the script runs at your specified intervals, checking for any issues and sending out alerts.

Conclusion

With this script in place, you’ll have a reliable system for monitoring your GTM tags and URL statuses. No more manual checks or missed errors. This solution keeps your marketing operations running smoothly, freeing you up to focus on what really matters.

Try it out, and let me know how it works for you. Happy monitoring!

Leave a Reply

Your email address will not be published. Required fields are marked *

Dan.marketing is the personal website of Daniel (Dan) Antony, a digital marketer passionate about business and technology
© Copyright 2023 - Oxygen - All Rights Reserved
envelopephone-handsetmap-marker
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram