如何使GSheets列隐藏脚本仅作用于指定工作表(A/B/C/D/E)？

Got it, let's build a robust solution that centralizes your column visibility control using the "Tracker" sheet, while covering all your requirements for sheets A-E, fixed hidden columns, and hiding the control row (row 4) in target sheets.

Core Approach #

• Use the Tracker sheet's row 4 as the single source of truth for which columns to hide (marked with x)
• Iterate over your target sheets (A, B, C, D, E)
• For each sheet:
  1. Reset all columns to visible first
  2. Hide columns marked with x in Tracker's row 4
  3. Force-hide the fixed helper columns (EM:ET)
  4. Hide row 4 in the target sheet so users don't see it

Full Google Apps Script Code #

function onOpen() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 1. Get the control data from Tracker sheet's row 4
  const trackerSheet = ss.getSheetByName('Tracker');
  if (!trackerSheet) {
    SpreadsheetApp.getUi().alert('Tracker sheet not found!');
    return;
  }
  const controlRowValues = trackerSheet.getRange('4:4').getValues()[0];
  const totalColumns = trackerSheet.getMaxColumns();

  // 2. Define target sheets and fixed helper columns to hide
  const targetSheetNames = ['A', 'B', 'C', 'D', 'E'];
  const fixedHiddenColumns = { start: 142, end: 149 }; // EM = column 142, ET = column 149 (verify with =COLUMN(EM1) in your sheet)

  // 3. Process each target sheet
  targetSheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return; // Skip if sheet doesn't exist

    // Reset all columns to visible first
    sheet.showColumns(1, sheet.getMaxColumns());

    // Hide columns marked with 'x' in Tracker's row 4
    controlRowValues.forEach((cellValue, index) => {
      const columnNumber = index + 1;
      // Skip redundant action if column is already in fixed hidden range
      if (cellValue === 'x' && !(columnNumber >= fixedHiddenColumns.start && columnNumber <= fixedHiddenColumns.end)) {
        sheet.hideColumns(columnNumber);
      }
    });

    // Force-hide the fixed helper columns (EM:ET)
    sheet.hideColumns(fixedHiddenColumns.start, fixedHiddenColumns.end - fixedHiddenColumns.start + 1);

    // Hide row 4 in the target sheet
    sheet.hideRows(4);
  });
}

Key Code Explanations #

• Tracker Validation: Checks if the Tracker sheet exists to avoid runtime errors if it's renamed or deleted
• Control Row Data: Pulls the entire row 4 from Tracker to use as the single visibility rule set
• Fixed Columns: Uses numeric column indices (EM = 142, ET = 149) because Apps Script operates with numbers instead of letters. You can confirm these values using the =COLUMN(EM1) formula in your sheet if needed.
• Redundancy Avoidance: Skips hiding fixed helper columns again if they're already marked with x in Tracker, saving minor processing time
• Error Resilience: Skips target sheets that don't exist, so the script won't break if a sheet is temporarily removed

Optional Optimizations #

• Manual Refresh Menu: Let users refresh visibility without reloading the sheet by adding a custom menu:

  function onOpen() {
    const ui = SpreadsheetApp.getUi();
    ui.createMenu('Visibility Controls')
      .addItem('Refresh Column Visibility', 'refreshColumnVisibility')
      .addToUi();
  }
  
  // Move core logic here for manual triggering
  function refreshColumnVisibility() {
    // Paste all the core code from the original onOpen() function here
  }
  

• Case Insensitivity: Accept both x and X by changing the check to cellValue?.toString().toLowerCase() === 'x'
• Batch Operations: For extremely large sheets, group columns to hide into batches to reduce API calls, though this isn't necessary for your specified column range

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