Generate scannable QR codes in cells

Northwind runs a few events a year, and every attendee needs a badge with a scannable QR code — usually pointing at a check-in URL or carrying their registration ID. The same problem turns up for asset tags on equipment and for product labels in the shop. The slow way is to paste each value into a QR generator website, download the image, and drop it into a cell. For a list of two hundred attendees that is an afternoon nobody wants.

A custom function fixes this. =QR() takes a cell value, builds a QR-code image URL from it, and lets Sheets render the code inline. Type the formula once, fill it down the column, and every row has its own code. When the source data changes, the codes update with it.

What you’ll need

• A Google Sheet with the values you want encoded in a column — attendee IDs, URLs, SKUs, anything that fits in a QR code.
• A header in row 1 so the formula starts on row 2.
• Nothing else. The function calls a public QR-code service, so there is no API key and no setup beyond pasting the code into the Apps Script editor.

The script

// The public QR-code image service. It takes the data and a size, and
// returns a PNG — no account or key needed.
const QR_API_BASE = 'https://api.qrserver.com/v1/create-qr-code/';

// Default edge length of the QR image, in pixels, when the caller does
// not pass a size.
const DEFAULT_SIZE = 200;

/**
 * Builds a QR-code image URL from a cell value. Wrap it in =IMAGE() to
 * render the code inline, or use it bare to store the URL.
 *
 * @param {string} text The value to encode (an ID, URL, or label).
 * @param {number} [size] Edge length of the square image, in pixels.
 * @return {string} A URL pointing at a QR-code PNG, or '' for a blank cell.
 * @customfunction
 */
function QR(text, size = DEFAULT_SIZE) {
  // 1. Blank cells produce a blank result — never a broken image.
  if (text === '' || text === null || text === undefined) return '';

  // 2. encodeURIComponent escapes spaces, slashes, and ampersands so the
  //    data survives being placed in a URL.
  const data = encodeURIComponent(String(text));

  // 3. Assemble the request URL. Size is given as WIDTHxHEIGHT.
  return `${QR_API_BASE}?size=${size}x${size}&data=${data}`;
}

How it works

1. QR receives the cell value and an optional pixel size. Because it is tagged @customfunction, Sheets exposes it as a formula you can type like any built-in function.
2. If the cell is empty, it returns an empty string straight away. Without that guard, a blank row would still build a URL and Sheets would show a broken image.
3. encodeURIComponent escapes any characters that are unsafe in a URL — spaces become %20, ampersands %26 — so a value like Booth A & B still encodes cleanly.
4. The function stitches the service base, the size parameter, and the encoded data into a single URL and returns it. The QR-code service renders the PNG on demand when the URL is fetched.

Example run

Say column A holds attendee check-in URLs:

Attendee	Check-in URL (A)	QR code (B)
Priya Shah	https://northwind.studio/checkin/4821	scannable code
Tom Lyle	https://northwind.studio/checkin/4822	scannable code

In B2 you type =IMAGE(QR(A2)) and fill it down. Each cell renders a QR code that, when scanned, opens that attendee’s check-in URL. To get the raw URL instead — handy for a mail merge — type =QR(A2, 300) for a larger 300-pixel image.

Use it

In the cell where you want the code to appear:

=IMAGE(QR(A2))

IMAGE() is the built-in Sheets function that turns a URL into a displayed picture. To get the URL itself rather than a rendered image, or to control the size, call QR directly:

=QR(A2, 300)

Fill the formula down the column and every row gets its own code. The codes recalculate whenever the source cell changes.

Watch out for

• IMAGE() re-fetches the picture every time the sheet recalculates. On a long list this is slow and hammers the QR service. Once the codes are final, copy the column and Paste special → Values only, or store the static URLs in a plain column and stop recalculating.
• The QR service is a free public endpoint with no uptime guarantee. For badges you must have on the day, generate the codes a few days early and paste them as values so a service outage cannot break your event.
• QR codes have a capacity limit. A long URL or a big block of text produces a dense code that cheap phone cameras struggle to scan. Keep the encoded value short — an ID or a shortened URL scans far more reliably than a 200-character string.
• Custom functions cannot call services that require authorisation, and they run anonymously. That is fine here because the QR endpoint is public, but do not encode anything sensitive — the data travels in a plain URL.