How do I determine if a column is hidden in Google

问题:

I'm trying to write a function for Google Spreadsheet that locates the first and last column of a group. It then hides the group, or shows it if it's already hidden.

However, I haven't been able to find a way to determine whether a column is hidden or not. I haven't been able to find anything anything on Google's Class Sheet page https://developers.google.com/apps-script/reference/spreadsheet/sheet, and I haven't found an equivalent to Excel's .hidden

getColumnWidth(column) returns the unhidden column width even when it's hidden.

Here's my code:

function hideShowColumns(startCol, endCol) { 
  //endCol is one column past the last data set that should be hidden
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transposed Data");
  //import the data from the Column Headers
  var colHeaderData = sheet.getRange(1, 1, 2, sheet.getMaxColumns()).getValues(); 
  var startColNum = 0;
  var endColNum = 0;

  // This section searches for the column names in the header rows and returns their column number
  for (var i =0; i < 2; ++i) {
    for (var j = 0; j < colHeaderData[0].length; ++j) {
      if (colHeaderData[i][j] == startCol) 
        startColNum = j;
      if (colHeaderData[i][j] == endCol) 
        endColNum = j;
    }
  }

  //This is the wrong command getColumnWidth doesn't change if column is hidden
  if (sheet.getColumnWidth(startColNum + 1) != 0) {
    sheet.hideColumns(startColNum + 2, endColNum - startColNum - 1);
    Logger.log(sheet.getColumnWidth(startColNum + 2));
    return;
  }

  //This is the wrong command getColumnWidth doesn't change if column is hidden
  if (sheet.getColumnWidth(startColNum + 1) == 0) { 
    for (var j = startColNum + 1; j < endColNum - 1; ++j) {
      sheet.unhideColumn(j);
      Logger.log(sheet.getColumnWidth(startColNum + 2));     
    }
    return;
  }
}

Thanks for the help!

回答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

回答2:

Sorry, I also could not find a way and stumbled upon this stack overflow. But I might as well share this script somewhere.

I use the first column as a place holder for a -- or || value to tell if its hidden or shown. (Not the best but its all I could seem to find)

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name: "Toggle Rows",
    functionName: "toggleRows"
  },{
    name: "Hide Rows",
    functionName: "hideRows"
  },{
    name: "Show Rows",
    functionName: "showRows"
  }];
  sheet.addMenu("Script", entries);
};

function toggleRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  var r = sheet.getRange(1, 1, maxRows+1);
  var values = r.getValues();
  var hideRanges = [];
  var showRanges = [];

  var toggleValue = null;
  var startRow = null;

  var tmp;
  var len = values.length;
  var i;
  for (i = 0; i < len; i++) {
    tmp = values[i][0];
    if (startRow === null && (tmp === '--' || tmp === '||')) {
      startRow = i + 1;
      toggleValue = tmp;
    } else if (startRow !== null && tmp !== toggleValue) {
      if (toggleValue === '--') {
        hideRanges.push([startRow, (i + 1) - startRow]);
      } else {
        showRanges.push([startRow, (i + 1) - startRow])
      }

      if (tmp === '--' || tmp === '||') {
        startRow = i + 1;
        toggleValue = tmp;
      } else {
        startRow = null;
      }
    }
  }

  var customRange = null;
  var range = null;

  i = hideRanges.length;
  while (i--) {
    customRange = hideRanges[i];
    range = sheet.getRange(customRange[0], 1, customRange[1]);
    range.setValue('||');
    sheet.hideRows(customRange[0], customRange[1]);
  }

  i = showRanges.length;
  while (i--) {
    customRange = showRanges[i];
    range = sheet.getRange(customRange[0], 1, customRange[1]);
    range.setValue('--');
    sheet.showRows(customRange[0], customRange[1]);
  }

};

function hideRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  var r = sheet.getRange(1, 1, maxRows+1);
  var values = r.getValues();
  var startRow = null;

  var tmp;
  var len = values.length;
  var i;
  for (i = 0; i < len; i++) {
    tmp = values[i][0];
    if (startRow === null && (tmp === '--' || tmp === '||')) {
      startRow = i + 1;
    } else if (startRow !== null && (tmp !== '--' && tmp !== '||')) {
      var numRows = (i + 1) - startRow;
      sheet.getRange(startRow, 1, numRows).setValue('||');
      sheet.hideRows(startRow, numRows);
      startRow = null;
    }
  }

};

function showRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var maxRows = sheet.getMaxRows();

  var r = sheet.getRange(1, 1, maxRows+1);
  var values = r.getValues();
  var startRow = null;

  var tmp;
  var len = values.length;
  var i;
  for (i = 0; i < len; i++) {
    tmp = values[i][0];
    if (startRow === null && (tmp === '--' || tmp === '||')) {
      startRow = i + 1;
    } else if (startRow !== null && (tmp !== '--' && tmp !== '||')) {
      var numRows = (i + 1) - startRow;
      sheet.getRange(startRow, 1, numRows).setValue('--');
      sheet.showRows(startRow, numRows);
      startRow = null;
    }
  }

};

See here for any updates: https://gist.github.com/LiamKarlMitchell/81cef19a530261c4af93

回答3:

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

回答4:

As of 2018, Google hasn't added row/col visibility methods to the Sheets API yet. Now, late 2018, they created an API for this.

That's sad but I found another way:

When a row is the last one visible, you cannot hide it (same with columns). Google Spreadsheet throws an error and shows you a message. So, when a script function hides every row except the one we want to check, if it fails, our row was hidden. If the run is successful, it means that our row was visible.

Please be advised that this work-around is too hackerish to be used in a performance sensitive script.

Usage examples:

var sheet = SpreadsheetApp.getActive().getActiveSheet()
// Is the tenth row hidden?
isRowHidden(sheet.getRange('B10')) 

// Is column B hidden?
isColumnHidden(sheet.getRange('B10'))

// Is cell B10 visible? (not in a hidden row and/or column)
!(isCellHidden(sheet.getRange('B10')))

Code

/**
 * Takes the first row of a range and checks whether is hidden or not.
 * Second parameter is an optional sheet. Defaults to the active sheet.
 * @param {range} row
 * @param {sheet} [sheet]
 * @returns {boolean} True if row is hidden, false if it is visible.
 */
function isRowHidden (row, optionalSheet) {
  var ss = SpreadsheetApp.getActive()
  var sheet = optionalSheet || ss.getActiveSheet()
  SpreadsheetApp.setActiveSheet(sheet)
  var dup = ss.duplicateActiveSheet()
  SpreadsheetApp.setActiveSheet(sheet)
  var isHidden = false
  var rowIndex = row.getRow()
  var numRows = dup.getMaxRows()

  if (numRows === 1) {
    ss.deleteSheet(dup)
    return false
  }

  try {
    if (rowIndex === numRows ) {
      dup.hideRows(1, numRows - 1)
    } else if (rowIndex === 1) {
      dup.hideRows(rowIndex + 1, numRows - 1)
    } else {
      dup.hideRows(1, rowIndex - 1)
      dup.hideRows(rowIndex + 1, numRows - rowIndex)
    }
    isHidden = false
  } catch (e) {
    isHidden = true    
  } finally {
    ss.deleteSheet(dup)
  }

  return isHidden
}

/**
 * Takes the first column of a range and checks whether is hidden or not.
 * Second parameter is an optional sheet. Defaults to the active sheet.
 * @param {range} column
 * @param {sheet} [sheet]
 * @returns {boolean} True if column is hidden, false if it is visible.
 */
function isColumnHidden (col, optionalSheet) {
  var ss = SpreadsheetApp.getActive()
  var sheet = optionalSheet || ss.getActiveSheet()
  SpreadsheetApp.setActiveSheet(sheet)
  var dup = ss.duplicateActiveSheet()
  SpreadsheetApp.setActiveSheet(sheet)
  var isHidden = false
  var colIndex = col.getColumn()
  var numCols = dup.getMaxColumns()

  if (numCols === 1) {
    ss.deleteSheet(dup)
    return false
  }

  try {
    if (colIndex === numCols ) {
      dup.hideColumns(1, numCols - 1)
    } else if (colIndex === 1) {
      dup.hideColumns(colIndex + 1, numCols - 1)
    } else {
      dup.hideColumns(1, colIndex - 1)
      dup.hideColumns(colIndex + 1, numCols - colIndex)
    }
    isHidden = false
  } catch (e) {
    isHidden = true    
  } finally {
    ss.deleteSheet(dup)
  }

  return isHidden
}

/**
 * Takes the first cell of a range and checks whether is hidden or not.
 * Second parameter is an optional sheet. Defaults to the active sheet.
 * @param {range} cell
 * @param {sheet} [sheet]
 * @returns {boolean} True if cell is hidden, false if it is visible.
 */
function isCellHidden (cell, optionalSheet) {
  var isHidden = isColumnHidden(cell, optionalSheet) || isRowHidden(cell, optionalSheet)
  return isHidden
}

PS: Code follows JS Standard Style.

回答5:

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