How to bulk-shorten urls in Google Sheets using Google Apps Scripts - a step by step tutorial

Do you have a google sheets that contains thousands of urls that you want to get the short version of? Do you want to get it done in a matter of seconds? Do you want to implement this without a big hustle?  If so keep reading.

Resume

This is a step by step guide to implement bulk urls shortening directly from Google Sheets, based on the TinyURL shortening service and using google apps script (even though, the process is relatively similar to any other url shortening services like Bitly or Ow.ly ). I will briefly describe the backstory but feel free to jump straight to the process.

Backstory

I have a friend who owns a relatively small website and he had a blackfriday campaign to launch. During this campaign, he wanted to share the urls that display his products on social media and additionally, he wanted to mail them to his customers The problem was that these urls are spam-my-looking .ie: http://friendwebsite.com/shirts/filters/promotion=50&offer=flash&start=(date)&end=(date)&customer=%20%new%20%&utc=blackf . Since I was the computer guy, he asked me to handle the bulk shortening.

Before jumping to the steps, I would like to define the technologies being used and specify the requirement for this tutorial.

Definition:

• Google Sheets: is an online spreadsheet app that lets users create and format spreadsheets and simultaneously work with other people.
• Google Apps Script: is a rapid application development platform that makes it fast and easy to create business applications that integrate with Google Workspace using JavaScript
• TinyURL: is a URL shortening web service, which provides short aliases for redirection of long URLs

Prerequisites:

A Google Sheet with two columns: one that contains urls to be shortened, the second -an empty one- will contain the end result.

Since GAS is a JavaScript platform, a familiarity with JS is recommended. Finish the talk, let's get to business ⚔️.

Steps:

1-Access the script editor:

Action: click on "Tools > Script editor":

Explanation: Here we will access the GAS editor. By default, it opens a Code.gs text file. This file contains a generic function called 'myFunction' that we don't need, and you can either keep it there or remove it entirely. The key point here is that this Code.gs is tied to our sheet so the function we will be developing is usable from inside the sheet like any other formulas.

2-Implement the url shortening function:

Action: create a new function called 'tinyurl_getShortLink' with the following code:

Explanation: This code snippet might be pretty self explanatory to JavaScript guys, but I would like to explain it to those without a web programming background. This code snippet takes the url that we want to shorten, referenced by the variable name 'url'. Since we are using TinyURL service, we will be using an endpoint which it exposes to generate a short url from a long one. This is the HTTP endpoint that we will be using: http://tinyurl.com/api-create.php?url=our_long_url_needs_to_be_injected_here (free to use 😃)

💡  An important note here is that we need to properly encode the url (Line 6) to eliminate eventual problems related to the url's structure.

Once the url is properly encoded, we do the HTTP request to TinyURL to get the short version. This is done using the  UrlFetchApp service (which is exposed by Google Apps Script environment), and get the result: If the request was successful (we got a 200 as result code), we return the shortened url, otherwise, we return that "An error occurred", as simple as that. Once finished, we hit save, give the project a name of our choice and we are good. Here is my azaytek_short_url project's screenshot:

3-Apply our new formulas, i.e function to the sheet's cells:

Action: apply the new formulas .i.e: tinyurl_getShortLink to the sheet.

Explanation: In the previous step, we created our proper formulas to shorten urls using the Google Apps Script and it became usable from inside our Google Sheet, similar to any native formulas. Now, we will be applying it to the cells of our choice. We have to select the cell that should contain the sort version, type: =tinyurl_getShortLink(LONG_URL_CELL), and hit ENTER. YADA! We get the short version right in there!

💡  The 'LONG_URL_CELL' placeholder is a reference to cell that contains the origin url.

If we would like to apply the formulas to all the rows, we only have to select the cell that was already generated, mouse button clicked and scroll down until we reach the end of the file. We will have a loading in place (This is our function being executed) :

Conclusion
This is what it take to implement a bulk url-shortenering from inside Google Sheet using Google Apps Scripts and TinyURL service. If you found this tutorial helpful, feel free to share it. Thank you and stay tuned for my next article: Web scrape like a pro using Google App Scripts and get IP rotation for free!