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.