"User Rate Limit Exceeded. Please sign up" in Google App Script

Question

Now I'm trying to use Google URL Shortener in Google Spreadsheet(app script).

However, when I run the script, it shows an error: "User Rate Limit Exceeded. Please sign up (line 16, file "Code")"

I went to Google Developers Console and changed Quota limit as 1000 requests/second/user, but it seems like the script didn't even send the request because the traffic reports for this API is 0.

The following is my script. I wanna get the analytics clicks for shortened URL in the spreadsheet.

function onOpen() {
  total_clicks()
}

function total_clicks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var end = SpreadsheetApp.getActiveSheet().getLastRow();

  for (var i = 2; i <= end; ++i)
  {
    var short_url = sheet.getRange(i, 2).getValue();

if (short_url !== "") {
  var click_number = UrlShortener.Url.get(short_url, {
    projection: 'ANALYTICS_CLICKS'
  });

  var clicks_month = sheet.getRange(i, 5);
  clicks_month.setValue(click_number.analytics.day.shortUrlClicks); 

  var clicks_week = sheet.getRange(i, 6);
  clicks_week.setValue(click_number.analytics.week.shortUrlClicks);

  var clicks_month = sheet.getRange(i, 7);
  clicks_month.setValue(click_number.analytics.month.shortUrlClicks);    

  var clicks_all = sheet.getRange(i, 8);
  clicks_all.setValue(click_number.analytics.allTime.shortUrlClicks);  

}
else {

}
}
}

Answer 1

Note : this does not provide an exact answer but instead suggests a different way to get the desired result.

var key = 'AIzaSy___________X2e86zY5Ko';// get your own API key from the console

function total_clicks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var end = SpreadsheetApp.getActiveSheet().getLastRow();
  for (var i = 2; i <= end; ++i){
    var shortUrl = sheet.getRange(i, 1).getValue();
    if (shortUrl == "") {continue};
    var row = [];var header = [];
    var response = getClicks(shortUrl);
    //Logger.log(response);
    for(var prop in response){
      var element = response[prop];
      for(var detail in element){
        var el = element[detail];
        for(var d in el){
          if(typeof(el[d])=='object'){el[d]=toSt(el[d])};
          row.push(el[d]);
          header.push(d)
        }
      }
    }
   // Logger.log(row);
    sheet.getRange(i,2,1,row.length).setValues([row])
    }
    sheet.getRange(1,2,1,header.length).setValues([header])
}

function toSt(array){
  var ret = '';
  for(var idx in array){
    var val = array[idx]
    var str = '';
    for(var p in val){
      str+= val[p]+' : ';
    }
    ret+=str+'\n';
  }
  return ret;
}

function getClicks(shortUrl) {
  var response = UrlFetchApp.fetch('https://www.googleapis.com/urlshortener/v1/url?shortUrl='+shortUrl+'&projection=FULL&key='+key).getContentText()
  return JSON.parse(response);
}

Example result in spreadsheet (real screen is much wider as there are many results to show):

url = http://goo.gl/fbsS