How to quickly make short pre-filled URLs

In this post, we’re going to look at how we can quickly make short bit.ly links for pre-filled Google Forms using Google Sheets and a bit of Apps Script.

Bit.ly links are great but if you have to make a lot of them in can be a slow, tedious process. Plus, if you also want to link to pre-filled forms that’s even more work.

Fortunately, here we’re going to see how we can do this by adding our pre-filled data on a Google Sheet, and then with a click of a button, we can create short links to those pre-filled forms.

As an example, we’re going to look at how we can create personalised questionnaires which will include the specific groups, levels and teachers at a school. This has two benefits: 1) it saves the students filling out this information on the form, 2) as we control the exact format of that data we can analyse it with ease afterwards.

We’re going to use this Google Sheet:

We have columns for the groups, levels, and teachers. Then we have two blank columns where we’re going to add the short personalised links for each group, and the full link. In cell E1, we paste in the generic pre-filled link we will get from the form (described below).

Main steps

1. Fill in the Google Sheet with the data you want to pre-fill on the form.
2. Get the generic pre-filled form link
3. Add the code in the script editor bound to the Google Sheet.
4. Get the bit.ly access token and add it to the code.
5. Connect to Martin’s UrlShortener library.
6. Run the oneTimeSetup function.
7. Refresh the GoogleSheet and run the script from the menu.
8. The short links and full links will appear on the Sheet in seconds.

Getting the generic pre-filled form link

The first step is to get the pre-filled link from the Google Form you’re using.

In the Google Form, click on the 3-dot menu and then select “Get pre-filled link”.

Fill in the fields you want to pre-fill with generic names. In this example, I want to pre-fill the group name, the level, and the teacher’s name.

Then scroll to the bottom and click “Get link”.

A notification will appear. Click on “copy link”.

Then on the Google Sheet, paste that link into cell E1.

---

The code

Now, let’s look at the code. It uses a wonderful library written by Martin Hawksey, which makes the call to the bit.ly API to get the short link. You can see his original post here.

I’ve adapted it so that we get the pre-filled data from the sheet and then return both the short links for each group questionnaire and also the full link.

First, let’s create a menu to run the script from.

1. //Add menu
2. function onOpen() {
3.  SpreadsheetApp.getUi()
4.    .createMenu("Shorten URL")
5.    .addItem("Create short and long links", "makeLinks")
6.    .addToUi()
7. }

L2: Set up the onOpen function.

L3: Create a menu.

L4: Add the menu item, calling the makeLinks function.

L5: Add it to the spreadsheet UI.

Next, let’s look at the main function, makeLinks.

9. //Make short and full links
10. function makeLinks() {
11.  const sh = SpreadsheetApp.getActiveSheet();
12.  const data = sh.getDataRange().getValues();
13.  let preFilledLink = data[0][4];
14.  data.splice(0, 2);

L10: Set up the function.

L11: Get the active sheet.

L12: Get all the data on the sheet.

L13: Get the pre-filled link from cell E1.

L14: Remove the first 2 rows from the data.

16.  //Loop thru data rows
17.  const shortLongLinks = data.map((row) => {
18.    //Replace any spaces in Class, Level, or Teacher
19.    let group = replaceSpaces(row[0]);
20.    let level = replaceSpaces(row[1]);
21.    let teacher = replaceSpaces(row[2]);
22.    let pfLink = preFilledLink.replace("GROUP", group)
23.      .replace("LEVEL", level)
24.      .replace("TEACHER", teacher);

L17: Now, loop through the data.

L19-21: The data could have spaces in it, which would break in an URL, so let’s replace the spaces with the character %20 which the browser will recognise as a space. I’ve placed that replacing in a separate function, which we’ll call for each of the pieces of text we want to include on the form, i.e. group, level, and teacher.

L22-24: We then need to replace the placeholders in the pre-filled link we added earlier, i.e. GROUP, LEVEL, and TEACHER, with the data from the current row (lines 19-21). Now we have the full link which will open the form and include the fields we want filled out.

26.    let shortLink = getShortLink(pfLink);
27.    return [shortLink, pfLink];
28.  });

L26: We call the getShortLink function and pass the full link to it. It will return the short link.

L27: We then return the short link and full link and store it in the shortLongLinks variable.

30.  //Add short and full links to columns D & E
31.  sh.getRange(3, 4, shortLongLinks.length, 2).setValues(shortLongLinks);
32. }

L31: Finally, we need to add both links for all the groups to the sheet.

34. function replaceSpaces(text) {
35.  return text.replace(/ /g, "%20");
36. }

L34-36: This is the function that replaces any spaces in the texts with %20. I’ve used the regular expression g in case there is more than one space.

38. //Add short links
39. function getShortLink(pfLink) {
40.  let url = UrlShortener.Url.insert({ longUrl: pfLink });
41.  return url.id;
42. }

L39-41: This is the code which works with the URL shortener library. It passes the full link to it and returns the short one.

44. //Get your bit.ly token and run this the first time
45. function oneTimeSetup() {
46.  UrlShortener.Util.setToken('your bit.ly token here');
47.  let grp = UrlShortener.Groups.list();
48.  UrlShortener.Util.setGUID(grp.groups[0].guid);
49. }

L45-49: The final function is separate and needs to be run before using the main one. For it to communicate with bit.ly, you will need to get a token and add it to line 46, and then run that function first.

---

Getting the bit.ly token

Let’s go through how to get the bit.ly token you’ll need for the code to work and be able to communicate with bit.ly.

if you haven’t got one already, create yourself a free bit.ly account.

Click on your account name.

Click on “Settings”.

Click on API.

Enter your password and click on “Generate token”. This will produce a token which will be a long series of numbers and letters. Copy that and paste it into line 46.

Connecting to the library

You’ll also need to connect to Martin’s library. In the script editor, click on the plus sign next to Libraries.

Paste in Martin’s library ID, which is: 1ddSpTQoae2xdocyx0GcfNCKOjZu8je_OFWXUM_-cG-fGVJIQyxGRrAnQ

Then click “Look up”.

This should find the library. Click “Add”.

You will see that the UrlShortener library has been added. Note, you will need to give permission to the script.

---

Run the oneTimeSetup function

Prior to making the links, you will need to run the oneTimeSetup function.

---

Making the links

To make the links, just click on the “Shorten URL” menu and “Create short and long links”.

You will see the short and full links appear on the Google Sheet in seconds!

Clicking on one of the links, we can see that fields have been pre-filled with the information we wanted.

The great thing is that once set up once, making these questionnaire links in the future is super quick!

Click on the links below to make copies of the files:

Google Sheet

Google Form

Thanks Martin for writing the bit.ly shim! 👍