Formula to ignore hidden columns

Question

I am trying to count visible columns in a spreadsheet with no luck. I've been trying using SUBTOTAL function but it's applied to hidden/visible rows only. I also tried working with CELL("width") function, but it doesn't return 0 when a cell is hidden

Is there any other option to ignore hidden columns in a count formula?

user_11225291 · Answer

You can definitely create your own custom function using Google Apps Script.

For example, the following function counts the number of visible columns in your active sheet:

function countVisibleColumns() {
  
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var n_cols = sheet.getMaxColumns();
var hidden_cols = []
var cnt = 0;
  
for (var i=1; i<=n_cols ; i++) {
  if ( sheet.isColumnHiddenByUser(i) ){
  continue;}
  else {cnt +=1} }
Logger.log(cnt)
return cnt;
}

You just need to click on Tools => Script editor and then copy the aforementioned code into a blank script. Then you can directly use the function as a formula in the google sheet like =countVisibleColumns(). See screenshot attached for more information.

Formula to ignore hidden columns

1 Answers