Мобильная версия- Совместные события для конкретных ежемесячных вкладок в моей электронной таблице (например, «Сентябрь - стадион Edge FY26 - События планирования»). < /Li>
Подробности Parse, такие как название события, номера билета и место на мероприятии. хронологически. Я из даже Сохраняю сценарий. Сообщение об ошибке:Код: Выделить всё
Syntax error: SyntaxError: Identifier 'firstDataRow' has already been declared line: 290 file: Untitled.gs
Подробная информация о проблеме:
ошибка возникает при сохранении: . Сценарий не может быть сохранен или выполнен из -за этой ошибки синтаксиса. Header_rows_to_skip + 1; - racible racope: Я понимаю, что «идентификатор уже был объявлен» обычно означает, что переменная объявляется дважды. Тем не менее, FirstDatarow предназначен для объявления один раз на лист и используется правильно после этого. Код включает в себя проверки и структурирование, чтобы избежать этого.
[*] Создал новый проект сценария приложений и удалил код. ниже) тщательно, обеспечивая, чтобы никаких строк не было пропущены или не включались дополнительные символы. Правильно.
Подтверждено соглашение о именовании листа правильное, и сценарий видит все соответствующие листы. (например, столбец X и Y - это ручные входы с раскрывающимися списками; столбец «Комната конференц -зала» также имеет выпадающие). Сценарий был обновлен, чтобы исключить ручные столбцы из записей и правильно проанализировать имена комнат для собраний. Среда сценария, которая вызывает это? Есть ли в самом редакторе сценария приложений тонкая проблема коррупции или состояния, которая предотвращает надлежащую компиляцию /сохранение этого кода? < /P>
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*(.*?)(?=(?:
|]*>||]*>||\\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 ---
< /code>
Заранее спасибо за любую информацию или помощь! < /p>
Подробнее здесь: https://stackoverflow.com/questions/796 ... ed-on-save
