如何在Google Apps Script中实现循环？解决Sheet同步日历重复添加等问题

Hey there! Let's work through your two main issues and cover how to implement loops in Google Apps Script for this workflow.

Problem 1: Duplicate Calendar Events on Every Edit #

The root issue here is that your current script doesn't track which rows have already been synced to Calendar. Every time you edit Sheet1, it re-runs and creates a new event for the last row—even if that row was already processed.

Fix: Add a "Synced" Status Column #

1. In Sheet2, add a new column (let's use column E) with the header Synced.
2. We'll update this column to TRUE once an event is created for that row, so the script knows to skip it next time.

Problem 2: getLastRow() Returns Empty Formula Rows #

When you copy formulas down, getLastRow() counts those formula-containing rows even if they don't have actual data. We need a way to find the last row with real, non-empty values instead.

Fix: Custom Function to Find Last Data Row #

Add this helper function to your script to get the true last row with data:

function getLastDataRow(sheet) {
  const data = sheet.getDataRange().getValues();
  for (let i = data.length - 1; i >= 0; i--) {
    for (let j = 0; j < data[i].length; j++) {
      if (data[i][j].toString().trim() !== "") {
        return i + 1; // Convert array index (0-based) to sheet row number (1-based)
      }
    }
  }
  return 0; // No data found
}

Implementing Loops to Process Multiple Rows #

To handle multiple rows instead of just the last one, we'll use a for loop to iterate through each row that hasn't been synced yet.

Full Updated Script #

Here's the complete script that fixes both issues and uses a loop:

function syncSheetsToCalendar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Sheet2');
  const lastDataRow = getLastDataRow(sheet);
  const calendarId = 'YOUR_CALENDAR_ID_HERE'; // Replace with your actual Calendar ID
  const masterCal = CalendarApp.getCalendarById(calendarId);

  // Assume row 1 is headers—start looping from row 2
  for (let row = 2; row <= lastDataRow; row++) {
    // Check if this row has already been synced
    const isSynced = sheet.getRange(row, 5).getValue(); // Column E is "Synced"
    if (isSynced) continue; // Skip if already synced

    // Get data from the row (columns A-D)
    const [title, eventDesc, eventLoca, date] = sheet.getRange(row, 1, 1, 4).getValues()[0];

    // Skip if title or date is empty (required fields for Calendar events)
    if (!title || !date) continue;

    // Create the all-day event
    try {
      masterCal.createAllDayEvent(title, date, {
        location: eventLoca,
        description: eventDesc
      });
      // Mark the row as synced
      sheet.getRange(row, 5).setValue(true);
    } catch (error) {
      // Log any errors (optional but helpful for debugging)
      console.log(`Error syncing row ${row}: ${error.message}`);
    }
  }
}

// Helper function to find the last row with actual data
function getLastDataRow(sheet) {
  const data = sheet.getDataRange().getValues();
  for (let i = data.length - 1; i >= 0; i--) {
    for (let j = 0; j < data[i].length; j++) {
      if (data[i][j].toString().trim() !== "") {
        return i + 1;
      }
    }
  }
  return 0;
}

Key Changes Explained #

• Loop Implementation: The for loop runs from row 2 (after headers) to the last data row, processing each row individually.
• Duplicate Prevention: We check the "Synced" column (E) and skip rows that are already marked as synced. After creating an event, we set this column to TRUE.
• Accurate Row Detection: The getLastDataRow function ignores empty formula rows by checking for actual non-empty values.
• Error Handling: Added a try/catch block to log errors without breaking the entire sync if one row fails.

Setting Up Auto-Trigger (Optional) #

If you want this script to run automatically when Sheet1 is edited:

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Add a new trigger:
   • Choose syncSheetsToCalendar as the function.
   • Select "On edit" as the event source.
   • Select "From spreadsheet" as the event type.
   • Save the trigger.

内容的提问来源于stack exchange，提问作者Gergő Gulyás