Using Google URL Shortener API in Google Sheets for bulk URL shortening

Question

Recently come across similar issue to this question, where I want to add use the Google URL shortener API in Google Sheets for bulk URL shortening.

However, I keep running into this error:

Rate Limit Exceeded (line 12, file "Code")

within the code

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

so it's to do with this line

var url = UrlShortener.Url.insert({longUrl: data[i][0]});

Any thoughts how to fix this?

Answer 1

The message is pretty clear: you are invoking the service too often. This is discussed in Google URL Shortener 403 Rate Limit Exceeded which indicates that the acceptable rate is somewhere between 1 and 10 requests per second.

Use the method Utilities.sleep to slow down the requests. For example, Utilities.sleep(1000) within the loop will ensure a 1-second interval between calls.