<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1822615684631785&amp;ev=PageView&amp;noscript=1"/>

How to Turn Google Sheets Formatting into HTML (Then Mail Merge with GMass)

If you didn’t know, Google Sheets allows you to format pieces of text inside a cell.

Google Sheets formatting on individual pieces of text

However, that formatting is trapped inside Google Sheets. It might work if you copy and paste it some places, but most of the time, you’ll get plain text.

And if you do a straight up mail merge in GMass…

Trying to mail merge formatted text

…you’ll get the text from the cell but you won’t get the formatting.

The formatting doesn't work

But GMass is absolutely capable of using rich formatting with mail merge — as long as that rich formatting comes in the form of HTML tags.

So if you want to merge in some bolded text, it will need to be wrapped in <b></b> tags or <strong></strong> tags.

And I’d never make you do that manually.

Google Sheets doesn’t have a native function to detect the formatting in a cell and convert it to HTML. But with a Google Apps Script, you can.

In this guide, I’ll walk you through setting up a Google Apps Script that will automatically convert your formatted Google Sheets content into HTML — which you can then use in Gmail mail merge campaigns with GMass.

Google Sheets Formatting to HTML: Table of Contents

Step 1: Prepping Your Google Sheet

The first step in this process is making sure you have a Google Sheet that’s ready for a GMass campaign and prepped for you to convert its formatting to HTML.

That means:

  • You have a column of email addresses somewhere in the Google Sheet
  • You have an empty column next to your column of formatted text. If you don’t want to have to edit the Google Apps Script I’ve shared later in this article, call your column with the formatted text in it RichText and the empty column HTMLText.
Create two columns in your Google Sheet for the text

What formatting can you use?

The Google Apps Script I wrote can detect the following formatting on text in a cell:

  • Bold, italics, strikethrough, underline
  • Text color
  • Line breaks
  • Text size
  • Hyperlinks
  • Ordered lists (lines that start with a number, followed by a period, followed by a space)
  • Unordered lists (lines that start with a hyphen followed by a space)

The script will not detect the following:

  • Font, since using fonts in HTML emails is a whole thing
  • Background color, since it applies to the whole cell and not individual pieces of text
  • Text alignment (centering, et. al.)
  • Indenting
  • Cut-and-pasted images, since without the URLs of the images it gets messy to import them into emails; use traditional personalized image embedding instead

Now we’ll use our Google Apps Script to process your formatting and turn it into HTML.

Step 2: Adding the Google Apps Script

To use a Google Apps Script, go to Extensions > Apps Script in your Google Sheet.

Choose Apps Script from the Google Sheets menu

In the Apps Script window, you can change the name of the project and code file. Both are optional, but can be good as a reminder when you reference this script sometime in the future.

You can optionally rename the project and file

Now delete function myFunction() { } and paste in this entire script.

See the Pen
Turn Google Sheets formatting into HTML
by Sam G (@samgmass1)
on CodePen.


If you did not name your columns RichText and HTMLText, change those names at the beginnings of the script.

The result:

The script inserted into the Apps Script window

Now click the floppy disk icon to save the script.

Then click the Run button.

Save then run the apps script

You’ll need to agree to permissions for the script to run.

Google Apps Scripts require permissions

Click Allow to give the script access to your Google Sheets.

Allow the apps script to run

And after you’ve successfully run the script, go back to your Google Sheet.

The HTMLText column should now show you your formatted text from the RichText column, but with HTML tags corresponding to the formatting.

There's now an HTML version of the Google Sheets formatting

Optional: Setting up the script to run every time you add a new row to your Google Sheet

If you plan on adding to the spreadsheet, you can set this script to run every time it detects a new row.

To set that up, click on the stopwatch icon on the left side of the Google Apps Script window to set up a trigger.

Create a trigger to make this script run in the future

Click on the + Add Trigger button in the bottom right.

Add the trigger with the button

For your trigger settings, go with the following:

  • Choose which function to run: copyFormattedText
  • Choose which deployment should run: Head
  • Select event source: From spreadsheet
  • Select event type: On change
  • Failure notification settings: Notify me daily
Settings for your trigger

Then click Save.

Now whenever there’s a change in your Google Sheet, the Apps Script will run to turn your formatted text into HTML.

An example of converted text after it was added

And with that, it’s time to use this HTML-formatted text in a GMass campaign.

Step 3: Using Your Formatted Text in a GMass Campaign

Go to Gmail and click the spreadsheet icon next to the search bar to begin your new campaign.

Connect to your Google Sheets in GMass back in Gmail

Select your Google Sheet from the dropdown menu. You can use any optional settings here if you want, like filtering, keeping duplicate emails, or updating your sheet with reporting data. (In my example, I’m not using any of those.)

Then click Connect to Spreadsheet.

Choose your formatted Google Sheet from the dropdown

Compose your email message. And use the {HTMLText} merge tag wherever you want to put in the formatted text from your Google Sheet.

Use the correct mail merge tag

Handle any other settings you want for your campaign.

And when you’re ready to send your campaign, hit the red GMass button.

If you want to preview the emails and make sure everything looks perfect before you send, you can use the Create Drafts setting and then hit the red GMass button. That’s what I’ll do here.

Create drafts to see previews

Seeing your formatted text in your messages

If you created drafts, you’ll find your messages in your Drafts folder. If they look good, click the link in the email you get from GMass to send your messages.

If you sent your emails without creating drafts first, you can see your messages in the Sent folder.

Click into one, and you’ll see your formatted text in place.

How the email looks after mail merging the HTML version

If things aren’t looking right, make sure you named the columns in your Google Sheet properly and you used {HTMLText}, not {RichText}, on your mail merge.

Getting Started with GMass

And if you haven’t tried GMass… time to give it a shot!

The technique in this article is just one of the many, many mail merge personalization options in GMass. You can also mail merge links, images, attachments, and more on your campaigns.

And that’s not all. GMass also has the easiest automated follow-ups in the game — and you can use mail merge in those as well.

Join the 300,000+ others who’ve given GMass an average score of 4.8+ stars across 7,500+ reviews!

You can get started by installing the GMass extension from the Chrome Web Store. It’s free to try and there’s no credit card required for the free trial.

You’ll be sending cold outreach, email marketing, and/or other mail merge campaigns from inside Gmail in a matter of minutes.

Ready to send better emails and save a ton of time?


GMass is the only tool for marketing emails, cold emails, and mail merge — all inside Gmail. Tons of power but easy to learn and use.


TRY GMASS FOR FREE

Download Chrome extension - 30 second install!
No credit card required
Love what you're reading? Get the latest email strategy and tips & stay in touch.
   


1 Comment
  1. I do not completely follow the use case for this. Admittedly, I don’t often create a lot of text with varying formatting in a specific field for just one client that would then need to be copied over to an email. I do often format text within the Gmass email so that the same word(s) are formatted for each recipient, but that would not need this feature to pull off.

    Would someone please share a use case for this? Especially if it has to do with marketing to clients.

Leave a Reply

Your email address will not be published. Required fields are marked *

Find out why GMass is the most popular Gmail mail merge tool in the world

Easy to learn. Easy to use. Packed with time saving, email enhancing features.

Try GMass for free Then check out the quickstart guide to send your first mail merge email in minutes!

GMass

Share This