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