I've found this super userful code that I have managed to add to a Google Sheet (total newbie here) but I have a couple of problems.
The first is to disregard blanks. I want to select a list, get links or stats for all URLs in the list. But if there is a single missing value in the list, it creates an error. It should be possible just to set it to disregard blank or non-conforming values and continue.
Second, for the analytics, I'm getting a return like:
{shortUrlClicks=20, longUrlClicks=23}
so with long lists of data, almost all of it is irrelevant. I just want the value for the total number of clicks on the short link. Is it possible to just return, for example:
20
I've tried pasting in the Mid
function from here but using it but I just get a shower of error messages. All suggestions targeted at idiot-level user gratefully read carefully.
Full code I'm using added:
var SHORT = "short", INFO = "info", LONG = ' long';
function onOpen() {
SpreadsheetApp.getUi()
.createMenu("New Link Shortener")
.addItem("Shorten Links","createShorts")
.addItem("Get Analytics","getInfo")
.addItem("Extract Long URL", "getLong")
.addToUi()
}
function createShorts() {
performAction(SHORT);
}
function getInfo() {
performAction(INFO);
}
function getLong() {
performAction(LONG);
}
function performAction(action) {
var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
var output = [], url, value, index;
for(var i = 0, iLen = data.length; i < iLen; i++) {
value = data[i][0];
switch(action) {
case SHORT:
url = UrlShortener.Url.insert({longUrl: value}), index = -1;
output.push([url.id]);
break;
case INFO:
url = UrlShortener.Url.get(value, {projection: 'ANALYTICS_CLICKS'}), index =2;
//var a = url.analytics.allTime;
var a = url.analytics.week.shortUrlClicks;
var b = ([flattenObject(a)]);
output.push(b);
break;
case LONG:
url = UrlShortener.Url.get(value), index = 2;
output.push([url.longUrl]);
break;
}
}
range.offset(0, index).setValues(output);
}
function flattenObject(obj) {
var f = new cFlatten.Flattener();
return f.flatten(obj);
}