Skip to content

Instantly share code, notes, and snippets.

@esquinas
Last active January 3, 2023 18:54
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save esquinas/bdc8de88a41f08cc128f00341857b0e5 to your computer and use it in GitHub Desktop.
Save esquinas/bdc8de88a41f08cc128f00341857b0e5 to your computer and use it in GitHub Desktop.
Check visibility of hidden rows and columns in Google Spreadsheet App Script
/*
USAGE EXAMPLES:
Is the tenth row hidden?
> isRowHidden(sheet.getRange('B10'))
Is column B hidden?
> isColumnHidden(sheet.getRange('B10'))
Is cell B10 visible? (not in a hidden row and/or column)
> !(isCellHidden(sheet.getRange('B10')))
*/
/**
* Takes the first row of a range and checks whether is hidden or not.
* Second parameter is an optional sheet. Defaults to the active sheet.
* @param {range} row
* @param {sheet} [sheet]
* @returns {boolean} True if row is hidden, false if it is visible.
*/
function isRowHidden (row, optionalSheet) {
var ss = SpreadsheetApp.getActive()
var sheet = optionalSheet || ss.getActiveSheet()
SpreadsheetApp.setActiveSheet(sheet)
var dup = ss.duplicateActiveSheet()
SpreadsheetApp.setActiveSheet(sheet)
var isHidden = false
var rowIndex = row.getRow()
var numRows = dup.getMaxRows()
if (numRows === 1) {
ss.deleteSheet(dup)
return false
}
try {
if (rowIndex === numRows ) {
dup.hideRows(1, numRows - 1)
} else if (rowIndex === 1) {
dup.hideRows(rowIndex + 1, numRows - 1)
} else {
dup.hideRows(1, rowIndex - 1)
dup.hideRows(rowIndex + 1, numRows - rowIndex)
}
isHidden = false
} catch (e) {
// Logger.log(e.message)
isHidden = true
} finally {
ss.deleteSheet(dup)
}
return isHidden
}
/**
* Takes the first column of a range and checks whether is hidden or not.
* Second parameter is an optional sheet. Defaults to the active sheet.
* @param {range} column
* @param {sheet} [sheet]
* @returns {boolean} True if column is hidden, false if it is visible.
*/
function isColumnHidden (col, optionalSheet) {
var ss = SpreadsheetApp.getActive()
var sheet = optionalSheet || ss.getActiveSheet()
SpreadsheetApp.setActiveSheet(sheet)
var dup = ss.duplicateActiveSheet()
SpreadsheetApp.setActiveSheet(sheet)
var isHidden = false
var colIndex = col.getColumn()
var numCols = dup.getMaxColumns()
if (numCols === 1) {
ss.deleteSheet(dup)
return false
}
try {
if (colIndex === numCols ) {
dup.hideColumns(1, numCols - 1)
} else if (colIndex === 1) {
dup.hideColumns(colIndex + 1, numCols - 1)
} else {
dup.hideColumns(1, colIndex - 1)
dup.hideColumns(colIndex + 1, numCols - colIndex)
}
isHidden = false
} catch (e) {
// Logger.log(e.message)
isHidden = true
} finally {
ss.deleteSheet(dup)
}
return isHidden
}
/**
* Takes the first cell of a range and checks whether is hidden or not.
* Second parameter is an optional sheet. Defaults to the active sheet.
* @param {range} cell
* @param {sheet} [sheet]
* @returns {boolean} True if cell is hidden, false if it is visible.
*/
function isCellHidden (cell, optionalSheet) {
var isHidden = isColumnHidden(cell, optionalSheet) || isRowHidden(cell, optionalSheet)
return isHidden
}
@keithworrell
Copy link

How this is different from sheet.isRowHiddenByUser()?

https://developers.google.com/apps-script/reference/spreadsheet/sheet#isRowHiddenByUser(Integer)

Note, confirmed through testing "by user" simply contrasts "by filter".

My guess is this was developed before the feature existed. Thank you so much for making this work, but it may be helpful to provide a link to the integrated feature and mark this project deprecated IF it is a duplicate.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment