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

Question

I am trying to write a function for Google Spreadsheet that defines the first and last columns of a group. He then hides the group or shows it if it is already hidden.

However, I could not find a way to determine if the column is hidden or not. I was unable to find anything on the google class page https://developers.google.com/apps-script/reference/spreadsheet/sheet and I did not find the Excel.hidden equivalent

getColumnWidth (column) returns the width of the hidden column, even if it is hidden.

Here is 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!

Answer 1

Unfortunately, there is no Google Apps Script method that returns whether a column or row is hidden or not. You might like to open a problem for him, as a way. receiving updates about the problem and b. so to speak, sign the petition.

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

Answer 2

Workaround. Create 2 lines. The first should always matter, and the second is a set of formulas. These 2 lines 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 an intermediate total using the specified aggregation function. The first argument 103 determines the type of function used for aggregation. The second argument is the range to apply the function.

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

A SUBTOTAL result 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 you can read the second row with a script to find out if the column is hidden.

Here's the transposed question and answer: stack overflow

Answer 3

Sorry, I also could not find a way and came across this stack overflow. But I could also share this script somewhere.

I use the first column as a place holder for a - or || value to determine whether it is hidden or not. (Not the best, but all I could 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 any updates here: https://gist.github.com/LiamKarlMitchell/81cef19a530261c4af93

Answer 4

As of 2018, Google has not yet added row / column visibility methods to the Sheets API. Now, at the end of 2018, they created an API for this.

This is sad, but I found another way:

When a row is the last visible, you cannot hide it (same with columns). Google Spreadsheet throws an error and shows you a message. Thus, when the script function hides every line except the one we want to check, in case of failure, our line is hidden. If the launch is successful, it means our row was visible.

Note that this workaround is too hacky to use in a performance sensitive script.

Examples of using:

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

The 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: Codex complies with JS standard.

Answer 5

New (as of 2018) API for this: isColumnHiddenByUser (columnPosition)

Returns whether this column is hidden by the user.

 var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; // Columns start at 1 Logger.log(sheet.isColumnHiddenByUser(1));