Google Sheets postcard mail merge with Apps Script
A Google Sheets postcard mail merge with Mailr is one Apps Script file: choose Mailr > Mail unsent rows, UrlFetchApp sends the rows to Mailr in a single batch request, and Mailr prints a postcard for each row, mails it with USPS and the script writes each postcard id next to its address.
What to mail from Google Sheets
| When this happens | Mail this |
|---|---|
| You choose Mailr > Mail unsent rows (a custom menu) | A postcard for every row without a Mailr ID, up to 1,000 per request. (Win-back) |
| A Google Form response lands in the sheet (installable On form submit trigger) | A thank-you postcard to the address on the form. (First-order thank you) |
| A time-driven trigger runs every morning | The same mail merge for rows added since the last run. |
Set it up
- Lay out the sheet. Name the tab Mailing list. In row 1 put the headers Name, Address, Address 2, City, State, ZIP, First name, Last product and Mailr ID. Format the ZIP column as plain text (Format > Number > Plain text) so 02134 keeps its zero; the script also pads short ZIPs.
- Add the script and your key. Open Extensions > Apps Script, paste the code and save. In Project Settings, under Script Properties, add MAILR_API_KEY with your Mailr test key.
- Run it from the menu. Reload the sheet and a Mailr menu appears. Choose Mail unsent rows and approve the permissions Google asks for, including connecting to an external service. The Mailr ID column fills in, and the pieces show in your Mailr dashboard.
- Automate it if you want. For form responses or a daily run, open Triggers (the clock icon) in Apps Script and add an installable trigger. A simple onEdit trigger can't make external requests.
- Go live. Add funds in Mailr and change the script property to your mlr_live_ key. Rows already sent keep their ids and are skipped.
The Apps Script (Extensions > Apps Script)
const MAILR = "https://api.mailr.dev/v1";
const FROM = { name: "Acme Outdoor", line1: "1 Example Way", city: "Phoenix", state: "AZ", zip: "85004" };
function onOpen() {
SpreadsheetApp.getUi().createMenu("Mailr").addItem("Mail unsent rows", "mailUnsentRows").addToUi();
}
function mailUnsentRows() {
const sheet = SpreadsheetApp.getActive().getSheetByName("Mailing list");
const [head, ...rows] = sheet.getDataRange().getDisplayValues();
const idCol = head.indexOf("Mailr ID");
if (idCol < 0) throw new Error('Add a "Mailr ID" column header.');
const v = (r, name) => (head.indexOf(name) < 0 ? "" : String(r[head.indexOf(name)]).trim());
const zip5 = (zip) => (/^\d{3,4}$/.test(zip) ? zip.padStart(5, "0") : zip); // 2134 back to 02134
// Rows with an address and no Mailr ID yet; one batch takes up to 1,000.
const todo = rows
.map((r, i) => ({ r, row: i + 2 }))
.filter(({ r }) => v(r, "Address") && !r[idCol])
.slice(0, 1000);
if (todo.length === 0) {
SpreadsheetApp.getActive().toast("Nothing to mail.");
return;
}
const body = JSON.stringify({
kind: "postcard",
size: "6x11",
template: "tmpl_winback",
from: FROM,
merge: { promo_code: "COMEBACK15" },
recipients: todo.map(({ r }) => ({
to: {
name: v(r, "Name").slice(0, 40),
line1: v(r, "Address"),
line2: v(r, "Address 2"),
city: v(r, "City"),
state: v(r, "State"),
zip: zip5(v(r, "ZIP")),
},
merge: { first_name: v(r, "First name").slice(0, 14), last_product: v(r, "Last product").slice(0, 22) },
})),
});
// Same rows give the same body and the same key, so a rerun after a timeout gets the first batch back.
const digest = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, body);
const res = UrlFetchApp.fetch(MAILR + "/batches", {
method: "post",
contentType: "application/json",
headers: {
Authorization: "Bearer " + PropertiesService.getScriptProperties().getProperty("MAILR_API_KEY"),
"Idempotency-Key": "sheets-" + Utilities.base64EncodeWebSafe(digest),
},
payload: body,
muteHttpExceptions: true,
});
const out = JSON.parse(res.getContentText());
if (res.getResponseCode() !== 201) {
throw new Error("Mailr " + res.getResponseCode() + ": " + out.error.message + " " + (out.error.field || ""));
}
// Pieces come back in the same order as the recipients.
todo.forEach(({ row }, i) => sheet.getRange(row, idCol + 1).setValue(out.pieces[i].id));
SpreadsheetApp.getActive().toast(out.count + " postcards queued (" + out.mode + ", $" + out.price + ").");
}- Simple triggers such as onEdit can't use services that need authorization, so the request has to run from the menu or an installable trigger. Installable triggers run as the person who created them.
- Apps Script quotas: 20,000 URL Fetch calls a day on a personal Google account (100,000 on Workspace), 6 minutes per run, and 90 minutes of trigger time a day (6 hours on Workspace). The mail merge uses one fetch for up to 1,000 rows.
- A batch is all or nothing: one bad ZIP stops the whole request, and the error names its place in the list, like recipients[12].to.zip for the 13th unsent row. Fix it and run again.
- Script properties keep the key out of the cells, but people who can edit the script can see them. Share the sheet as view or comment where you can.
- Merge values have length limits: first_name 14 characters, last_product 22. The script trims them so a long value can't stop the batch.
What it costs
Each live piece is paid from your Mailr balance, printing and postage included: a 4x6 postcard is $0.85, a 6x9 $0.89 and an 8.5x11 letter $0.95 on pay as you go, less on a plan. Test pieces are free. All prices.
Templates that fit: Win-back (6x11), First-order thank you (6x9).
Questions
Is there a Mailr add-on for Google Sheets?
No. Mailr has no Google Workspace Marketplace add-on. The script on this page lives in your own sheet and calls Mailr's REST API with UrlFetchApp.
How many rows can I mail at once?
1,000 per batch request. For more, run it again: rows that already have a Mailr ID are skipped, so each run takes the next 1,000.
What if I run it twice by accident?
Rows with a Mailr ID are skipped. If the first run timed out before it wrote the ids, the second sends the same body with the same Idempotency-Key within 24 hours and gets the first batch back, so nothing prints twice.
Can I send letters instead of postcards?
Yes. Set kind to "flat", size to "8.5x11" and a flat template such as tmpl_welcome, with its merge fields (first_name, company, founder_name) as columns.