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?

up vote 4 down vote accepted

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

enter image description here

Library Key

Add the library key, of the cFlatten library, in the Script Editor under Resources > Libraries: enter image description here

References

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.