[et_pb_section fb_built=”1″ admin_label=”section” _builder_version=”3.0.47″ custom_padding=”4px|0px|37.9062px|0px|false|false”][et_pb_row admin_label=”row” _builder_version=”3.0.48″ background_size=”initial” background_position=”top_left” background_repeat=”repeat”][et_pb_column type=”4_4″ _builder_version=”3.0.47″][et_pb_text admin_label=”Text” _builder_version=”3.21.1″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” use_border_color=”off” border_color=”#ffffff” border_style=”solid”]
The Google Url Shortener has closed down.
Wouldn’t it be better if instead of sharing a long link like the one below, you could get a shorten version of it? Well, you can and Google provides a URL shorten service, which generally reduces any URL to only about 13 characters.
For example, here’s a link that was created by Google Forms when I pre-filled some info in (see my post on personalizing Google Forms):
https://docs.google.com/forms/d/1cCk-eDEV81i2X9uua_pI15SGI4N5ZJq0HE-MIHHswq8/viewform?entry.1422654197=Monday%2019:15&entry.148162827=First%20Certificate&entry.2134513978=Mark%20Walsh&entry.349432024=A01&entry.1681636254
As you can see, it’s very, loooooong!
One way to shorten this is to go to Google URL shortener site at https://goo.gl/
Here paste in your long URL in the box and click the blue “Shorten URL”.
On the right-hand side, your short URL will appear, highlighted ready to copy. As you can see it’s much shorter than the one above.
Below it you will see what your page looks like, in this example it’s a form with the fields pre-filled out.
To share your new short URL, just press Ctrl+C (Cmd+C) to copy it and paste it where you want it.
This is fine if you don’t have many URLs to make, but what about if you have 10, 20, 100 to make? A long, slow, tedious process. But you’ve guessed it, there’s a better, quicker way!
Using the spreadsheet I created in my post about creating personalized Google Forms, I’m going to show you how a little bit of Google Apps Script, can quickly create these URLs.
What’s Google Apps Script? I hear you ask. This is a programming language which sits in the background of your Google Apps (Drive, Docs, Sheets, etc) and with which you can tell it to do some wonderful things. If you’re new to Google Apps Script, don’t worry to set this up, you don’t need to know how it all works, just follow the steps.
The main steps are:
- Open the Google Sheet you want to put the shortened URLs in.
- Open the Script Editor and paste in the code.
- Enable the Google URL shortener service. (only necessary the first time)
- Select the URLs you want to shorten.
- Authorize and run the code.
1) Open the Google Sheet you want. In this example, I have the 4 classes I wanted to create personalized Google Forms for. The long personalized URLs are in column E.
2) I want to put the Short URLs in the column to the left of the long ones. So, I right-click on the column where it says “E” and the menu will appear. Click “Insert 1 left”.
3) A new column is added in column E. Now I just give it a name, e.g. “short URL”. It can be any name.
4) Now to add our script. Go to the “Tools” menu and click “Script editor…”.
5) This will open the Script Editor in a new window.
6) First, give the script a name. Click on “Untitled project” and type in a name, e.g. “Create short URL”.
7) In the main window, a new script always starts with the function below. We don’t need any of that so, highlight it all.
8) Copy all the code in the block below and paste it into the Script Editor.
//When the spreadsheet is opened it adds a menu called "Shorten URL" and a sub menu item called "Shorten"//This relates to the function below called “short”
function onOpen() {
SpreadsheetApp.getUi()
.createMenu(“Shorten URL”)
.addItem(“Shorten”,”short”)
.addToUi()
}
//Takes highlighted range and goes down row by row, adding a short URL to the column to the left
function short() {
var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
var output = [];
for(var i = 0, iLen = data.length; i < iLen; i++) {
var url = UrlShortener.Url.insert({longUrl: data[i][0]});
output.push([url.id]);
}
//If you want to put the short URL in a different column, adjust the “-1” in line 20
//Negative number moves to a column to the left; positive number moves to a column to the right.
range.offset(0,-1).setValues(output);
}
Here’s a link to the code: https://goo.gl/KXzdFI
Your Script Editor should now look like this:
9) Now click save (the disk icon) to save the script.
10) Now we need to enable the Google URL service. This only needs needs to be done once. From the “Resources” menu, click “Advanced Google Services”.
11) This opens the Advanced Google Services box. Scroll down until you find “URL Shortener API”. Turn it on, by clicking on “off” and then click OK at the bottom of the box.
12) Back in the Script Editor, from the “Resources” menu again, click “Developers Console Project”.
13) This will open a new window showing the “Developers console Project”. Click on the blue link “Create short URL”.
14) Under the “Use Google APIs” click “Enable and manage APIs”.
15) This brings you to the Google APIs page. Click on “URL Shortener API”.
16) This opens the URL Shortener URL overview. Click “Enable” to well, enable it. It will change from “Enable” to “Disable”.
Note: steps 10-16 only need to be done once. Just close the window after using the Google Developers Console.
17) Now it’s time to use your script. Go back to your Google Sheet and click the browser refresh button. This will load your script in the background, as this script is automatically loaded every time you open the Sheet.
18) Select the cells your long URLs are in.
19) When you refreshed the Sheet (in step 17), a special menu was also created called “Shorten URL”. Click on that and click on “Shorten” to run the script.
The first time you run the script you’ll be asked to authorize it, just click “Continue” then on the next step, click “Allow”.
20) In a few seconds, you shortened URLs will appear in column E.
There seems to be quite a lot of steps, but even when you run it the first time and have to enable to URL shortener, this whole process takes less than a minute. The next time it takes seconds!
About the script
The only part of the script you may need to change is the last line:
range.offset(0,-1).setValues(output);
This tells the computer where to put the shortened URLs in relation to the URLs you’ve highlighted.
Generally, the only part that’s important is the second number, in this case “-1”. This is the column in which to put your URLs. If you want to put the short URL in a column to the left put a negative number; if you want to put the short URL to the right, put a positive number. The number is the number of columns away from the original URLs selected.
If you just want to use the above script for shortening URLs, where they are not linked to pre-filled Forms, I would recommend making a file just for this purpose, where you have one column for the long URL and on column for the shortened URL. Having a specific file for doing this, means that once you’ve enabled the URL shortener for the first time, you’ll only have to paste in your long URLs, run the Shortener script from the menu and voilà you have your short URLs.
This idea is adapted from one posted on stackexchange.com. If you’re using Google Apps Script, this is an amazing place to find out solutions, or to get your own problems resolved. Here’s the original post:
So, thank you to Alex.
[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row admin_label=”Row” _builder_version=”3.0.48″ background_size=”initial” background_position=”top_left” background_repeat=”repeat”][et_pb_column type=”4_4″ _builder_version=”3.0.47″][et_pb_text admin_label=”PostEnd” _builder_version=”3.21.1″ background_size=”initial” background_position=”top_left” background_repeat=”repeat” use_border_color=”off” border_color=”#ffffff” border_style=”solid” global_module=”22296″ saved_tabs=”all”]
Note, some of the information above may be out-of-date as Google continually add new features to the apps and make cosmetic changes. I have a set of eBooks, which are periodically updated to include the latest changes.
eBooks available on Drive, Forms, Sheets, Docs, Slides, Sheet Functions, and Apps Script:
- “Beginner’s Guide to Google Drive” – Kindle store / Apple Books store
- “Beginner’s Guide to Google Forms” – Kindle store / Apple Books store
- “Beginner’s Guide to Google Sheets” – Kindle store / Apple Books store
- “Beginner’s Guide to Google Docs” – Kindle store / Apple Books store
- “Step-by-step Guide to Google Slides” – Kindle store / Apple Books store
- “Google Sheet Functions – A step-by-step guide” – Kindle Store / Apple Books store
- “Beginner’s Guide to Google Apps Script 1&2 – Sheets & Forms” – Kindle Store / Apple Books Store
- “Beginner’s Guide to Google Apps Script 3 – Drive” – Kindle Store / Apple Books Store
Baz Roberts (Flipboard / Twitter)
[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]
I really like this, but I’m getting this message when I try to save the code: Illegal character. (line 5, file “Code”)
Hi Brian – Does the code in line 5 say this?: .createMenu(“Shorten URL”)
I see the problem, WordPress has changed the quotation marks. Replace line 5 with this: .createMenu(“Shorten URL”)
You’ll need to replace the quotation marks in line 6 too. Try copying it from here.
.addItem(“Shorten”,”short”)
It’s the first time I’ve tried copying code from a WordPress blog, so I’ll have to see how I can change the code, so it can be copied directly.
Hi Brian – I’ve sorted it. I had to change the HTML of the code a little so the smart quotes don’t appear when pasting it into the script editor. You should now just be able to copy and paste it without any problems. I’ve also added a link to the code. Thanks for letting me know!
I really appreciate your help! I made the changes but am now getting an ‘Invalid Value’ message when running the shorten URL. The blank column is immediate left (i.e., 4 columns right, or Col E) of the long URLs; the script is set to ‘range.offset(0,1).setValues(output);’
Are the long URLs in column A? If so, and you want the short ones to appear in column E, change the second number in the brackets to 4. Also make sure you’ve selected the long URLs before running the script.
That worked! Thank you!
One, final question: I have long urls in two columns. Is it possible to adjust your script to accommodate two columns with long urls?
Again, thank you very much!
Hi-I’ve added a prompt box which asks you which column you want to put the short URLs in. It means you will need to run twice to add to the two columns, but it’s more robust as you can put the short URLs anyway, without having to edit the code. Now in the range.offset there’s a reference to the answer you input in the prompt box (“columnRef”).
//When the spreadsheet is opened it adds a menu called "Shorten URL" and a sub menu item called "Shorten"//This relates to the function below called "short"
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Shorten URL")
.addItem("Shorten","short")
.addToUi()
}
//Takes highlighted range and goes down row by row, adding a short URL to the column to the left
function short() {
//Add prompt asking you to state where you want to put the short URLs
var ui = SpreadsheetApp.getUi();
var response = ui.prompt('Where do you want to put the short URLs?', '(+) to the right, (-) to the left', ui.ButtonSet.OK_CANCEL);
if (response.getSelectedButton() == ui.Button.OK) {
var columnRef = response.getResponseText();
}
//Gets selected range, then goes down the rows one by one, creating a short URL each time
var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
var output = [];
for(var i = 0, iLen = data.length; i < iLen; i++) {
var url = UrlShortener.Url.insert({longUrl: data[i][0]});
output.push([url.id]);
}
//If you want to put the short URL in a different column, adjust the "-1" in line 20
//Negative number moves to a column to the left; positive number moves to a column to the right.
range.offset(0,columnRef).setValues(output);
}
Thank you, sir. I really appreciate your helpfulness!
@bazroberts This is amazingly useful! Thanks for the idea.
I’ve tried it and it works if I shorten one url. However I’m trying to shorten in bulk hundreds of urls and I get the “Rate Limit Exceeded” error.
Not sure how to solve this?
In the Console API manager, I understand the URL Shortener API has a daily quota of 1million/requests/day and 100 requests/100seconds/user and it seems the 100 cannot be edited upwards. Any thoughts?
Yes, there’s a limit on both the amount you use and the rate you use. You can slow the speed down using “Utilities.sleep(1000)”, for example, which would add a second pause between each cycle, which sometimes does the trick. The downside, is that the process then takes longer. Another way is to use “Utils.rateLimitExpBackoff(function…” which waits only if there is a rate limited error, then tries again. So, it only waits when necessary. I know about both options but I’ve not tried to include them in a program. I’ll have a look at the weekend, to see if I can update the code, without it getting overly complicated. Another option, is to ask in the Google Apps Script community on Google+ there’s always someone, far more knowledgeable than me, who is willing to advise.
Albert – The first thing you could try is adding Utilities.sleep(1000); after line 16: output.push([url.id]); Before the curly bracket. This will put a pause in each cycle which should help it not produce an error for requesting this service too many times per sec. I’m going to look at the second option now, but that’s a bit more involved. The other thing of course, is to run them in smaller batches, by highlighting different groups of cells a a time.
Albert – The code for exponential backoff is more complicated than I thought. It’s probably what you need as it only pauses if an error is signaled. I’m reading an example from the book “Going GAS” and it’s nearly 3 pages long just to do this, but it’s not clear how that would fit into the program. If you’re the sleep option doesn’t work for you, I suggest asking the folks in the Google Apps Script Google+ community the question.
I created a two column sheet and copy pasted your script but I keep getting the following error message “ReferenceError: “UrlShortener” is not defined.”
Hi Phil – Have you enabled the URL shorter on the Advanced google Sevices & Developers console project? It won’t run without this step.
That’s amazing! thank you.
Thanks Fabien. 🙂
¡Wow! Thank you very much you saved a lot of time.
You’re welcome! 🙂
Life saver!!! This is awesome.
would be awesome to have something like this as a function as opposed than a menu. i.e =SHORT_URL(url)
That’s a good idea.
I’ve been trying to get this script to work with my sheet to automatically run (as opposed running it from a drop down menu). My goal is to have no user intervention and have a short link generate when triggered from entered data from a form the sheet is linked to.
Iv’e searched everywhere for this and even tried running the function on its own only to get an error: “Daily Limit For Unauthenticated Use Exceeded. Continued Use Requires Signup. (line 15)”
Do I need to enter an API key or something?
I’m lost when it comes to programming or writing scripts.
Much thanks, I’ve seen the original post on stackexchange first and stumbled across your site with a refreshing detailed explanation!
Hi-It’s difficult to say why you’re getting that error without seeing the code and understanding how you’re trying to do it. If you can, email me it and when I get a chance I’ll have a look at it.
Haha thanks for replying but to be completely honest I don’t know much about coding syntax.
I’ve read there are limitations to triggers…
Can you tell me if it is possible to have the link shortener automatically run upon a form submit?
Yes, it’s possible to run it off the onFormSubmit trigger. You need to set up the trigger and then would need to get the info from the last row on the form responses sheet. Have a look at my two posts on working with form submissions.
This one explains how to set up the trigger:
https://bazroberts.wordpress.com/2017/01/29/request-form-sending-automatic-emails/
This one shows how to get the info in the latest submission:
https://bazroberts.wordpress.com/2017/02/03/automatically-emailing-info-from-a-form-submission/
Hi Bazroberts
Thanks so much for sharing this its a really useful script and something we will definitely use
Can you advise if you have time how I can go about modifying the script to include an onEdit Function
Basically what im trying to do is
User fills in pre defined data validation fields to cut down on errors in generating the long UTM
Once the user has filled in all fields the last check cell contains a “yes” “no” text answer if the user selects “yes” it runs a formula in the next cell to build the long URL
=IF(G5 =”Yes”,A5&”?utm_source=”&(text(B5,”YYYY-MM-DD”))&”-“&(C5)&”-“&(D5)&”&utm_medium=”&(E5)&”&utm_campaign=”&(F5),””)
Ideally I then want the script to check two thing
1) Does cell G3:G say “yes”
2) If it says yes look at H3:H for any long URLs and shorten then
I have edited the script to add the url to right of G and I have looked at the following formulas for guidance hiding and unhiding rows but cant seem to tie the two together (learning script as I go )
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName(“Working Sheet”); // Enter sheet name
var row = s.getRange(‘S:S’).getValues(); // Enter column letter that has the text “hide” and “unhide”
s.showRows(1, s.getMaxRows());
for(var i=0; i< row.length; i++){ if(row[i] == 'Completed') { s.hideRows(i+1, 1); }
{ if(row[i] == 'Expired') { s.hideRows(i+1, 1); }
else if(row[i] == 'unhide'){ s.unhideRow(ss.getDataRange()); }
}}}
Thanks so much for your help in advance and no worries if you cant advise
Cheers
Tim
Hi Tim-When I get a chance I’ll have a look at this. In the meantime, you could post your question in the Google Apps Script Google+ community.
Hi Bazroberts
I have done all the things which you have described and it seems to be there are few things that have been updated from the date you have posted.
I’m getting the error as “Forbidden”. kindly suggest me what to do.
Hi-The first thing after getting the error, would be to look in the execution transcript in Script editor and see at what line in stops at.
Since Google has stopped goo.gl if anyone wants to shorten Multiple URLs then simply use L0.CL (https://L0.CL). No script required!
Just club the Multiple URL together separated with ; as delimiter or in new line.
50 URLs could be shortened in 1 go and there after they ask captcha and there’s no limit!