请求开发学校日历用Google Sheets脚本：单元格联动更新值

Hey Bill, sounds like you need a script that reacts when entries in your bottom tables are marked "Done" to automate your school calendar workflow. Below is a customizable script that will handle this, plus steps to set it up and optional extras like syncing to Google Calendar.

Step 1: Open the Google Sheets Script Editor #

• Open your Google Sheet
• Click Extensions > Apps Script to launch the editor
• Delete any default placeholder code (like function myFunction() {})

Step 2: Paste the Custom Script #

Here’s a script that detects "Done" status updates in your target tables and performs configurable actions (tweak these to fit your exact needs):

function onEdit(e) {
  // Define your target tables: adjust these ranges to match your sheet's bottom tables
  const targetTables = [
    { 
      statusRange: "Sheet1!C10:C30", // Status column of first table
      updateColumn: "D", // Column to update when marked Done (e.g., Published date)
      archiveTab: "Archived Entries" // Optional tab to move completed rows to
    },
    { 
      statusRange: "Sheet1!I10:I30", // Status column of second table
      updateColumn: "J", 
      archiveTab: "Archived Entries"
    }
  ];

  const editedRange = e.range;
  const editedValue = e.value;

  // Check if the edited cell is in any target status column and set to "Done"
  for (const table of targetTables) {
    const statusColumnRange = SpreadsheetApp.getActiveSpreadsheet().getRange(table.statusRange);
    if (statusColumnRange.getA1Notation().includes(editedRange.getA1Notation()) && editedValue === "Done") {
      const row = editedRange.getRow();
      const sheet = editedRange.getSheet();

      // 1. Update a column with today's completion date
      const updateCell = sheet.getRange(table.updateColumn + row);
      updateCell.setValue(new Date());
      updateCell.setNumberFormat("yyyy-MM-dd");

      // 2. Optional: Move the completed row to an archive tab (create the tab first if needed)
      if (table.archiveTab) {
        const archiveSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(table.archiveTab);
        if (archiveSheet) {
          const rowData = sheet.getRange(row, 1, 1, sheet.getLastColumn()).getValues();
          archiveSheet.appendRow(rowData[0]);
          sheet.deleteRow(row);
        } else {
          console.log(`Archive tab "${table.archiveTab}" not found.`);
        }
      }

      // 3. Optional: Sync to Google Calendar (uncomment below and add the function)
      // syncToCalendar(row, sheet);
    }
  }
}

Customization Tips: #

• Adjust Target Ranges: Update statusRange values to match the exact status columns of your two bottom tables (e.g., if your first table's status is in column E from row 15 to 40, use "Sheet1!E15:E40").
• Update Column: Change updateColumn to the column letter where you want to record the completion date (or another relevant value).
• Archive Tab: Remove the archiveTab lines or set it to null if you don’t want to move completed rows.

Optional: Add Google Calendar Sync #

If you want to automatically publish "Done" entries to your public Google Calendar, add this function to the script and uncomment the syncToCalendar line in the onEdit function:

function syncToCalendar(row, sheet) {
  // Adjust these to match your table's columns (e.g., event title in column A, date in column B)
  const eventTitle = sheet.getRange("A" + row).getValue();
  const eventDate = sheet.getRange("B" + row).getValue();
  const eventDescription = sheet.getRange("C" + row).getValue();

  // Replace with your public Google Calendar ID (find it in Calendar settings > Integrate calendar)
  const calendarId = "your-calendar-id@group.calendar.google.com";
  const calendar = CalendarApp.getCalendarById(calendarId);

  if (eventTitle && eventDate) {
    // Create an all-day event (modify for timed events if needed)
    calendar.createAllDayEvent(eventTitle, eventDate, { description: eventDescription });
    console.log(`Successfully added event "${eventTitle}" to public calendar.`);
  } else {
    console.log("Missing event title or date—skipped calendar sync.");
  }
}

Step 3: Test the Script #

• Save the script (click the floppy disk icon) and name it something like "CalendarWorkflowAutomation".
• Return to your sheet, mark an entry in one of the bottom tables as "Done".
• Verify that the update column populates with today’s date, or the row moves to the archive tab (if enabled).

Troubleshooting #

• Permissions: If you get a permission error, authorize the script by following the prompts (Google may flag it as an "unverified app"—select Advanced > Go to [Script Name] to proceed safely).
• Range Mismatches: Double-check that your statusRange values exactly match the columns/rows in your sheet.
• Calendar Sync Issues: Ensure you’re using the correct Calendar ID and that your Google account has edit access to the public calendar.

内容的提问来源于stack exchange，提问作者Bill Warrick