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.
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.
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");
}
}
}
First, create a new Google Sheet and name it something relevant, like “GTM Monitoring”. Then set up your sheet like this:
• 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.
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.
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!