如何在Google Sheets同一份电子表格的所有工作表中复用相同的Apps Script脚本？

Hey there! Let's fix this step by step since you're new to Apps Script—no worries, this is a common gotcha when starting out.

What's going wrong with your current code? #

1. Your hideRow2() function is hardcoded to always target the first worksheet (ss.getSheets()[0]). Even if you activate other sheets in your loop, the function still only operates on that first sheet.
2. Your everysheet() function has syntax errors: you’re reassigning the allsheets variable incorrectly, and the getSheetName() call is placed in the wrong spot, breaking the loop logic entirely.

Step 1: Refactor your core function to work with any sheet #

First, let’s rewrite hideRow2() to accept a sheet parameter. This way, you can pass any worksheet to it, instead of locking it to the first one:

function hideRow2(sheet) { 
  // Use the sheet passed into the function instead of hardcoding the first sheet
  var dropDown = sheet.getRange('B140:G140').getValue(); 
  
  if(dropDown === "NIE") { 
    sheet.hideRows(141, 5); 
  } else if(dropDown === "TAK" || dropDown === "") { 
    sheet.showRows(140, 6); 
  }
  // We can remove the empty else block since it doesn't do anything
}

Step 2: Write a proper function to apply logic to all sheets #

Now let’s fix the loop function to iterate through every sheet (except "Arkusz4") and pass each one to our refactored hideRow2():

function applyToAllSheets() { 
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var allSheets = ss.getSheets(); 

  allSheets.forEach(function(sheet){ 
    // Skip the "Arkusz4" worksheet
    if(sheet.getSheetName() !== "Arkusz4"){ 
      hideRow2(sheet); // Pass the current sheet to our function
    } 
  });
}

Step 3: Make the script work for new sheets automatically #

To ensure your logic applies to newly created sheets too, we’ll use a trigger. Here are two options:

Option A: Auto-run when the spreadsheet is edited

Add this global onEdit function—it will trigger automatically whenever any cell is edited, and only run your logic if the edit is in the dropdown range you care about:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  
  // Skip the "Arkusz4" sheet
  if(sheet.getSheetName() === "Arkusz4") return;
  
  var editedRange = e.range;
  // Check if the edited cell is within B140:G140
  if(editedRange.rowStart === 140 && editedRange.columnStart >= 2 && editedRange.columnEnd <=7) {
    hideRow2(sheet); // Trigger the hide/show logic
  }
}

Option B: Auto-run when a new sheet is added

If you want to apply the logic immediately when a new sheet is created:

1. Open the Apps Script editor and click the alarm icon (Triggers) on the left sidebar.
2. Click Add Trigger.
3. Configure it like this:
   • Choose function to run: applyToAllSheets
   • Choose deployment type: Head
   • Event source: From spreadsheet
   • Event type: Change
4. Save and grant the necessary permissions.

How to test this: #

1. Run applyToAllSheets() manually once to apply the logic to all existing sheets.
2. Edit the dropdown in B140:G140 on any sheet (except Arkusz4)—the rows should hide/show automatically.
3. Create a new sheet, add the same dropdown structure, and edit it—your logic will kick in right away.

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