Use goo.gl URL shortener when a Google Sheet is open

Question

My goal is to turn automatically the (very) long share links generate by Google drive for his documents in the short url: goo.gl/code

I found the beautiful script from Jacob Jan Tunistra here, but that post is now closed.

Jacob's code works like a charm but you need to run it. My needs are to run the script automatically when you open the document (and not to open the menu e push the button. How can I modify that script please?

function onOpen() {
SpreadsheetApp.getUi()
.createMenu("Shorten")
.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,1).setValues(output);
}

Answer 1

The UrlShortener service cannot be used by a simple trigger such as onOpen, because this service requires authorization. Instead, you should use an installable trigger. It can be created as follows:

1. From the Script Editor, go to Resources > Current project's triggers
2. Create a trigger for function rangeShort with parameters "from spreadsheet", "on open".

Reference: managing installable triggers.

Answer 2

Are you sure you would like to run it automatically after having opened it? That means all your previously generated short links will be overwritten by the new ones. You might want a short link to be generated automatically once you paste a long URL. For that, your code should look the following exact way:

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,1).setValues(output);
}

After that, from the Script Editor, go to Resources > Current project's triggers Create a trigger for function rangeShort with parameters "from spreadsheet", "on edit".