Coding level: Intermediate
Duration: 20 minutes
Project type: Editor Add-on
About this solution
Clean up spreadsheet data by automatically removing empty rows and columns, cropping the spreadsheet to the edges of the data range, and filling in gaps in the data.
How it works
The script performs the following functions:
- Delete blank rows: Within a selected range, the script identifies empty rows and deletes them. If cells within a row contain space characters, the row isn't considered empty.
- Delete blank columns: Within a selected range, the script identifies empty columns and deletes them. If cells within a column contain space characters, the column isn't considered empty.
- Crop sheet to data range: The script identifies where the data range ends and deletes the excess rows and columns.
- Fill in blank rows: The script copies and pastes the content of the selected active cell to the empty cells in the rows below it. The script stops pasting content when it encounters a row that isn't empty or reaches the end of the data range.
Apps Script services
This solution uses the following service:
- Spreadsheet Service: Gets the active sheet and performs all of the cleanup functions.
Before you begin
To use this sample, you need the following prerequisites:
- A Google Account (Google Workspace accounts might require administrator approval)
- A web browser with access to the internet
Try it
Step 1: Set up the Apps Script project
- Click the button below to open the Clean sheet Apps Script project.
Open the project - At the top, click Overview .
- At the top-right, click Make a copy
.
- At the top of the copied project, click Deploy > Test deployments.
- Next to Select type, click Enable deployment types
> Editor Add-on.
- Click Create new test.
- Under Test document, click No document selected.
- Choose a spreadsheet with data to clean up and click Insert. To use a sample document, make a copy of the Sample cleanup data spreadsheet.
- Click Save test.
- To open the spreadsheet, select the radio button next to the saved test and click Execute.
Step 2: Clean up data
- In the spreadsheet, select the range
A1:F20
. - Click Extensions > Clean sheet > Delete blank rows.
- When prompted, click Continue and authorize the script.
- Click Extensions > Clean sheet > Delete blank rows again.
- Click Extensions > Clean sheet > Delete blank columns.
- Click Extensions > Clean sheet > Crop sheet to data range.
- Select cell
C7
. - Click Extensions > Clean sheet > Fill in blank rows below.
Contributors
This sample is maintained by Google with the help of Google Developer Experts.