Google Sheets宏报错求助：实现多工作表B4单元格1与2互转

Key Issues in Your Original Code #

1. Undefined range Variable: You call range.getValue() but never assign the range to a variable. Activating a range (activate()) only selects it in the UI—it doesn't create a range variable for your code to use.
2. Redundant Code: You repeat the exact same logic three times for each sheet, which is inefficient and hard to maintain.
3. Logic Mismatch: Your code toggles between 0 and 1, but your requirement is to toggle between 1 and 2.

Corrected Macro Code #

function ToggleB4Values() {
  // Get the active spreadsheet once
  const spreadsheet = SpreadsheetApp.getActive();
  // List of target sheet names
  const sheetNames = ['Credentials', 'Credentials-fullsales', 'Credentials-reservations'];
  
  // Loop through each sheet in the list
  sheetNames.forEach(sheetName => {
    // Retrieve the sheet (skip if it doesn't exist)
    const sheet = spreadsheet.getSheetByName(sheetName);
    if (!sheet) return;
    
    // Get the B4 range and its current value
    const b4Range = sheet.getRange('B4');
    const currentValue = b4Range.getValue();
    
    // Toggle between 1 and 2
    if (currentValue === 1) {
      b4Range.setValue(2);
    } else if (currentValue === 2) {
      b4Range.setValue(1);
    }
    // Note: If B4 contains values other than 1/2, nothing will happen
  });
}

What This Code Does #

• Single Spreadsheet Reference: Fetches the active spreadsheet once instead of repeating the call.
• Loop Through Sheets: Uses an array of sheet names to iterate over each target sheet, eliminating redundant code.
• Explicit Range Variable: Assigns the B4 range to b4Range so you can safely call getValue() and setValue() on it.
• Correct Toggle Logic: Switches values between 1 and 2 as requested.
• Error Prevention: Checks if each sheet exists before processing to avoid runtime errors.

How to Use #

1. Open your Google Sheet.
2. Go to Extensions > Apps Script.
3. Replace your existing code with the code above.
4. Save the script (click the floppy disk icon) and name it something descriptive like ToggleB4Values.
5. Run the function (click the play button) and authorize the script when prompted.

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