Google Apps Script Google sheets How to ?

Bulk URL Shortener with goo.gl and Google Spreadsheet

Goo.gl is one of the most used service to shorten long URLs provided by Google. But this service does not support shortening multiple urls at once. You have to short one url at a time. Sometimes for marketing and sales, you may need to shorten bulk URLs. There are APIs available for Goo.gl which you can to shorten bulk urls. Using those APIs we have created a simple and effective tool with Google app script for bulk URL shortening.

Features

  • Integrated with Google Spreadsheet
  • One click bulk shortening
  • Saves shorten urls in Spreadsheet
  • Read analytics like
    • Click counts per url
    • County wise clicks
    • Browser wise clicks
  • Entirely customisable

URL Shorten

Code

NOTE: This is only a part of the actual code. Follow the instructions to get the full code.
function shortenURL(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("urlList");
  var lastRow = sheet.getLastRow();
  var urls = sheet.getRange("A2:A"+sheet.getLastRow()).getValues();
  for(var i=0;i<urls.length;i++){
        var url = UrlShortener.Url.insert({
          longUrl: urls[i][0]
        });
       sheet.getRange("B"+(i+2)).setValue(url.id);
       Utilities.sleep(1000);
  }
}

How it works

  • Open the Google Spreadsheet URL
  • Go to file → Make a copy → open the newly copied SpreadSheet
  • Go to Resources → Advanced Google Services
  • Scroll down you will find “URL Shortener API” → Just enable it → click on “ok”
  • Go to the top bar menu (URL Shortener) for bulk shortening and analytics
  • If you want to view the script, go to Tools → Script Editor

How to fetch analytics

Go to top bar → URL Shortener → Fetch Analytics

Google provides many other analytics properties like analytics.allTime.referrers[], analytics.allTime.longUrlClicks, analytics.allTime.platforms[].

You can use these properties like below.

var url = UrlShortener.Url.get(LONG_URL, {
     projection: 'FULL'
  });
Logger.log(JSON.stringify(url.analytics.allTime.referrers));

For more details visit this link

9 thoughts on “Bulk URL Shortener with goo.gl and Google Spreadsheet”

    1. Open the Spreadsheet link and go to script editor to get the full code. It seems like you are directly using the snippet given in the post. That is in fact part of the actual code.

      1. Not sure if you know but when i clicked on the spreadsheet link and signed in using my gmail id, nothing actually happened when i clicked on Tools > Script Editor…

        Nothing happens at all. Nada!

  1. Script is false in eExample! Line

    .addItem(‘Shorten’, ‘Shorten’)

    has to be changed in

    .addItem(‘Shorten’, ‘shortenURL’)

    Then it works very fine!

    Volker

  2. This code is splendid – it has saved me a ton of work!

    Getting it moving took a bit of troubleshooting:

    – Note that you need to turn on the URL shortener API in 2 places via the script editor: Resources > Cloud Platform Project and Resources > Advanced Google Services

    Also I was getting the “Cannot call method “getLastRow” of null” error. It was because getSheetByName needs the name of the sheet being referred to and the name in the present code wasn’t working. I also split up the var names:

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(“Sheet1”);

    I found it most useful to separate out the sheets by function, one for analytics and one for shortened URLs because the URL shortener function will re-do all of the URLs when you run it again, which isn’t useful for tracking purposes.

Leave a Reply

Your email address will not be published. Required fields are marked *