如何用Google App Script阻止特定条件下的Google表格行删除？

Absolutely! You can absolutely implement this row deletion restriction using Google Apps Script. Here's a practical, step-by-step solution tailored to your needs:

Core Idea #

We'll listen for row deletion events in your target sheet. When a row is deleted, we'll first restore it, check if the first cell's value exists in the first column of your reference sheet. If it does, we'll keep the row intact and alert the user; if not, we'll proceed with the deletion.

Working Script #

First, open your Google Sheet, go to Extensions > Apps Script to open the script editor, then replace the default code with this:

function onChange(e) {
  // Only respond to row deletion events
  if (e.changeType !== 'REMOVE_ROW') return;

  const ss = e.source;
  // Replace with your target sheet name (the one you want to protect)
  const targetSheet = ss.getSheetByName('YourTargetSheet');
  // Replace with your reference sheet name (the one storing values to check against)
  const referenceSheet = ss.getSheetByName('YourReferenceSheet');

  // Undo the deletion temporarily to access the row's original value
  ss.undo();

  const deletedRow = e.range.getRow();
  // Get the value from the first cell of the row that was attempted to be deleted
  const cellValue = targetSheet.getRange(deletedRow, 1).getValue();

  // Allow deletion if the cell is empty
  if (!cellValue) {
    targetSheet.deleteRow(deletedRow);
    return;
  }

  // Check if the value exists in the first column of the reference sheet
  // Using TextFinder for better performance, especially with large datasets
  const textFinder = referenceSheet.createTextFinder(cellValue)
    .matchEntireCell(true) // Ensures we match exact values only
    .findNext();

  const isValuePresent = textFinder !== null;

  if (!isValuePresent) {
    // Proceed with deletion if the value isn't in the reference sheet
    targetSheet.deleteRow(deletedRow);
  } else {
    // Alert the user and keep the row
    SpreadsheetApp.getUi().alert(`Cannot delete this row: The value "${cellValue}" exists in the reference sheet.`);
  }
}

Key Setup Steps #

• Replace Sheet Names: Make sure to swap YourTargetSheet and YourReferenceSheet with the actual names of your sheets (case-sensitive!).
• Authorize the Script: When you first run the script (click the play button), you'll need to grant it permissions to access your spreadsheet—follow the prompts to complete authorization.
• Set Up a Trigger: Simple triggers might not always capture events reliably, so set up an installable trigger:
  1. In the script editor, click Edit > Current project's triggers.
  2. Click Add trigger in the bottom right.
  3. Configure it as follows:
     • Choose function: onChange
     • Choose deployment type: Head
     • Select event source: From spreadsheet
     • Select event type: Change
  4. Click Save.

Optimization Tips #

• For large reference sheets, using TextFinder (as shown in the script) is much faster than fetching all values with getValues() and checking with includes().
• If you need to ignore case sensitivity, you can add .matchCase(false) to the TextFinder chain.

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