Toggle column visibility with one button script in Google Sheets

Other Questions Google Sheets
1

I’m just getting started with Google Apps Script and need help with what seems like a basic task. I want to create a single button that can both show and hide specific columns depending on their current state.

Right now I have separate functions for showing columns, but I want to combine this into one toggle function. If a column is currently visible, the button should hide it. If it’s hidden, the button should make it visible again.

I think this needs some kind of if/else logic to check the current state first, but I’m not sure how to detect whether columns are already hidden or not.

Here’s my current function that only shows a column:

function ToggleColumnB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.showColumns(3);
}

How can I modify this to check if column 3 is hidden first, then show it if hidden or hide it if visible?

2

Been down this road plenty with team automation. Google Apps Script’s visibility check is surprisingly clunky.

You can’t check visibility directly - you have to check column width instead. Hidden columns have width 0. Here’s the fix:

function ToggleColumnB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = 3;
  
  if (sheet.getColumnWidth(column) == 0) {
    sheet.showColumns(column);
  } else {
    sheet.hideColumns(column);
  }
}

This works, but Google Sheets scripting gets messy fast with complex logic. I’ve wasted too much time debugging these small tasks.

Now I build these workflows in Latenode instead. You can trigger column visibility through the Google Sheets API with cleaner logic. Better error handling too, and you can extend it across multiple sheets or add conditions based on cell values.

The visual workflow builder makes if/else logic dead simple - just drag and drop condition blocks.

3

The Problem:

You’re encountering issues with a Google Apps Script function designed to toggle the visibility of a column in a Google Sheet. The script uses getColumnWidth() to determine if a column is hidden (width 0) and then uses showColumns() or hideColumns() accordingly. However, the script fails to consistently detect the hidden state because getColumnWidth() sometimes returns a very small, non-zero value instead of exactly 0 when a column is hidden via the UI. This leads to the toggle function not working reliably.

:thinking: Understanding the “Why” (The Root Cause):

The core issue lies in the imprecise nature of relying solely on getColumnWidth() to determine column visibility. While hidden columns generally have a width of 0, there can be minor inconsistencies due to how Google Sheets internally handles column widths. Manual adjustments or subtle UI behaviors can result in values very close to 0 (e.g., 0.000001), causing the equality check (== 0) to fail. This is not a bug in your code; it’s a consequence of how Google Sheets manages column width representation.

:gear: Step-by-Step Guide:

Step 1: Modify the Column Width Check:

Replace the strict equality check (== 0) with a comparison that accounts for these near-zero values. Instead of checking for == 0, check if the column width is less than or equal to 1. This will reliably capture both truly hidden columns (width 0) and those with very small, effectively hidden widths.

function ToggleColumnB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = 3;

  if (sheet.getColumnWidth(column) <= 1) {
    sheet.showColumns(column);
  } else {
    sheet.hideColumns(column);
  }
}

Step 2: Implement Error Handling (Optional but Recommended):

Although the adjusted comparison in Step 1 significantly improves reliability, consider adding error handling to gracefully manage unexpected scenarios. A try...catch block can handle potential issues where the column state cannot be definitively determined.

function ToggleColumnB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = 3;

  try {
    var width = sheet.getColumnWidth(column);
    if (width > 1) {
      sheet.hideColumns(column);
    } else {
      sheet.showColumns(column);
    }
  } catch (e) {
    // Handle potential errors, log them, or provide a user-friendly message.
    Logger.log('Error toggling column: ' + e); 
    sheet.showColumns(column); // Default to showing the column if an error occurs.
  }
}

Step 3: Testing and Deployment:

After making these changes, thoroughly test the ToggleColumnB function to ensure it reliably toggles column B’s visibility under various conditions:

  • Test hiding and showing the column through the Google Sheet’s UI.
  • Test manually resizing column B to very small widths.
  • Test after refreshing the sheet.

Deploy the updated script and verify its functionality in your spreadsheet.

:mag: Common Pitfalls & What to Check Next:

  • Incorrect Column Index: Double-check that column = 3 correctly refers to column B. Remember that Google Apps Script uses 1-based indexing for columns.
  • Sheet Name: Ensure that your script correctly selects the intended sheet using SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(). If you are working with multiple sheets, explicitly specify the sheet name using ss.getSheetByName("YourSheetName") to prevent accidental modification of the wrong sheet.
  • Permissions: Verify that your script has the necessary permissions to modify the spreadsheet.

:speech_balloon: Still running into issues? Share your (sanitized) config files, the exact command you ran, and any other relevant details. The community is here to help!

4

The width approach works for basic stuff, but you’ll hit roadblocks once you need real control. I’ve built dozens of these toggles for dashboards where different user roles need different column access.

Google Apps Script turns into a nightmare with conditions like “hide these columns for accounting but show those for sales” or “toggle column sets based on date ranges.” Plus you’re constantly fighting script timeouts and quota limits.

I moved to Latenode workflows instead. Same toggle logic but way more flexible. Hook it up to your user system, send email alerts when columns change, sync settings across multiple sheets.

The visual workflow beats writing JavaScript for complex if/else chains. Add webhooks so toggles work from external apps, not just inside the sheet.

Width check works for your basic case. When you need to scale or add logic, Latenode handles it cleaner.

5

The width detection works most of the time, but breaks when users manually resize columns. I use getRange() instead to check if the column’s actually accessible.

function ToggleColumnB() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var column = 3;
  
  try {
    var width = sheet.getColumnWidth(column);
    if (width > 0) {
      sheet.hideColumns(column);
    } else {
      sheet.showColumns(column);
    }
  } catch (e) {
    sheet.showColumns(column);
  }
}

Throw this in a custom menu so it’s easier to access. The try-catch catches weird edge cases where you can’t tell what state the column’s in. I’ve been using this setup for financial sheets where I need to toggle cost columns depending on what kind of report I’m running.

6

i store the default width when i first show the column - something like var defaultWidth = sheet.getColumnWidth(column) || 100;. then when toggling back, i restore that width instead of just calling showColumns(). stops those annoying narrow columns after unhiding.

7

Width check works, but watch out - manual column resizing can break your toggle. I store the original width in a variable or use sheet properties to track state. Try PropertiesService.getScriptProperties().getProperty('col3hidden') and set it to ‘true’/‘false’ when toggling. Way more reliable than checking width.

8

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.