View source for 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 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==
<code>
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("=================================================");
}
</code>