Google Sheets – Show All Hidden Rows and Columns on Open

google sheetsgoogle-apps-script

I want to un-hide all the hidden rows and columns when I open the spreadsheet.

How can I do that for all sheets?

Best Answer

You can accomplish that with this piece of code.

Code

function onOpen() {
  // set up spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet(), sheets = ss.getSheets();

  for(var i = 0, iLen = sheets.length; i < iLen; i++) {
    // get sheet
    var sh = sheets[i];

    // unhide columns
    var rCols = sh.getRange("1:1");
    sh.unhideColumn(rCols);

    // unhide rows
    var rRows = sh.getRange("A:A");
    sh.unhideRow(rRows);
  }
}

Explained

The onOpen trigger will be activated upon opening of the spreadsheet. The script will find the outer bound of the sheet, for both columns and rows. First the columns are shown, followed by the rows. This code works for all sheets available.

Example

I've created an example file for you: unhide rows and columns upon opening

References

Edit

h/t : changed the code according to the suggestions AdamL made:

Two small possible performance gains:

  1. var sheets = ss.getSheets(); for (var i = 0, length = sheets.length; i < length; i++); var sh = sheets[i];
  2. can you do getRange("1:1")? (avoiding getMaxColumns call)
Related Topic