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);
}
  • Comments are not for extended discussion; this conversation has been moved to chat. – jonsca Mar 21 at 22:07

Try changing from

var a = url.analytics.week.shortUrlClicks;
   var b = ([flattenObject(a)]); 
    output.push(b);        
    break;

To

var a = url.analytics.week.shortUrlClicks;
//var b = ([flattenObject(a)]); 
Logger.log(a);
    output.push([a]);        
    break;

Ref:https://developers.google.com/url-shortener/v1/url#resource

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.