How to Auto-Trim Whitespace

Ever had something break, mysteriously, in your spreadsheet? It could be because of extra whitespaces that are accidentally copied into cells.

In today’s email:

1. Trim white space with Apps Script

2. Works on multiple sheets

3. Preserves formulas and functions

4. Video walkthrough coming soon…

SPONSOR

Got Sheet is powered by beehiiv, my go-to newsletter operating system for online businesses. Try for free here.

MAIN ARTICLE

How to trim whitespace in Google Sheets

We’ve got access to the TRIM() function in Google Sheets. This removes the extra spaces in a cell. Everything except for a single space between words.

This is great for one-off uses, but if we have an infection of whitespaces throughout our entire workbook, we need a better solution.

Thanks to Margot, who wrote in with this problem and asked how to implement a solution for her work! 🙌

How to trim whitespace with Apps Script

We’ll use Apps Script, of course. The full code is below for you to copy.

Everything lives inside of one function: cleanWhiteSpaces()

We grab all the sheets in our active spreadsheet with:

const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets

The bulk of the work then lives inside a forEach loop where we go through each of those sheets.

This part can be omitted if you only have one sheet, but we’re going the extra mile here.

I also added a try…catch block to handle some errors* that I initially encountered (more on that below).

Then we need to define some constants for the data range, the values of the data and the formulas in the range:

const range = sheet.getDataRange();
const values = range.getValues();
const formulas = range.getFormulas();

*errors - turns out that when you have an image in a cell in Google Sheets, it causes issues when running the code. This is why the try…catch block was necessary for multiple pages in this case.

Nested Loops

Then comes some nested loops. We’ve got to loop through each cell in the range, so we need to keep track of our row position as well as our column position. That’s why we nest these for loops.

Pro Tip - copying any of the lines of code into Claude or ChatGPT and then asking it to explain it to you in simple terms line by line is a great tool for learning the intricacies of the code.

for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {

Then we have another try…catch block. It will clean the whitespace, using trim() (this time we’re using the JavaScript method instead of the Google Sheets function).

We assign the new trimmed values or the formulas back to their respective positions in the values const.

And we log any errors we may receive.

try {
  // Clean whitespace in non-formula cells, preserve formulas
  if (formulas[i][j] === "" && typeof values[i][j] === "string") {
    values[i][j] = values[i][j].replace(/\s+/g, " ").trim();
  } else {
    values[i][j] = formulas[i][j] || values[i][j];
  }
} catch (cellError) {
  // Log errors for specific cells
  Logger.log(`Error cleaning cell R${i + 1}C${j + 1}: ${cellError.message}`);
}

Writing the values back to the sheet

Now we simply write those new values back into the sheet we’re currently looping through.

I added a short delay to make sure it could handle going from one sheet to the next.

/ Write cleaned data back to the sheet
range.setValues(values);

// Add delay to prevent quota issues
Utilities.sleep(500);

Error Handling

Then we finish out our initial try…catch block by logging any errors we had when getting the consts in the sheet.

catch (sheetError) {
  // Log errors for specific sheets
  Logger.log(`Error processing sheet ${sheet.getName()}: ${sheetError.message}`);
}

Apps Script code for trimming whitespace

Here’s the full code for you if you’d like to copy and use in your workbook.

I’ll have a video walkthrough recorded soon on my YouTube channel. I’ll go through all this plus adding a button to the spreadsheet to press and run the code.

function cleanWhiteSpaces() {
  // Get all sheets in the active spreadsheet
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  sheets.forEach((sheet) => {
    try {
      // Get all data and formulas from the sheet
      const range = sheet.getDataRange();
      const values = range.getValues();
      const formulas = range.getFormulas();

      Logger.log(`Processing sheet: ${sheet.getName()}`);

      for (let i = 0; i < values.length; i++) {
        for (let j = 0; j < values[i].length; j++) {
          try {
            // Clean whitespace in non-formula cells, preserve formulas
            if (formulas[i][j] === "" && typeof values[i][j] === "string") {
              values[i][j] = values[i][j].replace(/\s+/g, " ").trim();
            } else {
              values[i][j] = formulas[i][j] || values[i][j];
            }
          } catch (cellError) {
            // Log errors for specific cells
            Logger.log(`Error cleaning cell R${i + 1}C${j + 1}: ${cellError.message}`);
          }
        }
      }

      // Write cleaned data back to the sheet
      range.setValues(values);

      // Add delay to prevent quota issues
      Utilities.sleep(500);

      Logger.log(`Finished processing sheet: ${sheet.getName()}`);
    } catch (sheetError) {
      // Log errors for specific sheets
      Logger.log(`Error processing sheet ${sheet.getName()}: ${sheetError.message}`);
    }
  });

  // Log completion message
  Logger.log("Processing complete!");
}

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Consulting (limited availability)
   Grab a slot on my calendar for help with a spreadsheet OR newsletter project.

2. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

3. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

4. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

5. YouTube

   If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.