Pull in analytics and click stats for goo.gl links in Google Sheets?

Question

I've used this guide to create a Google sheet that creates shortened goo.gl links - is there any way I can update this to show the click stats in the next column?

Answer 1

With the code below, you're able to insert the analytics information as well.

Code

var SHORT = "short", INFO = "info", LONG = ' long';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Shorten")
    .addItem("Create Links !!","createShorts")
    .addItem("Get analytics !!","getInfo")
    .addItem("Get 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: 'FULL'}), index = 1;
        var a = url.analytics.allTime;
        output.push([flattenObject(a)]);        
        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);
}

Explained

Continuing from the answer you used as a guide, a couple of things have changed:

• menu has another item
• code is re-used to perform both actions
• the cFlatter is used to flatten the object obtained from the url.analytics.allTime

Perform the same action as you would do for creating the urls, but now select the shortened urls and pick the new menu item in the Shorten menu.

You can change projection: "FULL" into:

• "ANALYTICS_CLICKS" - returns only click counts
• "ANALYTICS_TOP_STRINGS" - returns only top string counts (e.g. referrers, countries, etc)

or you can change url.analytics.allTime into:

• url.analytics.month
• url.analytics.week
• url.analytics.day
• url.analytics.twoHours

to alter the analytics information.

Screenshot

Library Key

Add the library key, of the cFlatten library, in the Script Editor under Resources > Libraries:

References

• Url: get
• GAS library cFlatten by Bruce McPherson (MqxKdBrlw18FDd-X5zQLd7yz3TLx7pV4j)