LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Tuesday, September 26, 2023

Get Hidden Rows in Google Sheets with Google Apps Script


Introduction

Google Sheets allows you to hide rows and columns. 
Hidden rows are not displayed on the sheet and cannot be edited.

To get hidden rows, you can use Google Apps Script. 
Google Apps Script provides the isRowHiddenByUser() method in the Sheet class to determine if a specified row is hidden.


Source Code


Code.gs
function getHiddenRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var range = sheet.getRange("A1:A" + lastRow);
var values = range.getValues();
var hiddenRows = [];
for(var i = 0; i < values.length; i++) {
var row = i + 1
var hidden = sheet.isRowHiddenByUser(row);
if(hidden) {
hiddenRows.push(row);
}
}
Logger.log(["hiddenRows: ", hiddenRows]);
}



Reference

isRowHiddenByUser(rowPosition)

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...