Creating multiple short URLs in seconds

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”.

Short URL - 1

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.

Short URL - 2

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:

  1. Open the Google Sheet you want to put the shortened URLs in.
  2. Open the Script Editor and paste in the code.
  3. Enable the Google URL shortener service. (only necessary the first time)
  4. Select the URLs you want to shorten.
  5. 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.

Short URL - 3 (1)

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”.

Short URL - 4

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.

Short URL - 3b

4) Now to add our script. Go to the “Tools” menu and click “Script editor…”.

Short URL - 5

5) This will open the Script Editor in a new window.

Short URL - 6

6) First, give the script a name. Click on “Untitled project” and type in a name, e.g. “Create short URL”.

Short URL - 7

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.

Short URL - 8

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:

Short URL - 10

9) Now click save (the disk icon) to save the script.

Short URL - 11


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”.

Short URL - 12

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.

Short URL - 13

Short URL - 14

12) Back in the Script Editor, from the “Resources” menu again, click “Developers Console Project”.

Short URL - 15

13) This will open a new window showing the “Developers console Project”. Click on the blue link “Create short URL”.

Short URL - 16

14) Under the “Use Google APIs” click “Enable and manage APIs”.

Short URL - 17

15) This brings you to the Google APIs page. Click on “URL Shortener API”.

Short URL - 18

16) This opens the URL Shortener URL overview. Click “Enable” to well, enable it. It will change from “Enable” to “Disable”.

Short URL - 19

Short URL - 20

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.

Short URL - 22

18) Select the cells your long URLs are in.

Short URL - 23

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.

Short URL - 24

The first time you run the script you’ll be asked to authorize it, just click “Continue” then on the next step, click “Allow”.

Short URL - 25 Short URL - 26

20) In a few seconds, you shortened URLs will appear in column E.

Short URL - 27

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:

http://webapps.stackexchange.com/questions/76050/google-sheets-function-to-get-a-shortened-url-from-bit-ly-or-goo-gl-etc

So, thank you to Alex.


eBooks now available on Drive, Forms, Sheets, Docs, and Slides:

Baz Roberts (Flipboard / Twitter / Google+)


20 thoughts on “Creating multiple short URLs in seconds

  1. I really like this, but I’m getting this message when I try to save the code: Illegal character. (line 5, file “Code”)

  2. 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!

  3. 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);
        }

  4. @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.

  5. I created a two column sheet and copy pasted your script but I keep getting the following error message “ReferenceError: “UrlShortener” is not defined.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s