Google Apps Script試行錯誤 Blog: Get Hidden Rows in Google Sheets with Google Apps Script

2023年9月26日火曜日

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]);
}