Apps Script: Auto-Trim Google Sheets

Apps Script Course Early Access

OVERVIEW

1. The Issue with Standard Cleanup Methods

2. The Script: Smart Trimming with Formula Protection

3. Known Limitation: Images in Cells

RESOURCES

Some tools to make life easier. You’re welcome :)

• beehiiv - my choice for a newsletter operating system

• Coefficient - Live connections to 60+ business systems

• Lido - Automate your spreadsheets; accurately extract date from PDFs

• TransactionPro - quickly import, export or delete data in Quickbooks

• Perspective - Build high-converting lead funnel microsites

• Gamma - AI designer for presentations, websites, social posts, and more

• Gratitude Plus - social gratitude journal

• Senja - collect and display testimonials

• OpusClip - turn long videos into short clips

• Carrd - free one-page website builder

• Notion - notetaking + project management + database

• Transistor - my favorite podcast host

• Fathom - AI-powered notetaking app

Apps Script Auto-Trim

Bulk cleaning up white space in spreadsheets often comes at a cost: losing formulas. Most approaches overwrite the very logic that makes the sheet functional.

This method avoids that problem. With a small Apps Script, you can automatically trim whitespace across your entire Google Sheet—without touching any formulas.

Here’s the full video walkthrough (4min 47sec). Keep reading for the rundown.

The Issue with Standard Cleanup Methods

Trimming whitespace usually means converting everything to values first—copy, paste special, then run TRIM.

That works for static data. But if your sheet includes dynamic formulas, it breaks them. You’re left rebuilding formulas or fixing broken references after the fact.

For spreadsheets with live inputs, dashboards, or any automation, this isn't good.

The Script: Smart Trimming with Formula Protection

This custom Apps Script solves the problem with precision.

Here’s what it does:

• Loops through each sheet in the workbook

• Checks each cell to see if it contains a formula

• Trims leading, trailing, and extra internal whitespace only from cells without formulas

• Leaves formula cells untouched

• Adds a small delay to avoid rate-limiting

It runs on a button click and works across multiple sheets.

Here’s the demo sheet for you to check out.

Known Limitation: Images in Cells

If a sheet contains images embedded directly in cells, the script may trigger an error. This is a known issue with how Apps Script handles image-in-cell data. The script includes a try/catch block that logs the error and skips that sheet, so it doesn’t break the rest of the process.

If needed, the script can be customized to handle these cases.

Deployment Steps

1. Open your sheet and go to Extensions > Apps Script

2. Paste in the script (linked in the video description)

3. Optionally create a custom menu or button to run the function

4. Run the script from the editor or the sheet interface

Once executed, it returns a clean version of the data—with all formulas preserved and excess whitespace removed.

Use Cases

• Shared spreadsheets where data entry inconsistencies create downstream issues

• Dashboards and lookups dependent on clean, predictable strings

• Client-facing sheets that require polished formatting without sacrificing logic

This small automation improves readability, reduces lookup errors, and ensures data integrity—especially when formulas are part of the workflow.

Want to Work with Me?

Need personalized help? Reply to this email or grab a slot on my calendar here.

P.S.
This project came to be after a reader emailed me asking how to do it. If you’ve got a sheets problem you’re scratching your head over, let me know. I read every email, and it may even make it into a future video.

NEXT STEPS

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

1. Work with me
   I am available for consulting projects. Reach out and let’s chat.

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 50% discount on the personal finance system I update every year.

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!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.