VoYD Gig Calendar

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 Apps Script 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("=================================================");

}