Hide columns in Google Sheets based on cell value with App Scripts

Google Sheets doesn’t have build functionality to hide columns based on the specific value in the cell. However, I will show you how to achieve it with a simple Google Apps Script you can implement within a few minutes.

Hide columns in Google Sheets based on cell value #

Open your Google Sheet and click Extensions in the top menu.
Select Apps Scripts from the dropdown menu.
In the script editor, paste the following code:

function hideColumnsWithYesValues() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  // Change this to the last row you want to check
  var lastRow = 100;
  // Set the value to hide columns for
  // Use the empty quote "", to hide the column
  // when at least one cell is empty
  var valueToHide = "hide this column";

  for (var col = 1; col <= lastColumn; col++) {
    var range = sheet.getRange(1, col, lastRow, 1);
    var values = range.getValues();
    var hasValue = false;
    
    for (var row = 0; row < values.length; row++) {
      if (values[row][0] == valueToHide) {
        hasValue = true;
        break;
      }
    }
    
    if (hasValue) {
      sheet.hideColumns(col);
    }
  }
}

Change the valueToHide variable in the code to the value based on what you want to hide columns. So instead of "hide this column", type your value in quotation marks. If you want to hide the column when a cell is empty, use the empty quote "". In the lastRow type how many rows the script should look for the value.
Click on the Run button in the toolbar to run the script. You may need to authorize the script to run. The first time you run the script, Google will ask you to authorize the script to access your Google account. Follow the prompts to authorize the script.
Once the script is authorized, you can run it again by clicking on the "Run" button in the toolbar. This time, the script will execute immediately without asking for authorization.
The script will scan the specified column for the specified value and hide any column where that value is found. You can rerun the script at any time to unhide the hidden columns.

Functions used in this Google Apps Script:

SpreadsheetApp.getActiveSpreadsheet(): This function retrieves the currently active spreadsheet in the Google Sheets application.

getActiveSheet(): This function retrieves the currently active sheet within the active spreadsheet.

getLastColumn(): This function retrieves the index of the last column containing data within the active sheet.

getRange(row, column, numRows, numColumns): This function retrieves a range of cells within the active sheet, starting at the specified row and column and spanning the specified number of rows and columns.

Hide rows based on cell value #

You can use a similar script to hide rows instead of columns in the spreadsheet.

function hideRowsWithYesValues() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var valueToHide = "hide";

  for (var row = 1; row <= lastRow; row++) {
    var range = sheet.getRange(row, 1, 1, lastColumn);
    var values = range.getValues()[0];
    var hasValue = false;

    for (var col = 0; col < values.length; col++) {
      if (values[col] == valueToHide) {
        hasValue = true;
        break;
      }
    }

    if (hasValue) {
      sheet.hideRows(row);
    }
  }
}

getLastRow() is used instead of getLastColumn() to get the last row of the sheet.
The loop now iterates over rows instead of columns.
getRange(row, 1, 1, lastColumn) is used to get a range for each row.
getValues()[0] is used to get a 1D array of values for each row.
The inner loop now iterates over columns instead of rows.
sheet.hideRows(row) is used to hide rows instead of sheet.hideColumns(col).

Learn JavaScript for Google Apps Scripts by hiding columns in Sheets #

If you just want the script to hide cells just use the script above. However, you can learn Apps Scripts in this example.
Follow the tutorial below.

Hide columns C #

With this simple Apps Script, you can hide column C

function hideOneColumn() {
  var sheet = SpreadsheetApp.getActiveSheet();
  let columnToHide = 3
    // Hides column C
  sheet.hideColumns(columnToHide);
}

In columnToHide change number 3 to any other number of columns you want to hide.

If you want to get column by letter instead of number you can change

sheet.hideColumns(columnToHide);

sheet.hideColumn(sheet.getRange("C:C"));

Where instead of ”C:C” you can type „F:F” or any other column.

Let’s add a loop that will search for the value “hide” in the first row

Apps Script for hiding column if value in first row contains "hide" #

function hideColumns() {
  // connect to sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  // get values for selected sheet
  var values = range.getValues();
  
  // Set the value to hide columns for
  var valueToHide = "Value to hide column";
  
  for (var i = 0; i < values[0].length; i++) {
    if (values[0][i] == valueToHide) {
      sheet.hideColumns(i + 1);
    }
  }
}

Note that this script only checks the value's first row of the specified column. So if you have multiple cells in that column with the value you want to hide columns for, all of the corresponding columns will be hidden.

You can learn little bit of Google Apps Scripts and Javascript from this script.

  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();

Class SpreadsheetApp is udes to access and create Google Sheets file.
getActiveSheet() gets the active sheet in a spreadsheet.
getDataRange() returns a Range corresponding to the dimensions in which data is present.

var values = range.getValues()

This line of code returns values for this range. In our case, the values will look like this:

[ [ '1a', 'Column to hide', '1c', '1d' ],
  [ '1a', 'Hide me', '1c', '1d' ],
  [ '1a', 'Hide this column', '1c', '1d' ],
  [ '1a', 'Value to hide column', '1c', '1d' ],
  [ '1a', 'Hide column', '1c', '1d' ],
  [ '1a', 'Apps Script', '1c', '1d' ],
  [ '1a', 'Random value', '1c', '1d' ],
  [ '1a', 'Learn Apps Script', '1c', '1d' ],
  [ '1a', 'Second column', '1c', '1d' ],
  [ '1a', 'Last value', '1c', '1d' ] ]

values[0].length

Counting number of columns with values in the sheet.

for (var i = 0; i < values[0].length; i++) {
    if (values[0][i] == valueToHide) {
      sheet.hideColumns(i + 1);
    }

This loop iterates over each cell, starting from the first column, going on each row and moving to another column to check if there is a value for which we want to hide the column.

Use cases examples #

Some use cases where you might want to hide columns in Google Sheets based on cell values using Google Apps Script:

Data Management: You have a large data set with many columns, but you only want to see those columns where the total (stored in a particular cell) exceeds a certain threshold. Using a script, you could automatically hide columns where the total is less than this threshold, making the sheet easier to read.
Project Management: Suppose you're managing a project and have a timeline or Gantt chart where each column represents a day. Once a day has passed, you could use a script to automatically hide these columns, based on the date in a specific cell, keeping the current and future tasks in focus.
Attendance Tracker: If you're using Google Sheets to track student attendance and each column represents a student, you could use a script to hide the columns of students who have graduated or left the course, based on the value in a "Status" cell for each student.
Resource Allocation: Suppose you are managing resources in an organization where each column represents a resource, and you mark a cell in that column if the resource is fully booked or not. You could use a script to hide the columns of fully booked resources to quickly see the available resources.
Survey Responses Analysis: If you're analyzing survey responses and each column represents a question, you could use a script to hide the columns of questions where the average score (stored in a specific cell) is above a certain value, so you can focus on the questions where improvements are needed.

Using Google Apps Script for these tasks would require some basic knowledge of JavaScript, as Google Apps Script is essentially a JavaScript-based language. Google Apps Script also has specific classes and methods for interacting with Google Sheets, which you would need to familiarize yourself with.

If you are using Google Sheets for everyday work, learning Google Apps Scripts to manipulate Google Documents like Google Forms or Sheets is very useful. Try Google Apps scripts basics, and learn how to use Google Scripts.

Published 18 Feb 2023