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?
Best Answer
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.