Google Sheets条件锁定/解锁单元格：基于列A值控制列B权限

Got it, let's get this working for you. Here's a step-by-step guide to set up column B to be locked by default, only unlocking when the corresponding row in column A is set to "Sad":

Step 1: Set Default Protection for Column B #

First, we'll lock the entire B column so it's restricted by default:

• Open your Google Sheet and click the column header for B to select the whole column.
• Right-click anywhere in the selected column and choose Protect range from the menu.
• In the right-side panel that pops up, click Set permissions.
• Select Restrict who can edit this range, then choose Only you (or specify specific users if multiple people need access). Hit Done to save this setup. Now column B is locked for everyone except the allowed users.

Step 2: Add a Custom Script to Handle Dynamic Unlocking #

Google Sheets doesn't have a built-in way to do conditional locking, so we'll use a simple Apps Script to listen for changes in column A and adjust permissions automatically:

• Click the top menu Extensions > Apps Script to open the script editor.
• Delete the default myFunction() code that's there, then paste this script:

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  const changedCell = e.range;
  
  // Only react to edits in column A (column 1), and skip the header row (adjust row number if your header isn't row 1)
  if (changedCell.getColumn() === 1 && changedCell.getRow() > 1) {
    const mood = changedCell.getValue().trim();
    const targetBcell = activeSheet.getRange(changedCell.getRow(), 2);
    
    // Get the protection object for column B
    const columnBProtection = activeSheet.getRange('B:B').getProtection();
    
    if (mood === 'Sad') {
      // Allow the current user to edit this specific B cell
      if (columnBProtection) {
        columnBProtection.addEditor(Session.getActiveUser());
        // If you need to let specific users edit, replace the line above with:
        // columnBProtection.addEditor('user@example.com');
      }
    } else if (mood === 'Happy') {
      // Revoke edit access for this B cell
      if (columnBProtection) {
        columnBProtection.removeEditor(Session.getActiveUser());
      }
    }
  }
}

• Save the script (click the floppy disk icon) and give it a name like "MoodBasedLocking".

How This Script Works #

• The onEdit() function is a simple trigger that runs automatically whenever someone edits the sheet.
• It checks if the edited cell is in column A (and not the header row).
• If the cell value is "Sad", it adds the current user's edit permission to the corresponding B cell. If it's "Happy", it removes that permission, locking the cell again.

Step 3: Test the Setup #

Go back to your sheet and test it out:

• Type "Sad" in any row of column A, then try editing the corresponding B cell—it should let you type in it now.
• Change that "Sad" to "Happy", then try editing the B cell again. You should get a message saying you don't have permission to edit it.

Quick Notes #

• If your header row isn't row 1, adjust the changedCell.getRow() > 1 part to match your header's row number (e.g., > 2 if header is row 2).
• If multiple users need access, replace Session.getActiveUser() with specific email addresses (you can add multiple editors by calling addEditor() multiple times).
• The first time you edit column A after setting up the script, you might need to authorize the script to run—just follow the on-screen prompts to grant the necessary permissions.

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