如何开发触发式脚本实现Sheet1删除关联学生姓名行时同步删除Sheet2对应行

Hey there! Let me walk you through exactly how to set up this automated row deletion for your Google Sheets—no prior coding experience required, I’ll break every step down clearly.

First, let’s recap what we’re building: When you delete a row with a student’s name from Sheet1, the corresponding row with that same student’s name in Sheet2 will automatically delete too. This fixes the data mess you saw when deleting Sheet1’s row 5 and leaving Sheet2’s row 6 hanging.

Step 1: Open the Google Apps Script Editor #

• Open your target Google Sheet.
• Click Extensions > Apps Script from the top menu bar—this opens a new tab with the script editor, where we’ll add our code.

Step 2: Paste the Custom Script #

Delete any default code in the editor, then paste this script:

function handleRowDeletion(e) {
  // Access your spreadsheet and target sheets
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = spreadsheet.getSheetByName("Sheet1"); // Update if your sheet has a different name
  const sheet2 = spreadsheet.getSheetByName("Sheet2"); // Same here—match your actual sheet name

  // Only run if the change happened in Sheet1 and it's a row deletion
  if (e.changeType !== "REMOVE_ROW" || e.source.getActiveSheet().getName() !== "Sheet1") {
    return;
  }

  // Get the student name from the deleted row (adjust column number below: A=1, B=2, etc.)
  const deletedRowNumber = e.range.getRow();
  const studentName = sheet1.getRange(deletedRowNumber, 1).getValue(); // Assumes name is in Column A of Sheet1

  // Exit if no name was found (prevents errors for empty rows)
  if (!studentName) return;

  // Search Sheet2 for the matching name and delete the row
  const sheet2AllData = sheet2.getDataRange().getValues();
  sheet2AllData.forEach((row, index) => {
    // Adjust the index below if name is in a different column (A=0, B=1, etc.)
    if (row[0] === studentName) {
      sheet2.deleteRow(index + 1); // +1 because sheet rows start at 1, array starts at 0
    }
  });
}

Step 3: Customize the Script for Your Sheet #

You need to tweak two parts to match your spreadsheet setup:

1. Sheet Names: If your sheets aren’t named "Sheet1" and "Sheet2", replace those strings in getSheetByName("Sheet1") and getSheetByName("Sheet2") with your actual sheet names (keep the quotes!).
2. Column Positions:
   • If the student name is in Column B of Sheet1, change sheet1.getRange(deletedRowNumber, 1) to sheet1.getRange(deletedRowNumber, 2) (since columns are numbered starting at 1).
   • If the name is in Column B of Sheet2, change row[0] to row[1] (array indices start at 0, so Column A = 0, Column B = 1, etc.).

Step 4: Set Up the Automatic Trigger #

To make the script run when a row is deleted, we need to set up an installable trigger (the basic onEdit trigger doesn’t catch row deletions reliably):

• In the Apps Script editor, click Edit > Current project's triggers from the top menu.
• Click the + Add Trigger button in the bottom-right corner.
• Configure the trigger with these settings:
  • Choose which function to run: Select handleRowDeletion
  • Choose which deployment to run: Head
  • Select event source: From spreadsheet
  • Select event type: On change
• Click Save, then follow the prompts to authorize the script (this is safe—your script only accesses this specific spreadsheet).

Step 5: Test It Out! #

Go back to your Google Sheet, delete a row in Sheet1 that has a student name present in Sheet2. You’ll see the matching row in Sheet2 get deleted automatically right away—no more messy leftover data!

Quick Troubleshooting #

• If nothing happens: Double-check your sheet names and column numbers match what you set in the script. Also confirm the trigger is set to "On change" (not "On edit").
• If multiple rows delete: This is normal if there are duplicate student names in Sheet2. If you only want to delete the first matching row, add break; right after sheet2.deleteRow(index + 1); to stop the script from searching further.

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