Google Apps Script：用变量作为getCalendarById的ID报错，求解决方案

Hey there! Let's fix that TypeError you're hitting when setting the CurrentEmail in your Google Sheets script. The error tells us that cal is null—meaning CalendarApp.getCalendarById(CurrentEmail) couldn't find a valid calendar matching the email you entered. Here's how to diagnose and fix this, plus some improvements to make your script more robust:

1. Why getCalendarById Returns Null (and How to Fix It) #

This error pops up for one of three common reasons:

• Invalid Calendar ID/Email: Personal calendars use your email as the ID, but shared calendars have a unique ID (usually something like abc123@group.calendar.google.com or a long alphanumeric string). You can find this ID in the calendar's settings under the "Integrate calendar" section.
• Missing Permissions: If you're trying to access another account's calendar, make sure that calendar is shared with the Google account you're using to run the script (the one opening the spreadsheet), and you've been granted at least "View events" access.
• Bad Input in B3: Double-check the GettingEventsSettings sheet's B3 cell—remove any extra spaces, line breaks, or empty values. The script reads the cell content exactly as it is, so clean input matters.

2. Updated Script with Error Handling & Validation #

I've revised your script to add user-friendly error messages, input checks, and better behavior. This way, you'll know exactly what's wrong if something breaks:

function getEvents() {
  try {
    // Get references to both sheets
    const eventsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GettingEvents");
    const settingsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GettingEventsSettings");
    
    // Check if both sheets exist
    if (!eventsSheet || !settingsSheet) {
      throw new Error("Oops! Either 'GettingEvents' or 'GettingEventsSettings' sheet is missing.");
    }

    // Pull and clean settings values
    const dateFrom = settingsSheet.getRange("B1").getValue();
    const dateTo = settingsSheet.getRange("B2").getValue();
    const calendarId = settingsSheet.getRange("B3").getValue().trim(); // Remove extra spaces

    // Validate date inputs
    if (!dateFrom || !dateTo || dateFrom >= dateTo) {
      throw new Error("Please enter valid dates where Date From is before Date To in the settings sheet.");
    }

    // Validate calendar ID/email
    if (!calendarId) {
      throw new Error("Please enter a valid calendar ID or email in cell B3 of the settings sheet.");
    }

    // Fetch the calendar
    const cal = CalendarApp.getCalendarById(calendarId);
    if (!cal) {
      throw new Error(`Could not find a calendar with ID: ${calendarId}. Check if it's shared with your account or verify the ID is correct.`);
    }

    // Get events and clear existing data (only rows with content)
    const events = cal.getEvents(dateFrom, dateTo);
    const lastRow = eventsSheet.getLastRow();
    if (lastRow >= 2) {
      eventsSheet.getRange(2, 1, lastRow - 1, 5).clearContent();
    }

    // Write events to the sheet
    for (let i = 0; i < events.length; i++) {
      const event = events[i];
      eventsSheet.getRange(i + 2, 1).setValue(event.getTitle());
      eventsSheet.getRange(i + 2, 2).setValue(event.getStartTime());
      eventsSheet.getRange(i + 2, 3).setValue(event.getEndTime());
      eventsSheet.getRange(i + 2, 4).setValue(event.getLocation());
      eventsSheet.getRange(i + 2, 5).setValue(event.getDescription());
    }

    // Let the user know it worked
    SpreadsheetApp.getUi().alert(`Success! Synced ${events.length} events to the GettingEvents sheet.`);
  } catch (error) {
    // Show a clear error message
    SpreadsheetApp.getUi().alert(`Error: ${error.message}`);
    console.error(error); // Log details for debugging
  }
}

Key improvements here:

• try/catch block to catch issues and show plain-language alerts instead of confusing script errors
• .trim() on the calendar ID to eliminate accidental extra spaces
• Checks for missing sheets, invalid dates, and empty calendar IDs
• More efficient clearing of old data (only clears rows that have content)
• Success alert to confirm sync is complete

3. Make the Settings Sheet User-Friendly #

To help users avoid mistakes, add these touches to the GettingEventsSettings sheet:

• Add labels in column A:
  • A1: Date From
  • A2: Date To
  • A3: Calendar ID/Email
• Set up data validation:
  • For B1/B2: Enforce date format (Go to Data > Data validation, select "Date" as the criteria)
  • For B3: Enforce email format (criteria = "Email")
• Add a note below B3 explaining that shared calendars need their unique Calendar ID (not just an email)

内容的提问来源于stack exchange，提问作者Mauricio Alvarez