Shorten several URLs in Google Sheets when they are not placed consecutively

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

Replay

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

Category: google spreadsheets Time: 2016-07-29 Views: 0

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.175 (s). 12 q(s)