Will this overwrite my formulas with plain text?

Yes. getValues() returns computed values, not formula strings, so setValues() replaces any formula in the range with its current output as a static value. Run this on raw data sheets or make a copy first if you need to preserve formulas.

My sheet has 50,000 rows — will this time out?

Apps Script has a 6-minute execution limit for user-run scripts. A single getDataRange().getValues() call on 50,000 rows is usually under 5 seconds; the JavaScript loop is fast. The single setValues() write is the only other slow call. In practice this handles 100,000 cells comfortably. If you exceed that, split the sheet into chunks by row and call setValues() per chunk.

How do I trim only one column instead of the whole sheet?

Replace sheet.getDataRange() with sheet.getRange(1, 2, sheet.getLastRow(), 1) — that targets column B from row 1 to the last used row. Adjust the second argument (column index, 1-based) for whichever column you want.

Does this handle double spaces in the middle of a string?

The current script only calls trim(), which removes leading and trailing whitespace. Interior double spaces survive. To collapse those too, add cell = cell.replace(/ +/g, ' ') after the trim() line. That regex replaces one or more consecutive spaces with a single space.

// Sheets · Apps Script

Trim whitespace from every cell in Google Sheets.

A Google Apps Script that strips leading, trailing, and non-breaking spaces from every cell in a sheet — handling the NBSP characters that TRIM() and trimWhitespace() silently leave behind — then writes cleaned values back in a single batch call.

“I pasted data from a website or PDF into Sheets and now TRIM() isn't removing the spaces, so sorting, VLOOKUP, and exact-match formulas keep failing.”

The script

copy · paste · trigger

trimAllCells.gs

Apps Script

// trimAllCells — strips regular + non-breaking spaces from every cell
// Run from Extensions > Apps Script, then save and run trimAllCells()
function trimAllCells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var r = 0; r < values.length; r++) {
    for (var c = 0; c < values[r].length; c++) {
      var cell = values[r][c];
      if (typeof cell === 'string') {
        //   is the non-breaking space pasted from browsers and PDFs
        cell = cell.replace(/ /g, ' ');
        cell = cell.trim();
        values[r][c] = cell;
      }
    }
  }

  range.setValues(values);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why TRIM() and trimWhitespace() both fail on pasted data

The built-in TRIM() formula and the menu option Data > Data cleanup > Trim whitespace both work on ASCII space (character code 32). They do nothing with U+00A0, the non-breaking space that browsers inject whenever you copy from a webpage, a Google Doc, or a PDF viewer. It looks identical to a regular space on screen, but it is a different character — so TRIM() returns the string unchanged, and VLOOKUP quietly misses every row.

You can confirm this is your problem without writing any code: in an empty cell, type =CODE(LEFT(A1,1)) where A1 holds the suspicious value. A result of 160 means non-breaking space. A result of 32 means normal space. The script below handles both.

What the script does, and why setValues() matters

getDataRange() returns the smallest rectangle that covers all non-empty cells — so the script never processes blank padding at the bottom of the sheet, which keeps it fast even on large datasets.

The inner loop checks typeof cell === 'string' before touching anything, which means dates, numbers, and booleans pass through untouched. That matters: calling replace() on a Date object throws a runtime error, and calling it on a number silently coerces it to a string and destroys the numeric type.

The first time I hit this I wrote a version that called sheet.getRange(r+1, c+1).setValue(cell) inside the loop. On a 2,000-row sheet that fired 2,000 individual API calls and hit the Apps Script quota wall partway through, leaving the sheet half-cleaned. Reading the full range into a 2-D array, modifying in memory, then writing back with a single setValues() call is the correct shape for any bulk cell operation in Apps Script.

Running it and wiring it to a menu

Open Extensions > Apps Script, paste the function, save, and hit Run. The first run will ask for permission to edit the spreadsheet — that is the standard OAuth consent for user-run scripts, not a red flag.

If you clean imported data regularly, add an onOpen trigger so the function appears in a custom menu every time the sheet loads. Add this below the existing function:

function onOpen() { SpreadsheetApp.getUi().createMenu('Utilities').addItem('Trim all cells', 'trimAllCells').addToUi(); }

After that, a Utilities menu appears automatically and the trim runs on demand without reopening the script editor.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Will this overwrite my formulas with plain text?: Yes. getValues() returns computed values, not formula strings, so setValues() replaces any formula in the range with its current output as a static value. Run this on raw data sheets or make a copy first if you need to preserve formulas.
My sheet has 50,000 rows — will this time out?: Apps Script has a 6-minute execution limit for user-run scripts. A single getDataRange().getValues() call on 50,000 rows is usually under 5 seconds; the JavaScript loop is fast. The single setValues() write is the only other slow call. In practice this handles 100,000 cells comfortably. If you exceed that, split the sheet into chunks by row and call setValues() per chunk.
How do I trim only one column instead of the whole sheet?: Replace sheet.getDataRange() with sheet.getRange(1, 2, sheet.getLastRow(), 1) — that targets column B from row 1 to the last used row. Adjust the second argument (column index, 1-based) for whichever column you want.
Does this handle double spaces in the middle of a string?: The current script only calls trim(), which removes leading and trailing whitespace. Interior double spaces survive. To collapse those too, add cell = cell.replace(/ +/g, ' ') after the trim() line. That regex replaces one or more consecutive spaces with a single space.

// one good script a week