Google Sheets Remove Empty Columns & Rows Automatically

google_sheets_remove_empty_columns_remove_empty_rows

This is a tutorial on how to remove empty columns and rows in Google Sheets automatically with the click of a button. If you work with lots of data in Google Docs, this will help you stay under the 2 million cell limit in Google Sheets by removing unused cells. Here is the sample sheet. Go to the File – Make a Copy to edit the sheet.

Watch the video or follow the steps below to run the script.

1) File – Make a copy… of the sample sheet

2) Click the Remove Empty Rows button in Sheet1

3) Click Continue to allow the app to Authorize

4) Click Allow for the remove empty columns and rows script to View and manage your spreadsheets

5) You’ll see a yellow message on the top of the screen “Finished Script” when the Script has run and all the extra empty rows have been deleted from all the tabs in the sheet

6) Click the Remove Empty Columns button in Sheet1

7) You’ll see the same yellow message on the top of the screen “Finished Script” when the Script has run and all the extra empty columns have been deleted from all the tabs in the sheet

The data sets used in the the Google Sheet

Sheet1: Google Analytics data
Sheet2: Alta, Utah annual snowfall data
Sheet3: Odell Beckham Jr. stats

To edit the script go to Tools – Script editor…

You can edit the script and debug right in the Script editor. Click on the bug icon to step through your code for debugging and click on the play button to run your script. Below is a link to the script on GitHub.

Assign a Google Sheets script to a button click

The script in the sample sheet is linked to the two buttons. To assign a script to a button right click on the button then click the down arrow in the top right of the button. Click Asign script… then put the function you’d like to assign to run on the button click. In this case removeEmptyRows and removeEmptyColumns are the function names.

A real life example of the script in action – Google Analytics Sheets Add-on

I used this script in combination with the Google Analytics Sheets Add-on to pull more than 10000 rows of unsampled Google Analytics data and in the post I describe how I did it. This is a great example of how to push the data limits of Google Sheets. It allowed me to quickly remove empty rows and columns and stay under the 2 million cells per sheet maximum.

If you have any questions about using the Script or if you find it useful please let me know in the comments below or @ryanpraski.

  • Raelindia

    Thanks for the workaround.

    I tried this and got an error “You can’t delete all the columns on the sheet.”.
    Any idea what could be the problem?

    • William

      I’m having the same issue

    • William

      Ok I figured it out. Apparently I was getting the “You can’t delete all the columns on the sheet” error when one of my sheets was completely blank. After deleting the blank sheet everything worked.

  • Christina

    Thanks for the tip. Ryan was able to help me find a way to use a script for google spreadsheet in which rows can be deleted that only have some cells that have zero values.

  • one7media

    How do you limit this to only 1 sheet in the workbook?

    • To select a single Google Sheet to remove blank rows and columns instead of all sheets you can edit the code

       var sheet = ss.getSheetByName("Sheet1"); 

      If you have trouble editing the Google App Script you can always just copy the single sheet you can always run the script on a workbook with the single sheet.