1

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);
}
0

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);
}
0

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.