Google Sheets – Shorten Several URLs Not Placed Consecutively

google sheetsgoogle-apps-script

I am using the following code to change my long urls to goo.gl urls. It works perfectly, except, the code breaks if the cells are not all in order. Example: I can get the shortened urls (all at once) for cells 1, 2, 3, 4…but not if I select cells 2 and 4. I get the message "Invalid Value".

Can anyone help me figure out the code I need to make it work for various cells? All of my long URLs are in one column, but not every cell has a url. Hope that makes sense. 

function onOpen() {
  var ui = SpreadsheetApp.getUi()
    ui.createMenu('Get Goo.gl Url')
    .addItem('Go !!','rangeShort')
    .addToUi()  
}

function rangeShort() {
  var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
  var output = [];
  for(var i = 0, iLen = data.length; i < iLen; i++) {
    var url = UrlShortener.Url.insert({longUrl: data[i][0]});
    output.push([url.id]);
  }
  range.offset(0,0).setValues(output);
}

Best Answer

It seems you want the script to ignore whatever blank cells might be within the range, rather than throwing an error on them. Here is a modification to rangeShort function that achieves this: it adds a condition if (data[i][0]) which will fail on empty cells, and then for those cells output.push(['']); is used instead of calling the shortener.

function rangeShort() {
  var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
  var output = [];
  for (var i = 0, iLen = data.length; i < iLen; i++) {
    if (data[i][0]) {
      var url = UrlShortener.Url.insert({longUrl: data[i][0]});
      output.push([url.id]);
    }
    else {
      output.push(['']);
    }
  }
  range.offset(0,0).setValues(output);
}
Related Topic