如何从Google Sheets特定列循环读取数据创建Google Calendar事件，并支持后续新增填充数据的处理

Let's walk through fixing your script step by step — first we'll squash the immediate errors causing it to fail, then set it up to automatically handle new rows as your team fills them in.

First: Fix the Critical Bugs in Your Existing Code #

Your script has a few small but breaking issues right off the bat:

• You defined sheet as the active sheet but then tried to call spreadsheet.getRange() — that should be sheet.getRange() instead.
• The endTime line has an empty index (shift[];) — since your range is B2:D1000, column B is index 0, C is 1, so endTime should be shift[1].
• You're looping through every row including blank ones, which is why it crashes when hitting empty rows.

Here's the corrected base script with these fixes:

function scheduleNotifications(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById("xyz@gmail.com");
  // Pull data from B2:D1000
  var notifications = sheet.getRange("B2:D1000").getValues();
  
  for (let x = 0; x < notifications.length; x++) {
    var shift = notifications[x];
    var startTime = shift[0];
    var endTime = shift[1]; // Fixed the index here
    var tag = shift[2];
    
    // Skip any row where the start time is empty (no data)
    if (!startTime) continue;
    
    // Only create the event if all required fields are filled
    if (startTime && endTime && tag) {
      eventCal.createEvent(tag, startTime, endTime);
    }
  }
}

Next: Prevent Duplicate Events #

Right now, if you run the script multiple times, it'll create duplicate events for the same rows. To fix this, add a "Processed" flag in an extra column (like column E) to track which rows have already been handled:

Updated code with duplicate protection:

function scheduleNotifications(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById("xyz@gmail.com");
  // Include column E in our range to track processed status
  var notifications = sheet.getRange("B2:E1000").getValues();
  
  for (let x = 0; x < notifications.length; x++) {
    var shift = notifications[x];
    var startTime = shift[0];
    var endTime = shift[1];
    var tag = shift[2];
    var isProcessed = shift[3]; // Column E holds our "Processed" flag
    
    // Skip blank rows or rows that are already marked as processed
    if (!startTime || isProcessed === "Yes") continue;
    
    if (startTime && endTime && tag) {
      eventCal.createEvent(tag, startTime, endTime);
      // Mark the row as processed in column E (row x+2 because we start at row 2)
      sheet.getRange(x + 2, 5).setValue("Yes");
    }
  }
}

Finally: Set Up Auto-Processing for New Rows #

To make the script run automatically when your team adds new data, you have two great options:

Option 1: On-Edit Trigger (Runs Immediately When a Row is Filled) #

1. Open your Google Sheet, go to Extensions > Apps Script.
2. In the script editor, click the clock icon (Triggers) in the left sidebar.
3. Click Add Trigger in the bottom right corner.
4. Configure the trigger like this:
   • Choose function to run: scheduleNotifications
   • Deployment: Head
   • Event source: From spreadsheet
   • Event type: On edit
5. Click Save and authorize the script when prompted (you'll need to allow access to your calendar and sheet).

Option 2: Time-Driven Trigger (Runs on a Schedule) #

If you don't need instant processing, set the script to check for new rows on a schedule (e.g., hourly):

1. In the Triggers menu, select Event source: Time-driven.
2. Pick a frequency that works for your team (e.g., Hour timer > Every hour).
3. Save the trigger.

Quick Tips for Your Team #

• Make sure everyone fills in all three columns (B, C, D) for each event — the script will skip incomplete rows.
• Tell your team not to edit column E (the "Processed" flag) — this keeps duplicates from happening.
• Test the script manually first by running scheduleNotifications in the script editor to make sure it works with your existing data before setting up triggers.

内容的提问来源于stack exchange，提问作者Ethan Bush