Sunrise 2027
Create GS1 Digital Link QR codes from a GTIN column in Excel or Google Sheets
One formula turns a GTIN column into Digital Link QR codes, with the check digit checked.
You create GS1 Digital Link QR codes from a GTIN column with one IMAGE formula. Beepable builds the link from each GTIN, checks its check digit, and draws the QR code. A wrong digit shows a card instead of a code, so a bad GTIN never reaches print. Put the GTIN in column A, an optional lot in B and expiry in C, your picture key in Z1, and the domain in Z2.
Put a picture key in Z1. Create it on the Account page. Never put an All routes key in a sheet: anyone who can see the formula can copy it.
How is the sheet laid out?
- Format column A as text, then paste the GTINs from A2 down. Text keeps leading zeros.
- Paste your picture key in Z1.
- Put the domain in Z2. Use https://id.gs1.org until your own domain is ready; it must start with https.
- Put a lot in column B and an expiry date in column C only for units that have them. The date is YYMMDD or YYYY-MM-DD.
- Put this formula in D2 and fill it down:
=IMAGE("https://beepable.app/i/dl.png?gtin="&ENCODEURL(A2)&"&lot="&ENCODEURL(B2)&"&exp="&ENCODEURL(C2)&"&domain="&ENCODEURL($Z$2)&"&k="&$Z$1) - Scan one QR code with a phone. The link should open, and the 14 digits after /01/ should match column A with leading zeros.
The key is in Z1 rather than B1 because column B holds the lot. ENCODEURL keeps a lot with special characters inside the address.
What about leading zeros?
A 13-digit GTIN such as 9506000134352 becomes 09506000134352 in the link. Adding zeros at the front does not change the check digit, because the digits are weighted from the right. A sheet that stored the GTIN as a number has already dropped its zero before the formula runs, so format the column as text and type the codes again if the link shows the wrong number.
A text formula can build the address without Beepable:
="https://id.gs1.org/01/"&TEXT(A2,"00000000000000")It pads the GTIN, but it does not check the digit, so it will happily build a link for a wrong GTIN. Use it only to preview addresses for codes you already trust. Print from the IMAGE formula.
How do I add a lot and an expiry?
| Field | GS1 key | Rule |
|---|---|---|
| Lot (column B) | 10 | 1 to 20 characters from the GS1 character set; no spaces |
| Expiry (column C) | 17 | YYMMDD or YYYY-MM-DD; month 01 to 12; the day may be 00 |
| Best before | 15 | Same format; not in this formula; use the file upload |
| Serial | 21 | Same characters as the lot; not in this formula |
Leave a cell empty when the unit does not have that field. Every extra field changes the link, and so the QR code and what a phone opens. A wrong date shows: The date must be YYMMDD or YYYY-MM-DD. Use a month from 01 to 12. The day may be 00.
What if a cell shows a card?
| The cell shows | What to do |
|---|---|
| The check digit is invalid for this GTIN. | Check the GTIN in column A against its source. |
| A GTIN needs 8, 12, 13, or 14 digits. This has 12. | A digit is missing, often a leading zero. Format the column as text. |
| The domain must be an https address, such as https://id.brand.com. | Fix Z2. |
| This barcode needs an API key. | Paste your picture key in Z1. |
| Use a picture key, not an All routes key, in image addresses. | Replace the key in Z1 with a picture key. |
When is a file upload better?
When you already have a product file and want the links back as data. Upload it on the Digital Link page. The GTIN column may be called gtin, ean, upc, or value; the lot column lot or batch; the expiry expiry, exp, or 17; the best-before date best_before or 15; and a domain column is optional. You get the same file back with digital_link, element_string, qr_url, ean_url, and error columns. One bad row does not stop the others.
A copied link is fixed, while the formula follows the cell. Use the formula when GTINs still change, and the file when the catalog is final.
Frequently asked questions
Which columns do I need?
The GTIN in column A, an optional lot in B, an optional expiry in C, the formula in D, your picture key in Z1, and the domain in Z2.
Does the formula check the check digit?
Yes. A wrong digit shows a card with: The check digit is invalid for this GTIN. A TEXT formula that only pads to 14 digits does not check it.
How do I keep a leading zero?
Format the GTIN column as text before you paste. A number cell drops the zero, and the link then has the wrong GTIN.
Can I upload a file instead?
Yes. Upload the file on the Digital Link page. It returns the link, the element string, and the picture addresses for each row.
Which key goes in the sheet?
A picture key, in Z1. An All routes key is refused in a picture address with: Use a picture key, not an All routes key, in image addresses.
Keep going
