|
|
| Line 1: |
Line 1: |
| ==History==
| | #Redirect[[VoYD Gig Calendar]] |
| 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>
| |