VoYD Gig Calendar
From Rabbi Blog
History
I had a project at work recently combining 10+ Google calendars into one primary calendar. It had to include a prefix on each event title so the event could be traced back to the proper department area and it had to add\update\delete depending on how the source calendar was updated.
I keep a spreadsheet going for band related things and one of them is a calendar tab, but it isn't very portable. The recent project got me to thinking about the spreadsheet: I have dates, venue name, some notes, the venue location, some approximation of the time we usually go live, and we have a FB page... let's have Google App Scripts update a calendar based on the spreadsheet. Let's have it do it hourly.
And it does!
https://rabbibob.com/voydcalendar/
The Script
function syncVoYDGigCalendar() {
// ==========================================
// CONFIGURATION & TOGGLES
// ==========================================
var VoYDDebug = 0; // 1 = Show full row-by-row debugging logs, 0 = Quiet summary mode
var CALENDAR_ID = "thatspecialstringooiuwr209348294ufrjaowiuf2@group.calendar.google.com";
var FACEBOOK_URL = "https://facebook.com/voydrocks";
// ==========================================
// INITIALIZATION & SCRIPT SETUP
// ==========================================
var calendar = CalendarApp.getCalendarById(CALENDAR_ID);
if (!calendar) {
Logger.log("❌ ERROR: Could not find or access calendar ID: " + CALENDAR_ID);
return;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
var sheet = null;
// Target specifically by GID 729321447
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetId() == 729321447) {
sheet = sheets[i];
break;
}
}
if (!sheet) {
sheet = ss.getSheetByName("Calendar") || ss.getSheets()[0];
}
var data = sheet.getDataRange().getValues();
var timeZone = Session.getScriptTimeZone();
if (VoYDDebug === 1) {
Logger.log("=========== STARTING SHEET SCAN ===========");
Logger.log("📊 Total rows found in sheet (including header): " + data.length);
}
// Fetch existing calendar events for 2024 to 2026
var startRange = new Date("2024-01-01T00:00:00");
var endRange = new Date("2036-12-31T23:59:59");
var calendarEvents = calendar.getEvents(startRange, endRange);
var calEventsMap = {};
for (var j = 0; j < calendarEvents.length; j++) {
var ev = calendarEvents[j];
var tagKey = ev.getTag("GIG_KEY");
if (!tagKey) {
var evDateStr = Utilities.formatDate(ev.getStartTime(), timeZone, "yyyy-MM-dd");
var evTitle = ev.getTitle();
var extractedVenue = "";
if (evTitle.indexOf("VoYD played ") === 0) extractedVenue = evTitle.replace("VoYD played ", "");
else if (evTitle.indexOf("VoYD plays ") === 0) extractedVenue = evTitle.replace("VoYD plays ", "");
else if (evTitle.indexOf("VoYD might play ") === 0) extractedVenue = evTitle.replace("VoYD might play ", "");
tagKey = extractedVenue ? (evDateStr + "_" + extractedVenue) : (evDateStr + "_" + ev.getLocation());
}
calEventsMap[tagKey] = ev;
}
var validSheetKeys = {};
var metrics = { scanned: 0, accepted: 0, rejected: 0 };
// ==========================================
// PROCESS SPREADSHEET ROWS
// ==========================================
for (var i = 1; i < data.length; i++) {
var row = data[i];
metrics.scanned++;
var filterValue = String(row[0] || "").toLowerCase().trim();
var dateRaw = row[1];
var venue = String(row[4] || "").trim();
var desc = String(row[5] || "").trim();
var humanRowNumber = i + 1;
// Safe date alignment and year compilation
var isDateValid = dateRaw && !isNaN(new Date(dateRaw).getTime());
var yearValue = 0;
if (isDateValid) {
var parsedDate = new Date(dateRaw);
yearValue = parsedDate.getFullYear();
} else {
var backupYear = Number(row[2]);
if (!isNaN(backupYear)) {
yearValue = backupYear;
}
}
var isFilterValid = ["played", "scheduled", "potential"].indexOf(filterValue) !== -1;
var isYearValid = [2024, 2025, 2026, 2027, 2028, 2029, 2030, 2031, 2032, 2033, 2034, 2035, 2036].indexOf(yearValue) !== -1;
if (!isFilterValid || !isYearValid || !isDateValid) {
metrics.rejected++;
// Conditional Debug Logging for Skips
if (VoYDDebug === 1) {
var reasons = [];
if (!isFilterValid) reasons.push("Filter value '" + (row[0] || "BLANK") + "' is not accepted");
if (!isDateValid) reasons.push("Invalid date format in Column B");
if (isDateValid && !isYearValid) reasons.push("Extracted year '" + yearValue + "' is outside 2024-2026");
Logger.log("❌ Row " + humanRowNumber + " SKIPPED. Reasons: " + reasons.join(" | "));
}
continue;
}
// Handled Accepted Rows
metrics.accepted++;
var dateObj = new Date(dateRaw);
var dateStr = Utilities.formatDate(dateObj, timeZone, "yyyy-MM-dd");
if (VoYDDebug === 1) {
Logger.log("✅ Row " + humanRowNumber + " ACCEPTED: " + filterValue.toUpperCase() + " -> " + venue + " (" + dateStr + ")");
}
var sheetKey = dateStr + "_" + venue;
validSheetKeys[sheetKey] = true;
var prefix = "";
if (filterValue === "played") prefix = "VoYD played ";
else if (filterValue === "scheduled") prefix = "VoYD plays ";
else if (filterValue === "potential") prefix = "VoYD might play ";
var title = prefix + venue;
var location = (venue && venue !== ".") ? venue : "";
var descriptionText = "See VoYD at " + venue + "!\n\n";
if (desc && desc !== ".") {
descriptionText += desc + "\n\n";
}
descriptionText += "For specific information, check out our FB page at " + FACEBOOK_URL + "\n\nHope to see you there!";
// Evaluate additions/modifications
if (calEventsMap[sheetKey]) {
var existingEvent = calEventsMap[sheetKey];
var hasChanged = false;
if (existingEvent.getTitle() !== title) hasChanged = true;
if (existingEvent.getLocation() !== location) hasChanged = true;
if (existingEvent.getDescription() !== descriptionText) hasChanged = true;
if (!existingEvent.isAllDayEvent()) hasChanged = true;
if (hasChanged) {
existingEvent.setTitle(title);
existingEvent.setLocation(location);
existingEvent.setDescription(descriptionText);
existingEvent.setTag("GIG_KEY", sheetKey);
existingEvent.setVisibility(CalendarApp.Visibility.PUBLIC);
Logger.log(" 🔄 Calendar Updated: " + title + " (" + dateStr + ")");
} else if (VoYDDebug === 1) {
Logger.log(" 💤 No changes needed for: " + title);
}
} else {
var newEvent = calendar.createAllDayEvent(title, dateObj, {
description: descriptionText,
location: location
});
newEvent.setTag("GIG_KEY", sheetKey);
newEvent.setVisibility(CalendarApp.Visibility.PUBLIC);
Logger.log(" ➕ Calendar Created: " + title + " (" + dateStr + ")");
}
}
if (VoYDDebug === 1) {
Logger.log("=========== STARTING CALENDAR CLEANUP ===========");
}
// ==========================================
// PURGE STALE CALENDAR EVENTS
// ==========================================
var deletedCount = 0;
for (var j = 0; j < calendarEvents.length; j++) {
var ev = calendarEvents[j];
var tagKey = ev.getTag("GIG_KEY");
if (!tagKey) {
var evDateStr = Utilities.formatDate(ev.getStartTime(), timeZone, "yyyy-MM-dd");
var evTitle = ev.getTitle();
var extractedVenue = "";
if (evTitle.indexOf("VoYD played ") === 0) extractedVenue = evTitle.replace("VoYD played ", "");
else if (evTitle.indexOf("VoYD plays ") === 0) extractedVenue = evTitle.replace("VoYD plays ", "");
else if (evTitle.indexOf("VoYD might play ") === 0) extractedVenue = evTitle.replace("VoYD might play ", "");
tagKey = extractedVenue ? (evDateStr + "_" + extractedVenue) : (evDateStr + "_" + ev.getLocation());
}
var isManagedEvent = ev.getTag("GIG_KEY") ||
ev.getTitle().indexOf("VoYD played ") === 0 ||
ev.getTitle().indexOf("VoYD plays ") === 0 ||
ev.getTitle().indexOf("VoYD might play ") === 0;
if (isManagedEvent && !validSheetKeys[tagKey]) {
Logger.log("🗑️ Calendar Deleted (no longer in sheet/valid): " + ev.getTitle() + " on " + Utilities.formatDate(ev.getStartTime(), timeZone, "yyyy-MM-dd"));
ev.deleteEvent();
deletedCount++;
}
}
// ==========================================
// FINAL METRICS SUMMARY (Always Displays)
// ==========================================
Logger.log("=========== FINAL PERFORMANCE SUMMARY ===========");
Logger.log("📋 Total Data Rows Scanned: " + metrics.scanned);
Logger.log("🎉 Total Rows Accepted: " + metrics.accepted);
Logger.log("🚫 Total Rows Filtered Out: " + metrics.rejected);
Logger.log("🗑️ Calendar Events Purged: " + deletedCount);
Logger.log("=================================================");
}