I’ve been there as well: you have a huge list of URLs you need to shorten and schedule up in your Buffer queue. You probably have more than 10 URLs you need to shorten through Bitly.
I bet you don’t want to manually copy-paste all of them in your preferred URL shortening service.
So you start searching on Google how to do that, right? And you try with variations of:
How to bulk shorten a list of URLs?
Btw: did you know Google URL shorter is no longer available as a free tool? Google shut it down in 2018 and will be discontinued by the end of March 2019.
Back to the main story here…
You need an efficient way for shortening a long list of URLs in Bitly. And your research for working solutions proved to be a waste of time: all of the top results you found were relying on an old way of getting this task done (like this one).
That’s a bummer. A shortened version of your disappointment, if you want.
Here’s the reason why all solutions out there aren’t working:
- Bitly’s API keys are deprecated
- All previous ways to dynamically shorten URLs were taking advantage of Bitly’s APIs
- None of them works anymore
Fin of this productivity (sad) short story.
You don’t need to have a working knowledge of what APIs are (I don’t have it). Suffice to know is that when you read “deprecated” it means either something is no longer working or will soon stop working and it’s not in your best interest to use it.
So, has your research come to a dead end?
No, it hasn’t! Here’s how to automatically shorten hundreds of URLs without any scripting/deloping knowledge (in the example below, I had 150+ URLs).
These are the steps you should follow:
How To Bulk Shorten Multiple URLs With Bitly APIs — With Template
- Make a copy of this Excel sheet (File -> Make a Copy…)
- Generate your Access Token in Bitly by following the steps below:
- Click on your username tab
- Click on “Generic Access Token”
- Click on the “Password” box and confirm your bitly password and generate your access token by clicking the orange button below “Generate token”
- Copy your generic access token to your clipboard
- Go to your copy of the Google Spreadsheet and paste your generic access token within the “Settings” tab, where it reads “add your Generic Access Token”. And add your Bitly username (your email address) as well
- Now go to the “Shortener” tab and enter all your links you’d need to bulk shorten in column A (Long URL column). If you’re copying from another spreadsheet, use “Paste special > Paste values only”
- Now click the cell you’ll find in B2 and drag it down for all the long URLs you’d like to shorten in bulk
- Now, just wait for Bitly to pile up all your shortened URLs in the column!
Credits to Harish Kotra. Thanks!
Wrapping up
When you can automate even the smallest repetitive task, you earn an immense value from it: you get to free up time that, until that moment, was weighing you down and keeping you busy for doing that task.
Today, you have a way to get 20 minutes back from your working time.
Choose wisely how you’ll use them.
Its not working
Hey Sam,
thanks for stepping by :)
I just tested the spreadsheet and it works. Would you mind giving it another try and be sure to a) add the correct info in the “Start here” tab, b) add your links in A2, A3, etc. cells in the “Shortener” tab, and c) wait for Bitly to bulk shorten them.
This might take some time, based on the number of links you have there.
Let me know if you’re still experiencing any issues.
how many links can we shorten make with one sheet?
More than 150 for sure!
This was super convenient and saved me a lot of copy & paste overhead!
Thanks a bunch!
Mine not working
Sorry to hear that.
I’ve tested it again and I had no issues. What issues are you experiencing?
Not working for me as well. The link wont generate and says “impossible to reach this URL”
Very helpful if I get this to work though. Awesome work
Pierre, the sheet has been updated. You might want to try now and let me now!
Matteo, thanks a ton for this!
Your sheet wasn’t working for me in the beginning but then I noticed that there was a #REF! error in the formula within the urlencode() bit. Once I corrected that to refer to the cell A2, the sheet worked! Thought I should mention this since many people claimed to face errors.
I came through the Linkedin article you had commented on. That spreadsheet used the Bit.ly API and was slower than this.Your method of using the access token is much faster, especially since I was converting a lot of links.
Ha, that was it!
Thank you so much. Already updated my sheet and fixed the formula!
Glad you liked it!
Matteo this is great! It seems to be working for the most part. I pasted in a little over 500 links to shorten and dragged the formula down. About half of them spit out a shortened URL (hit or miss down the list) and the rest of the records said “Loading…” with an error note attached to the cell. I just left it alone for a bit while I was doing somethign else and most of them have finally returned a shortened URL, but a couple of them returned “#N/A” and the error message says that it could not reach bitly’s API. Not sure what happened there… any thoughts?
Hey Lauren,
that’s great to hear!
So what i’m getting here is that most of the URLs got shortened after a while — as I said in the blog post — but few got you “N/A”.
Is there a chance that the internet connection might have dropped while the spreadsheet and Bitly’s API where talking?
I mean, if 99% of them went through successfully, the cause might be something like that.
I have 1000+ links to convert and I’m getting a timeout error that says to use the Utilities.Sleep() function. How would I go about adding it to your file? Thanks again for your help!
Hey Jared,
have no idea how to use that in a formula. Why not splitting your list into 2 (or more) to prevent the timeout error?
Thanks!!!!! u rock!
Happy to help :)
Hello Not working say “could not fetch url :(
Great its working i made some mistake your are great Thank you :) :)
You’re welcome! Happy to help :)
getting errors today. something changed? working fine till now
Haven’t touched this in a while.
Hello Matteo
can you check again?
I’ve tried repeatedly but still can’t. Please help
Hey Malissa,
What are you experiencing? If you could share a bit more info, I’ll be happy to help.
Btw, haven’t touched a thing so it should work.
Hi Matteo,
Its not working. Showing loading and error sign.
The formula is returning a #NAME? error. I’m seeing unknown function: ‘urlencode’. Any advice? Thanks for sharing this.
Hello Matteo
I’m having trouble with your shortner.
Some links shortner, some say ‘n/a’ and others say ‘loading’.
Any suggestions?
Thanks
Could you check again. I see it on your file.
https://drive.google.com/file/d/1nhCneHf1NQPGoOZ2CAK1Dz2wZ7M_DljF/view?usp=sharing
hi! thanks for sharing! very awesome trick.
wondering if we can add our own customise back-half?
Thanks
Will this be updated to use Bitly’s API version 4?
Hi
I need the opposite…a bulk url lengthener. I havent seen anything like it on the web. I put in a few hundred of thousand shortened urls and it gives me back long ones. Do you
1) know any place that has this?
2) have any advice on how to build it?
Unfortunately, can’t help with your request.
Hello Matteo,
Sheet not work at the moment.Can you repair?
Hello Matteo,
Thank you for this useful tool :)
Just one question does it work with excel or only with google docs?
It works for me with google doc, but not with excel :)
Thank you,
Thomas
Dear Matteo,
thank you so much for your effort so far. What a great tool/way to automate a repetitive task.
Unfortunately, I experience an error as well:
After pasting the long URL in e.g. A2 I see that B2 initially shows “loading” but afterward gives back a “#nv” error.
Help would be appreciated.
Best
Daniel
Working! Thanks!
That’s awesome to hear!
All this can be simply done with L0.CL with no copy paste. 50 URLs are processed in 1 go for free users. For Paid user depending upon length the number of URL could be calculated. 1 Million characters are processed via their API so 10000 URLs of 100 character length could be processed and they very fast!
I’ll give it a try, thanks!
I have the same question as Kari. I see in your formula that it is using V3 of the API which is set to depreciate on March 1, 2020. Can you share what the new formula will be for V4?
Hey Ash,
I’m researching how to do that but I’m not sure I’ll succeed. I’ll post any updates here (if any).
Hi,
It did not work for me. It shows error “Could not fetch url:…” and also it shows “Unknown function :’urlencode'”. Can you please help.
Works like wonder! Thanks for sharing.
I did get a few #N/A out of almost 700 links but it is still much better than having to do it individually.
One question: Some of the bitly linked actually changed to #N/A after awhile, wonder why?
Thanks again
Hi Matteo!
Many thanks for the solution, works pretty well.
Only one thing is recently bit.ly has informed about API upgrade to V4.
Do you maybe have a solution for this case if yours actual one is going to be down soon?
Thanks for help!
R
Unfortunately, I don’t but I’ll try to update the blog post if/when things change. Thanks for your feedback!
Is this service now dead?
wow it works well! save me a lot of time, thank you so much!
Thanks for stopping by!
wow really working! Thanks a lot!
Glad to hear that!
OMG thank you so much for posting this!!! Your method worked beautifully and literally saved me hours!!!! I had over 200 links and roughly 124 or so populated almost instantly while the others finished processing in a span of approximately 30 minutes! Thanks again!
That’s awesome to hear. Happy that I helped you!
This was a HUGE help! Thank you so much!!!
Glad to her that!
Works like a charm. Thanks a ton. Saved a lot of time.
Thanks for letting me know, I’m glad you found it useful!
Hi there,
is there a possibility to somehow integrate this fabulous tool in word?
Problem: I have a very long link in Word and need to shorten it.
MUST be solved in MS Word … :-(
Thanks!
Not that I know of… but if you find it, please get back here so I can update my guide.
Thank you very much for sharing
Thank you man, it was very helpful!
saved me a lot of time, thanks!
It’s unfortunate this did not work well for others, this was such a fantastic tool and very easy to follow! It just takes time to convert the links. It was taking me on average about 8 seconds to convert 1 URL which is a lot better than doing it manually. I only had about 300 URLs but wow, thank you SO MUCH! Thank you for investing the time into creating this and sharing it with everyone.
HEY MAN THANKS FOR THIS EASY TRICK .I WAS LIKE TRYING TO CODE THIS FOR MORE THAN AN HOUR.THIS ACTUALLY SAVE A LOT OF TIME
Thank you for sharing this tool! Just used this to convert 30ish urls, and it worked great.
Thank you very much. This is brilliant
Hi Matteo,
After some trying i finally got it work :)
Thanks a lot mate!
Thank you, Matteo. I have done it from the first time!
work it, thanks a lot
As of January 20, 2021, this perfectly works! I only shortened about 25 links. Thank you, Matteo.
That’s awesome to hear, Shula!
It works! Thanks!
Thank you a lot for having this, for now, I can easily create it without manually converting it one by one
Thanks for the tool! Worked perfectly for me.
Is it in any way possible to also edit the title of the shortened URLs in bulk in the sheet? And to get a click count in a sheet per shortened URL?
Thank you for sharing this post with us, Very usefull
this is very very usefull to my job. Thank for sharing. Worked perfectly for me. Many Thanks
Hello Matteo, if you run a Bitly free plan that impacts the use of the tool?
In my case doesn’t work, it says “Error laoding data”
FYI: Apparently, Bitly push the API v4: https://dev.bitly.com/docs/getting-started/migrating-from-v3/
best
amaaaazing, thank you so much, confirmed still works!!! you just save me so much time, thanks :D