如何从Google Sheets自动更新Google Calendar事件？及脚本修改需求

Let’s walk through adjusting your existing script to check for overlapping events and add the respondent as a guest instead of creating a new event. Here’s the updated code with clear explanations:

Full Updated Script #

// Load the Moment.js library once
var moment = Moment.load();

var GLOBAL = {
  calendarId: "YOUR_CALENDAR_ID_HERE", // Replace with your actual calendar ID
  formResponse: null // Stores form submission data on trigger
};

function onFormSubmit(e) {
  GLOBAL.formResponse = e.response;
  
  // Extract form data (adjust indices to match your form's question order)
  var startTimeStr = GLOBAL.formResponse.getItemResponses()[0].getResponse(); // First question = start time
  var endTimeStr = GLOBAL.formResponse.getItemResponses()[1].getResponse(); // Second question = end time
  var eventTitle = GLOBAL.formResponse.getItemResponses()[2].getResponse(); // Third question = event title
  var respondentEmail = GLOBAL.formResponse.getRespondentEmail(); // Get submitter's email (requires form to collect emails)
  
  // Convert time strings to Date objects using Moment.js
  var startTime = moment(startTimeStr).toDate();
  var endTime = moment(endTimeStr).toDate();
  
  // Access your target calendar
  var calendar = CalendarApp.getCalendarById(GLOBAL.calendarId);
  
  // Check for existing events in the submitted time window
  var existingEvents = calendar.getEvents(startTime, endTime);
  
  if (existingEvents.length > 0) {
    // If overlapping events exist, add the respondent as a guest to the first match
    // (You can extend this to check for exact title matches if needed)
    var targetEvent = existingEvents[0];
    
    try {
      targetEvent.addGuest(respondentEmail);
      console.log(`Successfully added ${respondentEmail} as guest to event: ${targetEvent.getTitle()}`);
    } catch (error) {
      console.error(`Failed to add guest: ${error.message}`);
    }
  } else {
    // No overlapping events, create a new one with the respondent as a guest
    try {
      var newEvent = calendar.createEvent(eventTitle, startTime, endTime, {
        guests: respondentEmail,
        sendInvites: true // Set to false if you don't want auto-invites
      });
      console.log(`Created new event: ${newEvent.getTitle()}`);
    } catch (error) {
      console.error(`Failed to create event: ${error.message}`);
    }
  }
}

Key Adjustments Explained #

• Time Slot Check: The calendar.getEvents(startTime, endTime) line fetches all events overlapping with the submitted time range. If any exist, we skip creating a new event and add the guest instead.
• Guest Addition: targetEvent.addGuest(respondentEmail) adds the submitter’s email to the existing event. The try/catch block handles common errors like invalid emails or permission issues.
• Email Collection: getRespondentEmail() works only if your form has "Collect email addresses" enabled under Settings > Responses. If you use a form question to capture emails, replace this line with the appropriate getResponse() call for that question (e.g., getItemResponses()[3].getResponse()).
• Custom Matching: If you want to match events by title instead of just time overlap, add a loop to check existing events:

  let targetEvent = null;
  for (let event of existingEvents) {
    if (event.getTitle() === eventTitle) {
      targetEvent = event;
      break;
    }
  }
  if (targetEvent) { /* add guest */ }
  

Setup Notes #

1. Replace YOUR_CALENDAR_ID_HERE with your calendar’s ID (found in Calendar Settings > Integrate calendar).
2. Adjust the indices in getItemResponses() to match your form’s question order (arrays are zero-indexed, so the first question is [0]).
3. Authorize the script when running for the first time: you’ll need to grant access to your Calendar and Form data.

内容的提问来源于stack exchange，提问作者chris bernay