Z Zestmail
← All posts

Gmail Mail Merge Google Sheets: Fix Date and Price Formats

If your Gmail mail merge from Google Sheets puts "46321" where a date should be, or "1250" where you wanted "$1,250.00", the fix is the same in every case: stop sending the raw cell and send text instead. Add a helper column that converts the value with the TEXT() function, point your merge at that column, and the email will show exactly what you see in the sheet. The rest of this article explains why this happens, which formulas to use for dates, money, percentages and ID numbers, and what to check before you press send.

Why a Gmail mail merge from Google Sheets changes your dates and prices

A date in Google Sheets is not really the text "26 October 2026". Underneath, it is a serial number (the number of days since 30 December 1899), and the sheet only displays it as a date because the cell has a date format applied. The same is true for money: "$1,250.00" is the number 1250 with a currency format on top.

A mail merge tool reads your sheet through Google's API, and depending on the tool it may receive the underlying value rather than the displayed text. That is how you end up with:

  • 46321 instead of a readable date
  • 1250 instead of $1,250.00
  • 0.15 instead of 15%
  • 7 instead of 000007 for an invoice or member number
  • 2026-10-26T00:00:00.000Z for dates in some tools

Some tools read the formatted value and some read the raw one, and you usually cannot tell which until you look at a test email. Rather than learning each tool's behaviour, make the column itself plain text. Then every tool shows the same thing.

If you are on a Google Workspace plan that includes Gmail's built-in mail merge (the "multi-send" option in the compose window), it can pull columns from a linked sheet too, and the same advice applies. A free personal Gmail account does not have that option, which is why most people use a Google Sheets URL with an add-on, an extension or a small Apps Script.

Build merge-safe columns with TEXT()

The TEXT() function takes a value and a format pattern and returns a text string. The result is no longer a number or date, so no tool can reinterpret it. Keep your original columns for sorting and filtering, and add new ones next to them for the merge.

Dates

Say your due date is in column C, with a header "Due" and the first data row on row 2. In a new column with the header "Due text", enter:

=TEXT(C2,"d MMMM yyyy")

That gives 26 October 2026. Other patterns that work in Google Sheets:

  • "dddd d MMMM" → Monday 26 October
  • "MMM d, yyyy" → Oct 26, 2026 (US style)
  • "dd/MM/yyyy" → 26/10/2026
  • "yyyy-MM-dd" → 2026-10-26

Note the capital MM for months. Lowercase mm means minutes, which is the most common reason a date formula shows 00 where the month should be.

Currency and amounts

For an amount in column D:

=TEXT(D2,"$#,##0.00")

This produces $1,250.00. Replace the $ with € or £ as needed, or drop it if the currency is written out in the email. If you do not want decimals, use "$#,##0". The #,##0 part adds the thousands separator and makes sure a zero shows as 0 rather than an empty string.

If your sheet uses a comma as the decimal separator (common in many European locales), the pattern follows the sheet's locale, so "#.##0,00" is not needed; keep the pattern as above and Google Sheets outputs the separators your locale uses.

Percentages

A discount stored as 0.15:

=TEXT(E2,"0%")

gives 15%. Use "0.0%" for one decimal place.

Numbers with leading zeros

Invoice, member or order numbers lose their leading zeros the moment Google Sheets treats them as numbers. Either format the column as plain text before pasting the data (select the column, then Format → Number → Plain text), or convert on the fly:

=TEXT(F2,"000000")

That turns 7 into 000007. Phone numbers are the same problem: store them as plain text or they will lose the leading zero and may be shown in scientific notation.

Copy the formula down and handle blanks

Drag the fill handle down, or use an array formula in the header row so new rows are covered automatically:

={"Due text"; ARRAYFORMULA(IF(C2:C="", "", TEXT(C2:C,"d MMMM yyyy")))}

The IF(C2:C="", "", ...) part matters. Without it, an empty date cell becomes 30 December 1899 in the email, which looks worse than nothing. If a value is genuinely optional, a blank is fine; if it is required, the pre-send checks below will catch it.

