How To Not Copy Hidden Cells Google Sheets

by

Google Sheets is a popular cloud-based spreadsheet tool that offers numerous functionalities, including the ability to hide cells. But there are situations when you need to copy a range of cells, but do not want to include those hidden cells in the copied range. So, how can you copy visible cells only in Google Sheets and avoid the hidden ones?

In this blog post, we will walk you through step-by-step instructions on how you can achieve this. Unfortunately, Google Sheets does not currently have a built-in feature to selectively copy only visible cells. However, there are workarounds that can help you achieve this.

Workaround 1: Using Filters

One of the easiest solutions to this problem is to simply use the Filter function in Google Sheets.

Follow these steps:

  1. Select the cell range that you wish to copy.
  2. Click on Data and then Create a filter.
  3. Use the filter to hide the rows that you don’t want to copy.
  4. Now, copy the range. This should exclude any hidden cells.

Workaround 2: Using Google Apps Script

If you are comfortable with using Google Apps Script, you can write a custom function to copy only visible cells.

Below is a sample script that you can use:

    function copyVisibleCells() {
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var range = sheet.getDataRange();
      var values = range.getValues();
      
      for(var i = range.getNumRows() - 1; i >= 0; i--) {
        if(sheet.isRowHiddenByUser(i + 1)) {
          values.splice(i, 1);
        }
      }
      
      var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
      newSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
    }

This script gets the active sheet and the data range. It then loops through the rows in reverse order (so as not to mess up the indices when deleting rows). If a row is hidden, it is removed from the values array. Finally, a new sheet is created and the values array (minus the hidden rows) is written to the new sheet.

Note: This method will not copy cell formatting or formulas.

Even though Google Sheets doesn’t provide a direct method to copy only visible cells, with these workarounds, you can achieve just that. Whether you choose to use filters or scripts will depend on your comfort level with Google Sheets and coding. Either way, it’s possible to leave hidden cells behind when copying your data.