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
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
Getting the following error.
TypeError: Cannot call method “getLastRow” of null. (line 3, file “Code”)
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.
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!
Did you make a copy of the spreadsheet first ? If not go to File–> make a copy…
Then only it will work.
hi thanks this looks super useful. Analytics work but the shortener not. I get this: Message details
Script function not found: Shorten For more information, see https://developers.google.com/apps-script/reference/base/menu#addItem(String,String)
Script is false in eExample! Line
.addItem(‘Shorten’, ‘Shorten’)
has to be changed in
.addItem(‘Shorten’, ‘shortenURL’)
Then it works very fine!
Volker
I tried but said: Rate Limit Exceeded (línea 14, archivo “Code”)
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.