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