Google Sheets Script Check If Cell Is Hidden

Google Sheets Script: Check if a Cell is Hidden

This article will guide you through creating a Google Apps Script function to efficiently determine whether a specific cell in your Google Sheet is hidden. This is useful for various automation tasks, such as conditional formatting, data manipulation based on visibility, or building custom user interfaces within Google Sheets. We'll cover different approaches and provide adaptable code snippets for your specific needs. Understanding how to check cell visibility opens up many possibilities for enhancing your spreadsheet automation.

Why Check for Hidden Cells?

Knowing if a cell is hidden is crucial for several advanced Google Sheets functionalities. For example:

• Conditional Formatting: Applying formatting only to visible cells.
• Data Validation: Ensuring data input only occurs in visible cells.
• Data Extraction: Collecting data selectively based on cell visibility.
• Custom Menus and User Interfaces: Dynamically updating UI elements based on hidden/visible cells.
• Report Generation: Excluding hidden data from reports.

Methods for Checking Cell Visibility

There are several ways to determine if a cell is hidden using Google Apps Script. The most straightforward method leverages the isRowHidden() and isColumnHidden() methods. However, these only check if the entire row or column is hidden, not individual cells. To check individual cell hiding (due to conditional formatting or manual hiding of individual cells within a visible row/column), we need a more robust approach:

Method 1: Checking Row and Column Visibility (Simpler, but less precise)

This method is simpler but only works if the cell is hidden because its entire row or column is hidden. It won't detect individual cell hiding within a visible row or column.

function isCellHiddenRowCol(sheetName, row, column) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  return sheet.isRowHidden(row) || sheet.isColumnHidden(column);
}

//Example usage:
let sheetName = "Sheet1";
let rowNum = 5;
let colNum = 2;
let isHidden = isCellHiddenRowCol(sheetName, rowNum, colNum);
Logger.log("Cell (" + rowNum + ", " + colNum + ") is hidden: " + isHidden);

Method 2: Checking Cell Protection (More Robust)

This method is more robust and accounts for cells hidden due to protection. It checks if the cell is protected and whether the protection prevents editing. However, it doesn't detect hiding through conditional formatting directly.

function isCellProtected(sheetName, row, column) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(row, column);
  const protection = range.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  if (protection.length > 0) {
    return protection[0].getDescription() !== null && !protection[0].canEdit(); //Check for protection and edit permission
  }
  return false;
}


//Example usage:
let sheetName = "Sheet1";
let rowNum = 5;
let colNum = 2;
let isProtected = isCellProtected(sheetName, rowNum, colNum);
Logger.log("Cell (" + rowNum + ", " + colNum + ") is protected: " + isProtected);

Method 3: Advanced Approach using getHiddenValues (Most Comprehensive)

This method is the most comprehensive, checking for all types of cell hiding: row/column hiding, protection, and conditional formatting hiding. It requires iterating through the hidden values of a range.

function isCellHiddenAdvanced(sheetName, row, column) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const range = sheet.getRange(row, column);
  const hiddenValues = range.getHiddenValues();
  return hiddenValues[0][0] === null; //A hidden cell will return null in getHiddenValues

}

//Example usage:
let sheetName = "Sheet1";
let rowNum = 5;
let colNum = 2;
let isHiddenAdvanced = isCellHiddenAdvanced(sheetName, rowNum, colNum);
Logger.log("Cell (" + rowNum + ", " + colNum + ") is hidden (advanced check): " + isHiddenAdvanced);

Remember to replace "Sheet1", rowNum, and colNum with your actual sheet name and cell coordinates. Choose the method that best suits your needs and the type of cell hiding you want to detect. Method 3 offers the most complete solution for detecting hidden cells regardless of the reason for hiding. Always test thoroughly to ensure accuracy.