How to Generate and Track QR Codes in Google Sheets (Step-by-Step)

Published on | ProSheetHub

QR codes have become an essential bridge between offline physical items and digital workflows. Whether you are generating event check-in passes, printing asset labels for warehouse equipment, linking packaging to product specification sheets, or building marketing campaigns, generating QR codes in bulk is a huge time-saver.

Instead of manually creating QR codes one by one through online generator websites, you can generate hundreds of dynamic, scannable QR codes directly inside Google Sheets using a single native formula. This step-by-step guide covers how to generate QR codes automatically, customize their design, create vCard and Wi-Fi QR codes, and track scan analytics using UTM parameters.

Step 1: The Master QR Code Generator Formula

Google Sheets renders web images inside cells using the IMAGE function. By pairing IMAGE with a reliable QR code generator API (like QuickChart or QRServer) and the ENCODEURL helper function, you can turn any URL, text, or phone number into a QR graphic instantly.

If your target web link or product SKU is in cell A2, enter this formula in cell B2:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & ENCODEURL(A2))

Formula Parameter Breakdown:

  • IMAGE(...): Tells Google Sheets to fetch and display an image URL directly within the bounds of the cell.
  • size=150x150: Defines the pixel dimensions (width $\times$ height) of the generated QR code graphic.
  • ENCODEURL(A2): Translates special characters (like ?, =, &, and spaces) inside cell A2 into web-safe URL formats, preventing broken images.

Step 2: Customizing Size, Colors, and Error Correction

You can customize the appearance and reliability of your QR codes by passing additional parameters to the API URL string.

1. Custom Colors (Foreground & Background)

To change the default black-and-white style to match your corporate branding, pass hex color codes (excluding the # symbol):

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=200x200&color=0066cc&bgcolor=f0f4f8&data=" & ENCODEURL(A2))

2. Error Correction Level (ECC)

Error correction allows a QR code to remain scannable even if part of it is smudged, torn, or covered by a logo. The API supports four levels: L (7%), M (15%), Q (25%), and H (30% damage recovery):

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=200x200&ecc=H&data=" & ENCODEURL(A2))
Design Tip: When printing QR codes on physical packaging or asset tags exposed to outdoor wear and tear, always set ecc=H to ensure maximum readability for phone cameras.

Step 3: Generate Specialized QR Codes (Wi-Fi, vCard, Email)

QR codes aren't limited to website URLs—you can structure raw cell text to trigger specific smartphone actions when scanned:

QR Code Type Data Format String Result When Scanned
Wi-Fi Network WIFI:S:MyNetwork;T:WPA;P:SecretPassword;; Prompts smartphone to connect to Wi-Fi automatically.
vCard (Contact) BEGIN:VCARD\nN:Smith;John\nTEL:5550192\nEND:VCARD Prompts user to save contact directly to phonebook.
SMS Message SMSTO:+15550192:Hello from ProSheetHub Opens messaging app with pre-filled recipient & text.
Email Link mailto:support@prosheethub.com?subject=Inquiry Opens default email app with recipient & subject set.

Step 4: Track QR Code Scans with UTM Parameters

Standard QR codes printed on flyers, posters, or business cards don't provide native scan analytics. To track how many visits, leads, or sales originate from a specific physical QR code, attach UTM Campaign Parameters to your URL before encoding it.

Formula to Auto-Construct Tracked URLs:

In cell B2, combine your base website link with tracking tags:

=A2 & "?utm_source=qr_print&utm_medium=poster&utm_campaign=summer_sale"

Then reference cell B2 inside your QR formula in cell C2:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=" & ENCODEURL(B2))

When users scan the QR code, Google Analytics (or your website traffic tool) will log the visit under the source qr_print and campaign summer_sale, allowing you to measure offline conversion rates accurately.

Troubleshooting Common QR Code Display Issues

  1. QR Code Appears Tiny or Blurry:
    Google Sheets scales images to fit the active row height and column width. Expand the row height (e.g., set row height to 120px) and column width to give the QR graphic room to render clearly.
  2. Formula Returns #VALUE! Error:
    Ensure you wrapped the target cell reference inside ENCODEURL(). Unescaped characters like spaces or ampersands (&) in raw URLs break image API requests.
  3. Camera Fails to Scan Printed Barcode:
    Maintain high contrast between foreground and background colors. Always use a dark foreground (e.g., navy or black) over a light background (white or light grey). Reversing colors (light QR code on dark background) often fails on mobile scanners.

Comments

Post a Comment