Last active
January 3, 2023 18:54
-
-
Save esquinas/bdc8de88a41f08cc128f00341857b0e5 to your computer and use it in GitHub Desktop.
Check visibility of hidden rows and columns in Google Spreadsheet App Script
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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 | |
} | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.