Spreadsheets and files
Barcode generator for Google Sheets
One formula draws the barcode in the cell. No add-on.
Three steps, once
-
Create a picture key
Sign in and open the Account page. The key is shown once, so copy it.
-
Paste it in Z1
Z is out of the way of your data. The formula reads the key from there, so you paste it once per spreadsheet.
-
Put the formula in B2, fill down
Sheets draws one barcode per row. No add-on, no Apps Script, nothing to authorise.
You generate barcodes in Google Sheets with the same IMAGE formula Excel uses. Put the code in column A, your picture key in Z1, and the formula next to the code. Sheets fetches the picture and shows it in the cell. A valid GTIN becomes a retail barcode, a web address becomes a QR code, and anything that cannot be a barcode shows a card with the reason. There is no add-on to install.
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 do I generate a barcode from a cell?
- Sign in, open the Account page, and create a picture key. Copy it, because it is shown once.
- Paste the key into Z1.
- Select column A and choose Format, Number, Plain text. Then paste your codes, starting in A2. Plain text keeps leading zeros.
- Put this formula in B2:
=IMAGE("https://beepable.app/i/auto.png?v="&ENCODEURL(A2)&"&k="&$Z$1) - Fill the formula down. $Z$1 stays on the key in every row.
- Make the rows taller until the bars are clear, and scan one barcode from the screen before you print.
For internal SKUs, put code128 in the formula instead of auto:
=IMAGE("https://beepable.app/i/code128.png?v="&ENCODEURL(A2)&"&k="&$Z$1)Which barcode type comes back?
| Value | Barcode with auto |
|---|---|
| 8, 12, 13, or 14 digits with a valid check digit | EAN-8, UPC-A, EAN-13, or ITF-14 |
| A GS1 Digital Link | A QR code. Build the link from a GTIN on the Digital Link page. |
| Any other web address | A QR code |
| A SKU or other text | A card, until you put a type such as code128 in the formula |
A fixed type never switches. If you write ean13 and the cell has 12 digits, the card says how many digits EAN-13 needs. Add hrt=1 to print the digits under an EAN-8, EAN-13, or UPC-A:
=IMAGE("https://beepable.app/i/auto.png?v="&ENCODEURL(A2)&"&hrt=1&k="&$Z$1)What does a bad row look like?
| The cell shows | Why | What to do |
|---|---|---|
| Check digit is invalid for this GTIN. Name code128 to encode it anyway. | The last digit does not match the other digits. | Copy the code again from its source. Beepable never makes up a check digit. |
| A retail barcode needs 8, 12, 13, or 14 digits. This has 11. Name code128 to encode it anyway. | The cell has too few or too many digits, often because a leading zero was dropped. | Format the column as text and type the code again. |
| This is neither a valid GTIN, an Amazon FNSKU, nor a URL. Name a symbology, such as code128. | The value is text, such as a SKU, and the formula says auto. | Put code128 in the formula instead of auto. |
| This barcode needs an API key. | Z1 is empty, or the formula does not point at it. | Paste your picture key in Z1, and keep both dollar signs in $Z$1. |
| This API key has been revoked. | The key was revoked on the Account page. | Create a new picture key and paste it in Z1. |
| A broken picture, with no card | Too many new barcodes were requested at the same moment. | Wait a minute, then recalculate the sheet. |
An empty cell shows: Enter a GTIN or a URL. Cards are never saved, so a fixed row shows its barcode on the next recalculation.
When would I use the Apps Script?
You do not need a script to draw barcodes. The optional script adds a function, BARCODE, that returns the error as text instead of a card, which is handy when you want to filter bad rows. To install it:
- Open the script and copy all of it.
- In your spreadsheet, choose Extensions, Apps Script.
- Paste the script, save, and reload the spreadsheet.
- Put an All routes key in Z3. BARCODE refuses a picture key with: This key can only draw pictures.
- Use this formula. It adds the picture key from Z1 to the address, so the All routes key never appears in a picture address:
=IMAGE(BARCODE(A2,"auto",$Z$3)&"&k="&$Z$1)
Who else can see the key?
Anyone who can open the spreadsheet can copy the key in Z1 and read every value. Share the file only with people who should have that key, and keep private links out of the sheet. If a copy leaves your company, revoke the key on the Account page and paste a new one in Z1. The old barcodes stop drawing on the next recalculation.
Frequently asked questions
Do I need a Google Sheets add-on?
No. Put the code in A2, a picture key in Z1, and the IMAGE formula from this page in B2. There is nothing to install.
How do I change the barcode type?
Replace auto in the formula with a type such as ean13 or code128. If the value does not fit that type, the cell shows a card that says why. Beepable never switches to another type on its own.
Can one column mix GTINs and URLs?
Yes, with auto. A valid GTIN becomes EAN-8, UPC-A, EAN-13, or ITF-14 depending on its length. A web address, including a GS1 Digital Link, becomes a QR code.
Can I print the digits under the bars?
Yes, for EAN-8, EAN-13, and UPC-A. Add &hrt=1 before &k= in the formula.
When do I need the Apps Script?
Only when you want the error as text in the cell instead of a card. The script needs an All routes key in Z3, and it still uses the picture key in Z1 for the picture.
Keep going
