Javascript

How to trim all cells in Google Spreadsheets

January 5, 2015

trimming

I’ve been using Google Spreadsheets to clean data and I recently discovered this little script to trim (remove leading and trailing spaces) from all cells. This is really great.

In an open Google Spreadsheet:

  1. Go to Tools, then Script Editor.
  2. Copy and paste the code below into the Script Editor.
  3. Save the script.
  4. In the Script Editor go to Run, then onOpen.
  5. Return to the open Spreadsheet and you’ll see a new menu item, Trim Cells (you can close the Script Editor).
  6. Highlight the cells you’d like to trim and run your new script!

Screen Shot 2015-01-05 at 3.05.37 PM

The Trim Cell Script

var ss = SpreadsheetApp.getActiveSpreadsheet();

function trimSpacesInSelectedCells() {
  var sheet = ss.getActiveSheet();
  var activeRange = sheet.getActiveRange();
  for (var cellRow = 1; cellRow <= activeRange.getHeight(); cellRow++) {
    for (var cellColumn = 1; cellColumn <= activeRange.getWidth(); cellColumn++) {
      cell = activeRange.getCell(cellRow, cellColumn);
      cellFormula = cell.getFormula();
      if (cellFormula[0] != "=") {
        cellValue = cell.getValue();
        cell.setValue(String(cellValue).trim());
      }
    }
  }
}

function onOpen() {
  var entries = [{
    name : "Remove leading and trailing spaces",
    functionName : "trimSpacesInSelectedCells"
  }];
  ss.addMenu("Trim Cells", entries);
}

 

Anthony Montalbano

If it's worth doing, it's worth doing right.

Published on: January 5, 2015

Last modified on: July 22, 2020