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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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