Print Barcode Labels Using ONLY Google Sheets
Vložit
- čas přidán 26. 09. 2020
- A little setup on Google Sheets and you can print barcodes (along with whatever else you want) onto label sheets WITHOUT messing with mailmerges or barcode fonts that make unreadable barcodes!
UPDATE: It looks like enough people were using these types of sites for this that many of them have monetized when you try to do more than a certain number. Good for them - why shouldn't they make a little money off of their own hard work?! A few of these of have come and gone, or changed their setup since I first made this. Just recognize that, by the time you look into this, you may need to find a different website for barcode creation, or potentially pay some money to the web service if you need to do large numbers.
I spent weeks printing barcodes using a mail merge with Excel and Word, converting numbers to barcodes using a barcode font. There are a few big problems with this. First, mail merges take some time to set up EVERY SINGLE TIME. Second, Microsoft Word has formatting characters that are invisible in normal fonts but become visible in barcode fonts, making the barcodes unreadable. Getting rid of those invisible characters requires some sneaky tactics, not to mention the need for particular special characters to start and end the data being turned into a barcode. Third, sharing files created that way is a pain because everyone you share it with has to download the same font you had to make it readable, or you have send a pdf which isn't editable. Fourth, barcode fonts are limited to codes that convert one character at a time, so something like a QR code can't be generated with a special font.
This method avoids all of those problems - no mail merge, no barcode fonts, and the versatility to use whatever barcode (or QR code) type you want.
This makes use of online barcode generator sites and the Google Sheets "IMAGE()" function. Effectively, you are outsourcing the actual code creation to a website that somebody else already made and bringing back an image file, so you won't have to mess with anything to get the barcode to read correctly!
Here's the link to the file I made - feel free to make a copy and use it for your labels. It might take a little adjustment to get it to print correctly with your printer, but all the formulas for either barcodes or QR codes are already done.
docs.google.com/spreadsheets/...
The first couple minutes is setup and problems with other methods I've seen - jump to 03:50 for the start of my method.
*Absolutely love the **Bestt.Digital** Label Printer! I have needed a label printer to make shipping orders so much easier but the fact that i got it in such a pretty color. Top notch! Prints nicely*
Such a help! So easy even I understood it. Many less tears were shed. Thank you!
Nice work, thanks for showing the steps and including the link to the demo sheet. My Mother-in-law just asked for help with labels she needs for a small church library and I had done this before with Excel/Word mail merge but I like your way and will try it myself next time.
Thanks a ton. This is a comprehensive guide from setting up till getting the print out. Made my life easier.
This is awesome! I used it for a store where we use Square. I can now create my own barcodes in Google Sheets & scan them (UPC-A format) into Square with a barcode scanner & ring up sales into Square with the barcode scanner. I have looked everywhere on how to do this. Thank you so much....
im struggling so much with this right now. im desperate to get it to work.!!
You're a genius. I've spent many time looking for this video. Thank's!!!!!!!!!!
Great tutorial for Print Barcode Labels Using ONLY Google Sheets, simple and clear.
This was great, thanks a ton. I have a question, if I use a dollar amount as one of the data inputs it does not copy the $ sign to the barcode page in text. Is there a way to make it transfer the $ symbol to the barcode sheet? I have no experience with formulas or coding. And Google isn't helping.
Extremely helpful video. Thank you so much Andrew Benett.
Hey, thank you for the video! Very informative. I've managed to follow all your steps successfully, however the one difference is i'm printing barcodes to place onto product packaging. I have a thermal label printer, which i've purchased 1x2inch labels for. Is there any way you would be able to show us how to print 1 barcode at a time for the designed quantity, with a thermal label printer? Thank you!
Thanks, This was very helpful. Got my Amazon UPC labels ready.
Thank you for this. Im using it now for my Loyverse POS
Thanks man, you've just saved me so much trouble.
Might try to use that for shopping lists! Thanks.
OMG....Thank you so much, this is going to help my small business so much :)
How you scan the code to Google sheet at the end?
Thank you! This method helped me allot !
This is GENIUS!! Thank you so much!
Thank you very much! This video and template helps me a lot.
thank you very very much i have been looking for this information for a long time. You even attached a link to download )). Wish you all the best Sir..
you sir are a gentleman and a scholar. Thankyou!
Absolutely genius. Thanks!
Brother, you literally solved a huge problem for me. Cheers. Thank you.
Thank you for this configuration, I am using it to control my PC inventory
A muito tempo procurava uma solução para gerar o código de barras a partir do Google Sheets. Muito bom. (For a long time I was looking for a solution to generate the barcode from Google Sheets. Very good.).
Thank you Andrew great work 🙏
Hi I just want to ask how did you connect the scanner on your google sheet? Thank you
Thank you so much for the detailed video
Sorry to be so late to the game. Is there a way to eliminate the asterisks around the text? This is a great help. Thanks!
hello. I would like to ask if there is a way to use the same method as when i try to do it, it gives me nothing as there is a dash in the information im trying to enter such as GW R4 - 02 - B
Any idea how to do something like this in Excel?
Great!!!!!! this is amazing .... Can we do the same process on Microsoft Excel ?????? please let me know
Thanks for this video, it works great,but i have a problem, if i decide to make a barcode with a jumpline,how can i do that?for example in your data i need a make barcode with student name and student number,how can i do that?.Can someone help for this problem?
Thank you. Very helpful.
This has been extremely helpful! Been using this for almost 2 years. Unfortunately, my spreadsheet has the same issue that yours has now, I only get an image for the barcode that says "invalid barcode settings" any idea how that might be resolved? I've tried reverse engineering your method from their website, but can't get it to work right.
What scanner are you using?
Thank you, you are awesome!
When we scan the barcodes, how the details get in to google sheet ?
YOU are AWESOME! Thanks!
What label template did you use?
how did you get his barcodes elongated like that?
I'm pretty sure I'm using the same barcode generating site for my code 128 barcodes and you don't need any added line to show the characters in the barcode because the website does provide the option to include characters in the barcode inserted into the image underneath the barcode itself.
I loaded your sample sheet and to make the image include the characters contained in the barcode below the barcode itself is very simple!
Look at your formula sending the data to the website and the only change needed to get the characters to display below the barcode itself is in the formula you used, where the formula says "SHOW-TEXT=false" simply change that "false" to "true" and the website will then generate the barcode and include the characters contained in the barcode in text underneath the barcode.
Hi, I used the method and it work well! I have an issue when printing. I have multiple pages to print and from page 2, margins don't seem to apply. Do you have a solution so the margins are applied to all sheets? Thank you!
Question: I've been looking on the barcodes you made on the first part and it has a label on it (NEWERDATA etc..) but when it comes to the barcodes of the students there is no label. How did you do it? you are using the same formula there right?
On the barcode generator website, change the Show Text to False, then generate the Barcode again. Now copy that URL instead so it only shows the barcode without the text
how do you get barcode in google sheets?
thanks so muchhh. it was helpful and i did it!
Very useful, thanx
Hi thanks for sharing
Is this doable with QR codes?
I want to print this on my label printer. Set the page width to 3.5cm but Google sheet saying minimum 6.5cm as page width. Any solution to that.
Best video ... Thank you for India...
This video was so helpful! Around 06:30 your barcodes display the * TEXT * underneath, but in the next screen at 06:40 the text is no longer underneath, how would I go about making the text hidden?
On the barcode generating website, there is a 'Code 39 Barcode Text Style' heading, and the first line is Show Text, change this to True to display the data under the barcode, False for a BC without text.
They set a License code on the service you are reviewing. Can you fix it?
I have used this for a while and for some reason it is not working now. Is this a glitch? I am stressing out because this has never happened before.
Thank You!
Thanks for sharing
Great video - I have a Square Shop and I need to figure out a way to create barcodes using my SKU that is different on each item. I can export my list to a spreadsheet but doh't know how to print to labels. Can you help me? Thank you so much. I can upgrade to Square Plus and they have a barcode app but it would cost me $60/MO - eeeeeeek:O(
Thank you so much!!!
The Method Shared here was very effective, however now the URL for onbarcode has been changed this is kinda redundant right now. please share update
Hi, Andrew, can I know how to put a price on the barcode pls?
You can choose for USD229.75 or EUR1299,50 but no spaces or for example the € symbol
Hi and thank you!!!! I have a question, if I am just doing barcodes no text can I format one sheet to the sizes needed rather that starting a new page and formatting to transfer data too? Will it work the same? Thanks soooo much!
The downside to that it's that it makes it tougher to change, as yours have to go into each of the 30 formulas individually to make any changes you needed. Keeping the data on another sheet and using a formula to pull values into the barcode formulas simplifies that process.
If you don't need to make changes (say, you just want to print a ton of barcodes work the same value encoded) then there's no big reason to have the data in a separate sheet.
@@AndrewBennettScience Thank you! I will keep the two sheets because I do need to add more data when new products come in! Again thanks so much for this tutorial!
Thank you
im really lost when you try to copy and paste the barcodes and conctanate the rows to print. : l
Great trick! I wrestled with trying to rotate this to a vertical placement with the ROTATE option in the URL, tried 90, 180, -90 etc. Finally discovered it's based on preset 90 degree integer triggers so: "ROTATE=3" worked for me, that rotated it 90 degrees clockwise. ROTATE=0 is default, ROTATE=1 is 90 degrees CC, ROTATE=2 is 180 degrees (upside down), and ROTATE=3 is 90 degrees clockwise. Seems the rotate image function in Sheets won't work with embedded URL images. Hope this helps somebody....
Nice! Thanks for sharing, Ralph! They get a lot of bang for their buck in the URL for that barcode!
Today the generator broke, do you know how to get back to the free code generator?
How would I make the code show up under the barcode? Also amazing video! Thank you!
On the barcode generating website, there is a 'Code 39 Barcode Text Style' heading, and the first line is Show Text, change this to True to display the data under the barcode, False for a BC without text.
Is there a way to make the barcodes not blurry? Thank you!!!
This is not an issue I've seen before. Is it just when you print, or on-screen as well? If it's onscreen, I'd recommend playing with the size settings on the barcode website you're getting the url from. If it's just when you print, it's likely a printer setting issue.
I keep getting an error
What Avery number did you use?
Anything that says the template number is 5160 will work with the formatting of this sheet (there are a few varieties that use that template).
Awesome. Thanks!
i love this tutorial, im trying to get it to work for a thermal label printer instead of those multi label sheets, which i could do with your method, but instead of 30 with some manual labor of cut and copy, i have 1,801 different barcodes i need to print and im afraid that may be a little too much time, are there any work arounds for this?
i think ive worked it out, i found a barcode generator where i can input a header and footer in text and the barcode in the middle and applied the =a2 and =b2 into the link and im just going to print column c with custom margins and "1 per page" seems like its going to work on a quick test batch
That's interesting! Did it work?
@@AndrewBennettScience unfortunately not, after 3 "pages" it crapped out and started printing inconsistently, i found a barcode generator that could take my excel file and print out a massive list on a thermal printer like mine for i think only $30 usd (codefinix) i was trying to avoid spending money at all costs but this was the fastest solution after hours of trying and it wasnt outrageous prices like the big name generator softwares.
@@kevinmorris2959 Hi Kevin, please could you share a link to the barcode generator where you can input footer text?
This one does what I need - www.dcode.fr/barcode-128 - but it does not generate an editable URL, i.e the link does not contain the data entered, so I can not manipulate it in the way we need inside Google Sheets.
ANDREW BENNET YOU BLOODY RIPPERRRRR THIS IS PERFECT
Hey I figured out how to fix the stupid code activation thing the barcode generator added.
1) go to their you tube copy and paste to activate. It then allows you to edit the generator.
2) right click the image of the generated barcode and click open in new tab
3) go to the new tab copy the url
4) go to your google sheet you have been using and highlight the section of the old url and paste in the new one
5) in the url you just added go to the section that says “.aspx?TYPE=4&DATA=“ after the = erase 1234567 that was the place holder sku they had from the image and replace it with “&C2&” (make sure to include the “” around that formula.
5) hit enter and verify it correctly pulled the sku you had in C2. If it’s right just click and drag the cell down and problem solved.
It seems we will have to follow these steps every 2 months as they change their dumb activation code but it still works.
I dont need a pirate barcode generator anymore. Thank you.
Thanks for the video, Andrew.
On czcams.com/video/QZi2pQcxRsw/video.html the barcodes you copied from the website seems to have the text transcript under the barcode
and on your sheet 2 czcams.com/video/QZi2pQcxRsw/video.html you have the barcodes without the text transcript under the barcode. How did you do that?
Thanks in advance
I got it, "showtext" set to false. Thank you very much for the video!
Glad you found it ... I couldn't remember the answer!
Can I pay you to help me transfer my barcodes to Google sheet.
فهو يتفعل الموقع علا الشرح فل فيدي و
What's the Paper format dear author??
8.5 x 11 (all the labels I saw available in the US are on that format). Anyone printing on a different paper size of with a different label template will need to adjust the margins and column and row sizes. I made measurements on the label sheet, then dialed it in with some trial and error.
@@AndrewBennettScience First of all thanks allot for your attention and effort to reply me.
Yes as you said i made an Avery L7656 on an a4 format by changing the margins but WHEN I ATTEMPT TO PRINT IT, I KEEP STOPPING AS THE PRINT PREVIEW IS SHOWING THE WHOLE WORKSHEET OF 1000 ROWS TO BE PRINTED...pls help me out!!
I was expecting there are so many barcode software out there for less or for free that can do almost everything like bulk printing and customize labels coz barcode exist like 2 decades already. but no.. XD when I tried looking for software that can do all of it, not only that I can't find a decent all in one offline software, but most of them hide every feature in different paywall. a paywall for bulk printing and paywall for text labeling. and a paywall for this and that.
and I realize I don't need these. instead I'm using Figma and Photoshop with libre39 font from google font.
in photoshop there is Smart object in which you can just edit one layer and all of them change accordingly. this is so perfect for bulk printing.
not only that, you can also resize them without losing resolution and can also maximize the paper coz you have full control over it.
I have now created a bulk barcode template PSD, I'm lucky I'm been very good at photoshop and work for it like it was part of limb.
I’ve been wanting to get a label printer for my small business for the longest time and finally pulled the trigger. I’m glad I did ! This machine is great czcams.com/users/postUgkxw55tQTywUAAOsbrlwxONZULry77sFF1Q and the blue/green color matches the rest of the accents in my office. My favorite part is how small the footprint is. It’s less than 1/4 the size of my regular printer.Set up was fairly easy. Seller will email you a link with video instructions Which I found to be more helpful than the written instructions. I use a Mac and had no issues. They even included an adaptor for USB to USB-c which is great because I’m always losing mine! I had a few issues getting the labels to print properly and wasted quite a few sheets. You need to play around with the settings to get everything adjusted just right. If you have the wrong settings, it’ll crop off the edges or feed through blank labels. These aren’t issues with the machine though, just user error.
Apparently, the barcode website now makes you enter a new code every two months in order to use this system with the sheet he shared. You will not be able to do this until you enter your code. It is FREE, but you have to remember to do it.
science and barcodes!
Since Onbarcode added a necessary "2 month activation code" for their online barcode generation to work, your method no longer works reliably.
I thought I had it working with the free activation code changing every two months, but with their new activation code, I don't know if the activation code changes based on the device used on or what, but OnBarcode's free online barcode generation is now proving totally UNRELIABLE for generating the barcodes in the spreadsheet!!!
It works flawlessl one day, then next day on same computer, only half the barcodes generate correctly, but it always seems to change so every day or two I have to modify the cell contents to get the barcodes to generate correctly for a couple hours!!!
sadley no longer relevant as online companies have stopped people from doing this, so you shou;d update this info
true
what a load of pfaffing but thanks for sharing
Thank you Andrew great work 🙏