I’m working on a Google Apps Script to synchronize events from my Google Calendar to a Google Spreadsheet. The script is designed to:
- Fetch events for specific monthly tabs in my spreadsheet (e.g., “SEPTEMBER – FY26 EDGE Stadium – Planning Events”).
- Parse details like event name, attendees, ticket number, and location from the event description.
- Combine new events with existing data on the sheet and sort them chronologically.
- Place a “Last Sync” timestamp on a dedicated “Sync Log” sheet.
- Critically, it’s configured to handle multiple header rows (4 rows) and specific data validation rules for meeting rooms.
I am consistently encountering a SyntaxError that prevents me from even saving the script. The error message is:
Syntax error: SyntaxError: Identifier 'firstDataRow' has already been declared line: 290 file: Untitled.gs
Key details about the problem:
- Error occurs on saving: The script cannot be saved or run due to this syntax error.
- Specific line: The error points to
let firstDataRow = HEADER_ROWS_TO_SKIP + 1;
- Variable scope: I understand that “Identifier has already been declared” usually means a variable is declared twice. However,
firstDataRow is intended to be declared once per sheet iteration and used correctly thereafter. The code includes checks and structuring to avoid this.
Troubleshooting steps I’ve already tried:
- Created a brand new Apps Script project and deleted all default code.
- Copied and pasted the entire script (provided below) carefully, ensuring no lines were missed or extra characters included.
- Attempted saving in different web browsers.
- Cleared my browser’s cache and cookies.
- Confirmed my Google Calendar ID and Spreadsheet ID are correct.
- Confirmed the sheet naming convention is correct and the script sees all relevant sheets.
- Confirmed
HEADER_ROWS_TO_SKIP is correctly set to 4 for my sheet’s structure (data starts on Row 5).
- The spreadsheet has data validation rules on certain columns (e.g., Column X and Y are manual inputs with dropdowns; the “Meeting Room” column also has dropdowns). The script has been updated to exclude manual columns from writes and to correctly parse meeting room names.
My question is:
Why would this specific SyntaxError (Identifier ‘firstDataRow’ has already been declared) occur upon saving the script, given the variable scoping appears correct, and what could be unique to my Apps Script environment that causes this? Is there a subtle corruption or state issue in the Apps Script editor itself that prevents proper compilation/saving of this code?
const now = new Date();
// --- UPDATED DATE RANGE LOGIC ---
// Set start date to September 1st of the current year
const startOfYear = new Date(now.getFullYear(), 8, 1); // Month is 0-indexed, so 8 is September
// If the current date is already past September 1st, start from 'now'. Otherwise, start from September 1st.
const startDate = (now > startOfYear) ? now : startOfYear;
// Set the end date to one year from the calculated start date
const futureDate = new Date(startDate);
futureDate.setFullYear(startDate.getFullYear() + 1);
const allEvents = calendar.getEvents(startDate, futureDate);
// --- END UPDATED DATE RANGE LOGIC ---
// --- NEW DEBUG LOG: Log ALL Sheet Names Found in the Spreadsheet ---
Logger.log('--- All Sheet Names Found in the Spreadsheet ---');
sheets.forEach(sheet => {
Logger.log(` Sheet Name: "${sheet.getName()}"`);
});
Logger.log('------------------------------------------------');
// --- END NEW DEBUG LOG ---
// --- DEBUG LOG: Check if your November event is even retrieved from the calendar ---
Logger.log('--- All Events Retrieved from Calendar (Title, Date, and Month Index) ---');
allEvents.forEach(event => {
Logger.log(` ${event.getTitle()} on ${event.getStartTime().toDateString()} (Month Index: ${event.getStartTime().getMonth()})`);
});
Logger.log('----------------------------------------------------------');
// --- END DEBUG LOG ---
sheets.forEach(sheet => {
// Log for all sheets to debug if NOVEMBER tab is being skipped
Logger.log(`Checking sheet: "${sheet.getName()}"`);
if (!monthTabPattern.test(sheet.getName())) {
Logger.log(`Skipping sheet (name mismatch): "${sheet.getName()}"`);
return;
}
const sheetMonthName = sheet.getName().split(" ")[0].toUpperCase();
Logger.log(`Processing sheet: "${sheet.getName()}" (Sheet Month Name: ${sheetMonthName})`);
// --- CRITICAL FIX FOR READING EXISTING TICKETS ON EMPTY SHEETS ---
let existingTickets = new Set(); // Initialize as empty Set
let lastRowWithContent = sheet.getLastRow(); // Get the last row with any content
let firstDataRow = HEADER_ROWS_TO_SKIP + 1; // This is the first row where actual data should be (e.g., Row 5)
// Only attempt to read existing tickets if there are actual data rows in the sheet
if (lastRowWithContent >= firstDataRow) {
const numRowsToRead = lastRowWithContent - firstDataRow + 1; // Calculate number of rows to read
// Read existing tickets from the Ticket Number column
const ticketsData = sheet.getRange(firstDataRow, COLUMN_INDICES['Ticket Number'] + 1, numRowsToRead, 1).getValues();
ticketsData.flat().filter(Boolean).forEach(ticket => existingTickets.add(ticket));
}
// --- END CRITICAL FIX ---
const eventsForMonth = allEvents.filter(e => {
const eventMonthName = Utilities.formatDate(e.getStartTime(), "CET", "MMMM").toUpperCase(); // Get full month name
Logger.log(` FILTER CHECK: Event '${e.getTitle()}' (Cal Month Name: ${eventMonthName}) vs Sheet '${sheet.getName()}' (Sheet Month Name: ${sheetMonthName}). Match: ${eventMonthName === sheetMonthName}`);
return eventMonthName === sheetMonthName;
});
Logger.log(`Found ${eventsForMonth.length} events for "${sheet.getName()}"`);
const newRows = [];
eventsForMonth.forEach(event => {
const title = event.getTitle();
const description = event.getDescription() || "";
const location = event.getLocation() || "";
Logger.log(`--- Processing Event: ${title} on ${event.getStartTime().toDateString()} ---`);
const start = event.getStartTime();
const end = event.getEndTime();
// Helper to extract specific data from the description using regex
const extract = (label) => {
// FIXED REGEX: Ensured the lookahead group is properly terminated.
const regex = new RegExp(`${label}:\s*(.*?)(?=(?:<br>|<p[^>]*>|<\/p>|<a[^>]*>|<\/a>|\n)|$)`, "i");
const match = description.match(regex);
const extractedValue = match ? match[1].trim().replace(/<[^>]*>/g, '') : ""; // Strip all HTML tags
Logger.log(` Extracted '${label}': '${extractedValue}'`);
return extractedValue;
};
// --- Data Extraction ---
let eventNameFromDesc = extract("Name of event");
const actualEventName = eventNameFromDesc || title; // Use extracted name, fallback to event title
const rawPax = extract("Number of attendees").replace(/[^0-9]/g, ""); // Extract numbers only
const pax = rawPax ? `${rawPax} PAX` : ''; // Format as 'XX PAX'
Logger.log(` Raw PAX: '${rawPax}', Formatted PAX: '${pax}'`);
// SF Event Host: Now relies solely on extraction from description
const sfEventHost = extract("Who the IBP is");
Logger.log(` SF Event Host: '${sfEventHost}'`);
const ticket = extract("Event number");
Logger.log(` Ticket Number: '${ticket}'`);
// --- Type Column Logic (Column C) ---
let eventType = '';
if (title.toUpperCase().includes("SIC")) {
eventType = "SIC";
} else {
const typeMatch = description.match(/Whether it is internal/ external:s*(Internal|External)/i);
eventType = typeMatch ? typeMatch[1] : ""; // Will be "Internal" or "External" based on description
}
Logger.log(` Event Type (derived): '${eventType}'`);
// --- Meeting Room Logic (Column F) ---
let finalRoomName = "";
const normalizedCalendarLocation = location.toLowerCase(); // For matching keys in LOCATION_MAPPING
// 1. Check for exact direct matches in the LOCATION_MAPPING (case-insensitive)
for (const calendarLocMapKey in LOCATION_MAPPING) {
if (normalizedCalendarLocation.includes(calendarLocMapKey)) {
finalRoomName = LOCATION_MAPPING[calendarLocMapKey];
break;
}
}
// 2. If not found in direct mapping, try to match against the dropdown options pattern
if (!finalRoomName) {
// Clean up calendar location for regex matching (remove prefixes like "Amsterdam - Edge Stadium-")
let cleanedLocationForMatch = location.replace(/Amsterdam - Edge Stadium-/, '').replace(/ [Google Meet]/i, '').trim();
// Also normalize "(XX)" to "(XX pax)" if that's how dropdowns are
cleanedLocationForMatch = cleanedLocationForMatch.replace(/((d+))$/, '($1 pax)');
// Escape special characters for regex from dropdown options
// Handle both '&' and '&' as they might appear.
const escapedDropdownOptions = DROPDOWN_ROOM_OPTIONS.map(option =>
option.replace(/[.*+?^${}()|[]\]/g, '\$&') // Escape common regex special chars
.replace(/&/g, '(?:&|&)') // Handle both & and &
);
// This regex tries to find any of the dropdown options anywhere in the cleaned location
const roomPattern = new RegExp(`(${escapedDropdownOptions.join('|')})`, 'i');
const match = cleanedLocationForMatch.match(roomPattern);
if (match && match[1]) {
finalRoomName = match[1].replace(/&/g, '&'); // Use the captured group and convert & back to &
} else {
Logger.log(` Warning: No regex match found for cleaned location: '${cleanedLocationForMatch}'`);
}
}
Logger.log(` Final Meeting room name: '${finalRoomName}'`);
// --- Duplicate Check ---
Logger.log(` Checking duplicate: Ticket '${ticket}', exists in sheet: ${existingTickets.has(ticket)}`);
if (!ticket || existingTickets.has(ticket)) {
if (!ticket) Logger.log(`Skipping event "${title}" because no ticket number was found.`);
else Logger.log(`Skipping event "${title}" with ticket ${ticket} because it already exists.`);
return; // This 'return' skips the event
}
Logger.log(` Event "${title}" passed duplicate check.`);
// --- Construct Row Data ---
// Initialize with empty strings for columns A to W (TOTAL_COLUMNS: 23)
const rowData = new Array(COLUMN_INDICES.TOTAL_COLUMNS).fill('');
rowData[COLUMN_INDICES['Dayporter Assigned']] = ""; // Manual
rowData[COLUMN_INDICES['Date of event']] = Utilities.formatDate(start, "CET", "dd/MM/yyyy");
rowData[COLUMN_INDICES['Type']] = eventType;
rowData[COLUMN_INDICES['Name of Event']] = actualEventName;
rowData[COLUMN_INDICES['SF Event Host']] = sfEventHost;
rowData[COLUMN_INDICES['Meeting room']] = finalRoomName; // Use the cleaned and validated room name
rowData[COLUMN_INDICES['PAX']] = pax;
rowData[COLUMN_INDICES['Start Time (CET)']] = Utilities.formatDate(start, "CET", "HH:mm");
rowData[COLUMN_INDICES['Finish Time (CET)']] = Utilities.formatDate(end, "CET", "HH:mm");
rowData[COLUMN_INDICES['Ticket Number']] = ticket;
// Populate other fields if data is extractable or has defaults
// These fields are filled up to Column W (index 22). Columns X and Y are left untouched.
rowData[COLUMN_INDICES['Slack Channel']] = "";
rowData[COLUMN_INDICES['SIC Guest Registration Done?']] = "";
rowData[COLUMN_INDICES['Ground Floor Check-In']] = "";
// Basic check: if 'Catering:' label exists, assume 'Yes', otherwise 'No'.
rowData[COLUMN_INDICES['Catering YES/NO']] = extract("Catering") ? "Yes" : "No";
rowData[COLUMN_INDICES['Catering What, Caterer & Time Status: request/pending/confirmed']] = extract("Catering"); // Full catering info
rowData[COLUMN_INDICES['Menu']] = "";
rowData[COLUMN_INDICES['Furniture Set Up']] = "";
rowData[COLUMN_INDICES['Comments']] = description; // Full description can go here, or more parsed details
rowData[COLUMN_INDICES['AV Requirements']] = extract("AV support");
rowData[COLUMN_INDICES['AV Ticket']] = "";
rowData[COLUMN_INDICES['Alcohol (SVP Approval) YES/NO']]= "";
rowData[COLUMN_INDICES['Minors']]= "";
rowData[COLUMN_INDICES['OOH Event']]= "";
// 'Extra security requested?': Column X (index 23) - Manual, not managed by script
// 'VP / C-Level': Column Y (index 24) - Manual, not managed by script
newRows.push(rowData);
Logger.log(` Event "${title}" added to newRows.`); // Log if event is added
});
// --- DEBUG LOG: Check newRows.length before the 'if' condition ---
Logger.log(`DEBUG: newRows.length for "${sheet.getName()}": ${newRows.length}`);
// --- END NEW DEBUG LOG ---
// --- CRITICAL LOGIC FIX: Explicitly handle empty newRows array ---
if (newRows.length === 0) {
Logger.log(`Skipping write operation for "${sheet.getName()}" because newRows is empty.`);
// This explicit 'return' prevents the error "The number of rows in the range must be at least 1."
// when newRows is truly empty for a sheet (e.g., April, July in your logs)
return; // Exit processing for this sheet if no new rows to add/sort.
}
// --- END CRITICAL LOGIC FIX ---
// This block will now only execute if newRows > 0
let firstDataRow = HEADER_ROWS_TO_SKIP + 1;
let lastUsedRowInSheet = sheet.getLastRow();
// Determine the actual range of existing data to read.
let numExistingDataRows = 0;
if (lastUsedRowInSheet >= firstDataRow) {
numExistingDataRows = lastUsedRowInSheet - firstDataRow + 1;
}
let allData = [];
if (numExistingDataRows > 0) {
// Read existing values within the defined data columns (A to W)
allData = sheet.getRange(firstDataRow, 1, numExistingDataRows, COLUMN_INDICES.TOTAL_COLUMNS).getValues();
}
const combinedData = allData.concat(newRows);
const sorted = combinedData.sort((a, b) => {
// Get the date value from the appropriate column
let aDateValue = a[COLUMN_INDICES['Date of event']];
let bDateValue = b[COLUMN_INDICES['Date of event']];
let aDate = new Date(0); // Default to epoch for empty/invalid dates
let bDate = new Date(0);
// Attempt to parse/use aDateValue
if (aDateValue instanceof Date) {
aDate = aDateValue;
} else if (typeof aDateValue === 'string' && aDateValue.trim() !== '') {
try {
aDate = Utilities.parseDate(aDateValue.trim(), "CET", "dd/MM/yyyy");
} catch (e) {
Logger.log(`Warning: Could not parse date for A: '${aDateValue}' Error: ${e.message}`);
}
}
if (bDateValue instanceof Date) {
bDate = bDateValue;
} else if (typeof bDateValue === 'string' && bDateValue.trim() !== '') {
try {
bDate = Utilities.parseDate(bDateValue.trim(), "CET", "dd/MM/yyyy");
} catch (e) {
Logger.log(`Warning: Could not parse date for B: '${bDateValue}' Error: ${e.message}`);
}
}
return aDate.getTime() - bDate.getTime();
});
const newNumberOfRowsToWrite = sorted.length;
const newNumberOfColsToWrite = COLUMN_INDICES.TOTAL_COLUMNS;
// Define the target range for writing the sorted data (A to W)
const targetRange = sheet.getRange(firstDataRow, 1, newNumberOfRowsToWrite, newNumberOfColsToWrite);
targetRange.setValues(sorted); // This overwrites the target area
// IMPORTANT: If the number of sorted rows is *less* than the previously used rows,
// clear the excess rows below the new data (in columns A to W)
if (newNumberOfRowsToWrite < numExistingDataRows) {
const clearStartRow = firstDataRow + newNumberOfRowsToWrite;
const rowsToClearCount = numExistingDataRows - newNumberOfRowsToWrite;
// Clear only the cells in the columns the script manages (A to W)
sheet.getRange(clearStartRow, 1, rowsToClearCount, newNumberOfColsToWrite).clearContent();
Logger.log(`Cleared ${rowsToClearCount} excess rows for tab: "${sheet.getName()}"`);
}
Logger.log(`${newRows.length} new events added and sheet sorted for tab: "${sheet.getName()}"`);
});
// --- FINAL FIX for "Last Sync" placement ---
// Place "Last Sync" on the "Sync Log" sheet, cell A1
const syncLogSheet = spreadsheet.getSheetByName("Sync Log");
if (syncLogSheet) {
syncLogSheet.getRange('A1').setValue("Last Sync: " + Utilities.formatDate(now, "CET", "dd/MM/yyyy HH:mm:ss"));
Logger.log('Last Sync timestamp updated on "Sync Log" sheet, A1.');
} else {
Logger.log('Warning: "Sync Log" sheet not found. Could not update Last Sync timestamp.');
}
// --- END FINAL FIX ---
Thank you in advance for any insights or assistance!
