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?

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 acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.