Other columns that look fine in the sheet but break in the email

Dates and amounts are the loudest problems, but a few quieter ones cause the same "that is not what I typed" reaction.

Trailing spaces and invisible characters

Data pasted from a CRM, a form or another spreadsheet often carries extra spaces or line breaks. "Hi Sarah ," with a space before the comma is the usual symptom. Clean the column with:

=TRIM(CLEAN(B2))

CLEAN strips non-printing characters and TRIM removes leading, trailing and doubled spaces. Do this on name, company and address columns, not just the one you noticed.

Full name where you need a first name

If you only have a "Name" column, the first word is usually enough for a greeting:

=INDEX(SPLIT(B2," "),1)

Watch for rows where the name is actually a company ("Acme Ltd") or in "Last, First" order. A quick sort of the column shows the odd ones. What to do when the result is empty is a separate topic; the short version is to always provide a neutral fallback so nobody receives "Hi ,".

Capitalisation

=PROPER(B2) turns "SARAH JONES" into "Sarah Jones". It also turns "McDonald" into "Mcdonald" and "iPhone" into "Iphone", so apply it, then scan the column rather than trusting it blindly.

Formulas that reference other sheets

If your helper columns use IMPORTRANGE or VLOOKUP to another file, the merge tool may read them while they are still loading, which shows up as #N/A or Loading... in the email. The safe move is to freeze the values: select the helper columns, copy, then Edit → Paste special → Values only. Do this right before sending, after the data is final.

Pre-send checks that take five minutes and save a resend

A mail merge is the one email you cannot recall, so check it the same way every time.

  1. Match header names to placeholders exactly. Most tools match on the header text, so Due text and Due Text are different columns. Use short headers without punctuation: due_text, amount_text, first_name.
  2. Add yourself as row 2. Put your own email address in the first data row with deliberately awkward values: a long name, a four-digit amount, a date at the end of the month. Send to that row only, open the email on your phone and on a desktop, and read it as the recipient would.
  3. Filter for blanks in required columns. Select the header row, Data → Create a filter, open the filter on each required column and tick only "(Blanks)". Anything that appears needs a value or a fallback before sending. Clear the filter afterwards so no rows are hidden from the merge.
  4. Check the count. The number of rows with an email address should equal the number of emails you expect to send. A stray header row copied into the data, or an email in the wrong column, will show up here.
  5. Look for duplicates. Select the email column, then Data → Data cleanup → Remove duplicates. Sending the same person two identical emails is a quick way to lose their trust.
  6. Freeze formula results (Paste special → Values only) if anything depends on another sheet, as described above.
  7. Send in a small batch first. Ten rows, then check the sent folder, then the rest. If something slipped through, you have ten apologies to write instead of three hundred.

Where a tool like Zestmail fits in

All of the above works with any mail merge method, including a free Apps Script you write yourself. What a dedicated tool adds is convenience: you paste the sheet's URL instead of wiring up code, and you stay inside the Gmail compose window.

Zestmail is a Chrome extension for Gmail that includes mail merge from a Google Sheets URL, alongside scheduled sending and email open tracking that shows whether and when each recipient opened the message, with your own opens and common bot opens filtered out. For a merge that is really a batch of invoices or reminders, seeing which ones were opened tells you who to chase, and the open times help you pick a sending hour for the next batch. Open tracking is not perfect; image blocking and privacy proxies like Apple Mail Privacy Protection can hide opens or report them early, so treat it as a strong signal rather than proof. Zestmail does not request permission to read your Gmail inbox, though some features do send the draft to its server to work.

The free plan covers up to 20 emails per day and adds a small "Sent with Zestmail" footer to each email. Pro removes that limit and costs $9 per month or $69 per year, with a 7-day free trial; the details are on the pricing page. If your merge is a one-off with a handful of rows, the free plan is enough. Either way, build the text columns first: the tool can only send what the sheet gives it.