Skip to content

Instantly share code, notes, and snippets.

@marksharrison
Last active September 26, 2018 17:30
Show Gist options
  • Save marksharrison/343d0b095c6efe835c14 to your computer and use it in GitHub Desktop.
Save marksharrison/343d0b095c6efe835c14 to your computer and use it in GitHub Desktop.
Google Sheets apps script for Goo.gl URL Shortener & Simple Analytics
//Goog.gl Shortener API - https://developers.google.com/url-shortener/v1/
//Requires URL Shortener API to be enable in Resources > Advanced Google Services of Script Editor
//Requires cFlatten reference "MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j" in Resources > Lirbraries... of Script Editor - http://goo.gl/U4v9q2
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Goo.gl")
.addItem("Shorten Selected URL(s)","setURLShorts")
.addItem("Fetch Selected URL(s) Analytics","getURLAnalytics")
.addToUi()
}
function setURLShorts() {
var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
var outputShort = [];
var outputQR = [];
try {
for(var i = 0, iLen = data.length; i < iLen; i++) {
if (isURL(data[i][0])) {
var url = UrlShortener.Url.insert({longUrl: data[i][0]});
var qr = "= HYPERLINK(\"https://chart.googleapis.com/chart?cht=qr&chs=100x100&choe=UTF-8&chld=H|0&chl=" + [url.id] + "\", \"Link\")";
//TODO: Request shortened URL with https from goo.gl
outputShort.push([url.id]);
outputQR.push([qr]);
} else {
throw new Error("Select a cell with a valid URL");
}
}
range.offset(0,1).setValues(outputShort);
range.offset(0,3).setValues(outputQR);
} catch(e) {
handleError(e.message);
}
}
function getURLAnalytics() {
var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
var output = [], url, value;
try {
for(var i = 0, iLen = data.length; i < iLen; i++) {
if (isURL(data[i][0])) {
value = data[i][0];
url = UrlShortener.Url.get(value, {projection: 'ANALYTICS_CLICKS'}); //'FULL','ANALYTICS_CLICKS','ANALYTICS_TOP_STRINGS'
var a = url.analytics.allTime; //.month, .week, .day, .twoHours
output.push([flattenObject(a)]);
} else {
throw new Error("Select a cell with a valid URL");
}
}
range.offset(0,1).setValues(output);
} catch(e) {
handleError(e.message);
}
}
function flattenObject(obj) {
var f = new cFlatten.Flattener();
return f.flatten(obj);
}
function isURL(s) {
var regexp = /(http|https):\/\/(\w+:{0,1}\w*@)?(\S+)(:[0-9]+)?(\/|\/([\w#!:.?+=&%@!\-\/]))?/
return regexp.test(s);
}
function handleError(message) {
Logger.log(message);
throw new Error(message);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment