Sheets to Docs Mail Merge

TL;DR: I needed a way to perform a mail merge, passing data from a Google Sheet over to a Doc. Nearly everything works, with the exception of two complicating factors – the template file I’m copying includes a table, and one of the table cells must contain an image. I’m stumped!


If you’re anything like me, when you think “mail merge” you don’t think “G Suite”. For years I’ve been mail merging with Excel and Word because…it’s easy. You open the wizard, you click some buttons, and poof – labels, envelopes, whatever. But a recent project requiring a QR code API and product labels has me digging into the depths of G Suite for a solution that will allow me to get the QR codes via an API and then feed the code along with some product data from a Google Sheet.

Pulling in the QR codes was a piece of cake.

// Writes images to a spreadsheet
function generateCodes() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var firstRow = 2;
var lastRow = sheet.getLastRow();
var barCol = 2;
var codeCol = 8;

for (var i = firstRow; i <= lastRow; i++) {
var currentValue = sheet.getRange(i,barCol).getValue();
var url = "https://api.qrserver.com/v1/create-qr-code/?size=50x50&color=000000&margin=2&bgcolor=255-255-255&data=" + currentValue;
var urlRequest = UrlFetchApp.fetch(url).getBlob();
var qrValue = sheet.insertImage(urlRequest,codeCol,i);
}
}

// Deletes all images in a spreadsheet
function clearCodes() {
var images = SpreadsheetApp.getActiveSpreadsheet().getImages();
for (var i = 0; i < images.length; i++) {
images[i].remove();
}
}

After writing that code, I realized it would be even easier to just insert a Sheets formula – I don’t really need to use a FOR loop to write images to the spreadsheet, just point to their location. Just use something like:

=arrayformula(image(hyperlink("https://api.qrserver.com/v1/create-qr-code/?size=100x100&color=000000&margin=2&bgcolor=255-255-255&data=" & B2:B9)))

That formula grabs the base URL for the QR code API, tacks on the barcode or product ID from column B, and spits out the QR code itself into an array of cells.

Now that the QR codes are created, it’s time to mail merge. This is where things get tricky.

There are a couple of decent add-ins already out there that will do this – autoCrat was the best that I found. It had a slick interface, a lot of customization, and best of all – it worked. But how does it work? I needed to find out. So I did some digging to see if I could write some code that would do basically the same thing, just without most of the polish.

I found this StackExchange question and answer where a user basically laid out exactly what needed to happen to create a basic mail merge. They even went as far as to dump the code in Github. So I ran the code, and it almost all worked – everything except the QR code passed through.

I logged some test results, and I figured out that the script sees my template as a paragraph, a table, and another paragraph element. It’s writing text to the paragraphs elements just fine, but when it comes to writing the QR code to the table, it’s not producing the intended result. If I change the field that I’m trying to write to the table – say, to regular text – then it works. So there has to be something about the QR code image that this script doesn’t like.

} else if (child.getType() == DocumentApp.ElementType.TABLE) {
mergedDoc.appendTable(child);

Maybe I’m writing the table to the Doc, but the image isn’t being written to the cell? Or maybe the problem is in the FOR loop that overwrites the existing template contents?

for (var f = 0; f < fieldNames.length; f++) {
body.replaceText("\\[" + fieldNames[f] + "\\]", row[f]);//replace [fieldName] with the respective data value
}

I’m at a loss. What do you think?

Advertisements

Published by Doug

Owner, WaltersWorks.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: