How do I determine if a column is hidden in Google Spreadsheet using scripts?

The new (as of 2018) api for this is: isColumnHiddenByUser(columnPosition)

Returns whether the given column is hidden by the user.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// Columns start at 1
Logger.log(sheet.isColumnHiddenByUser(1));

Unfortunately there is no Google Apps Script method that will return whether a column or row is hidden or not. You might like to star the issue opened for it, as a way of a. receiving updates about the issue, and b. "signing the petition", so to speak.

https://code.google.com/p/google-apps-script-issues/issues/detail?id=195&q=hidden%20column&colspec=Stars%20Opened%20ID%20Type%20Status%20Summary%20Component%20Owner


A workaround. Create 2 rows. The first must always have a value and the second has a set of formulas. These 2 rows look like this:

  |           A             |           B             |            C            |
---------------------------------------------------------------------------------
1 |                       1 |                       1 |                       1 |
2 | =NOT(SUBTOTAL(103, A1)) | =NOT(SUBTOTAL(103, B1)) | =NOT(SUBTOTAL(103, C1)) |

SUBTOTAL returns a subtotal using a specified aggregation function. The first argument 103 defines the type of function used for aggregation. The second argument is the range to apply the function to.

  • 3 means COUNTA and counts the number of values in the range
  • +100 means ignore hidden cells in the range.

The result of SUBTOTAL with a range of 1 cell will be 0 when the cell is hidden and 1 when the cell is shown. NOT inverts it.

Now your can read the second row with your script to know if a column is hidden.

Here's the transposed question and answer: https://stackoverflow.com/a/27846202/1385429