0

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

1 Answer 1

1

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

1
  • Woah! Worked. Many thanks! Commented Mar 21, 2018 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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