And I\u2019m going to show you two methods:<\/p>\n
- \n
- The manual way<\/strong>, where you generate the QR codes yourself and add them to your Google Sheet.<\/li>\n
- The automated way<\/strong>, where the Google Sheet will automate adding QR codes for you. (This way is a bit more technical, but has fewer steps.)<\/li>\n<\/ol>\n
Mail Merge QR Codes: Table of Contents<\/h2>\n
- \n
- Before You Begin: Google Drive Permissions and Folders<\/a><\/li>\n
- Option 1: Manually Creating and Entering Your QR Codes<\/a><\/li>\n
- Option 2: Having Google Sheets Generate QR Codes, Upload Them, and Create Image Links for You<\/a><\/li>\n
- The Last Step: Sending Your Mail Merge Email with Personalized QR Codes<\/a><\/li>\n
- Mail Merge QR Codes: Next Steps<\/a><\/li>\n<\/ul>\n
Before You Begin: Google Drive Permissions and Folders<\/h2>\n
In this article, I\u2019m going to use Google Drive as a place to store and host my mail merge QR codes<\/strong>. It\u2019s generally the tidiest solution and keeps everything \u2014 your sheet of contacts, your email campaign, and your QR code images \u2014 inside of Google\u2019s ecosystem together.<\/p>\n
And you\u2019ll need to give GMass permission to access your Google Drive<\/strong>\u00a0to insert the QR codes in your emails. Plus you need a folder on your Google Drive to store those QR codes.<\/p>\n
So we\u2019ll take care of both of those steps first.<\/p>\n
Giving GMass permission to grab the QR code images from Google Drive<\/h3>\n
At GMass, never want to ask your permission for something we don\u2019t need<\/a>\u00a0\u2014 which is why we only ask for Google Drive permission if you\u2019re planning to use it. In this case, you are.<\/p>\n
So before you get started, click this link<\/a>\u00a0to give GMass the proper Drive access permissions<\/strong>.<\/p>\n
If you want to host your QR code images elsewhere, like Dropbox or your own server, you can do that as well. (Check out instructions in our personalized images<\/a>\u00a0article.) However, for this demo, we\u2019re using Google Drive.<\/p>\n
Creating a folder in Google Drive to store your QR code images<\/h3>\n
Go to Google Drive<\/a>. Navigate to wherever you want to create your folder for the QR codes. (I\u2019m just putting mine in the default My Drive folder.)<\/p>\n
Click on the New<\/strong>\u00a0button in the top left, then choose New Folder<\/strong>.<\/p>\n
![\"Create](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualnewfolder-tinified.png\")
<\/a><\/figure>\nGoogle Drive is now ready to go<\/strong>.<\/p>\n
Time to make some QR codes.<\/p>\n
Option 1: Manually Creating and Entering Your QR Codes<\/h2>\n
For the first option, I\u2019m going to show you how you can manually create QR codes<\/strong>\u00a0and\u00a0put them into your Google Sheet of contacts to send out a mail merge email with those personalized codes in GMass.<\/p>\n
Create your QR codes<\/h3>\n
The easiest way to prepare QR codes at scale is to enter the QR code data into a spreadsheet, export the sheet as a CSV file, then upload it to a bulk QR code generator<\/strong>.<\/p>\n
QRExplore.com has a free bulk generator<\/a>\u00a0for creating up to 100 QR codes at a time<\/strong>. Based on my research, that\u2019s about as good as you\u2019re going to do for free.<\/p>\n
Prepare your data by creating a Google Sheet with two columns<\/strong>: the URL you want each QR code to link to, and a file name for that QR code.<\/p>\n
Here\u2019s how I did it.<\/p>\n
Open the spreadsheet for my campaign featuring the contacts. It has the URLs I want to use in each of their QR codes in the URL <\/strong>column.<\/p>\n
Then use Google Sheets functions to create file names with each contact\u2019s first name plus a random number. (My formula was
=A2&\"-\"&ROUND(RAND()*10000000,0)<\/code>.)<\/p>\n![\"Create](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualfilenames-tinified.png\")
<\/a><\/figure>\nCopy the URL column and file names columns to a new sheet<\/strong>\u00a0(Sheet2) and paste the results <\/strong>as values<\/strong><\/em>.<\/p>\n
![\"Paste](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualpastevalues-tinified.png\")
<\/a><\/figure>\nNow download Sheet2 as a CSV file<\/strong>.<\/p>\n
![\"Download](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualcsv-tinified.png\")
<\/a><\/figure>\nAt QRExplore\u2019s Bulk QR Code Generator, drag the CSV into the QR code window<\/strong>\u00a0to upload it.<\/p>\n
![\"Upload](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-uploadcsv-tinified.png\")
<\/a><\/figure>\nWhen all looks correct, generate your QR codes<\/strong>.<\/p>\n
![\"Generate](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-generatecodes2-tinified.png\")
<\/a><\/figure>\nThen download the zip file<\/strong>\u00a0of all the codes.<\/p>\n
![\"Download](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-downloadcodes-tinified.png\")
<\/a><\/figure>\nUnzip the file<\/strong>\u00a0and you should have all the PNG files ready to upload.<\/p>\n
Uploading the QR codes to Google Drive<\/h3>\n
Go back to Google Drive and open the folder you created to store your QR code images<\/strong>.<\/p>\n
Now drag in the QR code images<\/strong>\u00a0from your hard drive. Don\u2019t drag in the zip file, drag in the individual QR images.<\/p>\n
![\"Drag](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualdragin-tinified.png\")
<\/a><\/figure>\nNext we have to make sure anyone has permission to view these files<\/strong>. Select all three files. (Click on the one at the top of the list, hold the Shift key, then click on the one at the bottom of the list.)<\/p>\n
Right click to bring up the options menu. Choose Share<\/strong>.<\/p>\n
Under General access<\/strong>, switch from Restricted<\/strong>\u00a0to Anyone with the link<\/strong>. Give anyone with the link Viewer<\/strong>\u00a0permission. (That\u2019s the default.)<\/p>\n
![\"Give](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualpermissions-tinified.png\")
<\/a><\/figure>\nNext hit the Copy links<\/strong>\u00a0button on the bottom left of the window.<\/p>\n
Once it tells you the links are copied, click the Done<\/strong>\u00a0button.<\/p>\n
Adding the QR code links to your Google Sheet<\/h3>\n
Now return to your Google Sheet of contacts. Create a new sheet, Sheet3. This is where we\u2019ll prep the links from Google Drive for the mail merge<\/strong>.<\/p>\n
We have to do a few annoying copy\/paste techniques because of how Google Drive prepares the copied files<\/strong>. It turns them all into a comma-separated list.<\/p>\n
So we\u2019ll need to\u2026<\/p>\n
- \n
- Click into cell A1 on Sheet3 and hit paste.<\/li>\n
- In cell A2, enter this formula:
=SPLIT(A1,\", \")<\/code>. Don\u2019t forget the space after the comma that\u2019s inside the quotes.<\/li>\n- Select everything in the second row. Click Copy.<\/li>\n
- In cell A3, click Edit > Paste special > Values only<\/strong>.<\/li>\n
- Select everything in the third row. Click Copy.<\/li>\n
- In cell A4, click Edit > Paste special > Transposed.<\/strong><\/li>\n<\/ol>\n
Now we have each Google Drive link on its own line<\/strong>.<\/p>\n
![\"The](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualimport-transposed-tinified.png\")
<\/a><\/figure>\nDelete rows 1, 2, and 3<\/strong>. That will leave you with just the URLs as they\u2019re supposed to be.<\/p>\n
Once that\u2019s done, we can now adapt these for mail merge. Because, you see, Google didn\u2019t give you direct links to the images<\/strong>. They gave you links to Google Drive pages<\/em>\u00a0featuring those images. And to mail merge QR codes (or any images), we need to grab the image links themselves.<\/p>\n
Yes, I recognize this is cumbersome.<\/p>\n
In B1, type in the following formula:
=MID(A1,33,(FIND(\"\/\",A1,33)-33))<\/code>. We\u2019re extracting the file ID from the string. We know it starts at the 33rd character and we\u2019re accounting for the possibility it could be any length. Then drag that formula down to the rest of the rows<\/strong>.<\/p>\n![\"Extract](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-extractid-tinified.png\")
<\/a><\/figure>\nWe\u2019re ready to build our mail merge links.<\/p>\n
In C1, type the following
=CONCATENATE(\"<img src=\"\"https:\/\/drive.google.com\/uc?export=view&id=\",B1,\"\"\">\")<\/code> . You need all those double quotes in there because that\u2019s how you escape quotation marks in this particular Google Sheets scenario. Now drag that formula down to the rest of the cells in the column.<\/p>\n![\"Create](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-makeimagelinks-tinified.png\")
<\/a><\/figure>\nAnd finally<\/em>, copy everything in column C<\/strong>.<\/p>\n
Go to Sheet1. Create a new column called ImageLink<\/strong>. Then, in the second row of that column, use Edit > Paste special > Values only<\/strong>\u00a0to paste in your ready-for-mergin\u2019 QR code links.<\/p>\n
![\"Paste](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualpastelinks-tinified.png\")
<\/a><\/figure>\nYou can also delete the QRFilename<\/strong>\u00a0column, as we won\u2019t need that anymore. Which leaves us with a glorious final result of\u2026<\/p>\n
![\"How](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-manualfinalsheet-tinified.png\")
<\/a><\/figure>\nYou can also delete Sheet2 and Sheet3 if you want.<\/p>\n
And you’re ready to send… so you can now skip down to the section<\/strong>\u00a0on sending your mass email with personalized QR codes.<\/p>\n
Option 2: Having Google Sheets Generate QR Codes, Upload Them, and Create Image Links for You<\/h2>\n
Here\u2019s the other way to add QR codes to your mail merge campaigns: Let the machines handle most of the steps from above.<\/p>\n
I wrote a Google Apps Script that automatically generates QR code<\/strong>\u00a0for each contact in your Google Sheet using the free API from goQR.me<\/a>\u2026 uploads those images to your Google Drive\u2026 gives the images proper viewer permissions\u2026 then writes formatted image links back to your Google Sheet.<\/p>\n
Create a Google Sheet with a few extra columns<\/h3>\n
Create a new spreadsheet in Google Sheets, or use your current spreadsheet. The URL<\/strong>\u00a0column is the link you want for your QR code.<\/p>\n
I\u2019ve also created two new columns at the end: QRCode<\/strong>, which will be filled in with the actual images of the QR codes so you can preview them\u2026 and ImageLink<\/strong>, which will be filled in with the ready-to-merge image link.<\/p>\n
Note: If you use different column names, you\u2019ll need to change the Google Apps Script to reflect those column names. You\u2019ll do that in lines 11-14 of the Script if needed.<\/em><\/p>\n
![\"Setting](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qu-autosheetsetup-tinified.png\")
<\/a><\/figure>\nCreate the Google Apps Script (it\u2019s a copy\/paste job)<\/h3>\n
In Google Sheets, go to Extensions > Apps Script<\/strong>.<\/p>\n
![\"Select](\"https:\/\/www.gmass.co\/blog\/wp-content\/uploads\/2023\/01\/qr-extensionsappsscript-tinified.png\")
<\/a><\/figure>\nThat will bring up the Apps Script interface.<\/p>\n
You can replace Untitled Project<\/strong>\u00a0with whatever you want (I named mine \u201cQR Code Generator\u201d).<\/p>\n
Delete everything in the script window (
function myFunction () { \u00a0}<\/code>).<\/p>\nThen copy the entire script below and paste it in.<\/p>\n
See the Pen
\nUntitled<\/a> by Sam G (@samgmass1<\/a>)
\non CodePen<\/a>.<\/p>\n
- Option 1: Manually Creating and Entering Your QR Codes<\/a><\/li>\n
- The automated way<\/strong>, where the Google Sheet will automate adding QR codes for you. (This way is a bit more technical, but has fewer steps.)<\/li>\n<\/ol>\n