Clean up data in a spreadsheet

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.

Demo of add-on formatting a spreadsheet

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:

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

  1. Click the button below to open the Clean sheet Apps Script project.
    Open the project
  2. At the top, click Overview .
  3. At the top-right, click Make a copy The icon for making a copy.
  4. At the top of the copied project, click Deploy > Test deployments.
  5. Next to Select type, click Enable deployment types The icon for project settings > Editor Add-on.
  6. Click Create new test.
  7. Under Test document, click No document selected.
  8. 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.
  9. Click Save test.
  10. To open the spreadsheet, select the radio button next to the saved test and click Execute.

Step 2: Clean up data

  1. In the spreadsheet, select the range A1:F20.
  2. Click Extensions > Clean sheet > Delete blank rows.
  3. When prompted, click Continue and authorize the script.
  4. Click Extensions > Clean sheet > Delete blank rows again.
  5. Click Extensions > Clean sheet > Delete blank columns.
  6. Click Extensions > Clean sheet > Crop sheet to data range.
  7. Select cell C7.
  8. Click Extensions > Clean sheet > Fill in blank rows below.

Contributors

This sample is maintained by Google with the help of Google Developer Experts.