0

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?

2

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.

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.