1

In a spreadsheet, I have a app script for count hours in a google calendar and the output is copied in the spreadsheet.

A few days ago, anything worked fine.

but today (monday July 1 2013 ), when I try run the script, every time, I get the message "Authorized required".

http://cl.ly/Q0bd

I press in "Authorized" button, and re-run, and again get the message "Authorized required".

the code in a gist

// add menu
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [{name:"Calcular Horas", functionName: "calculateHours"}];
ss.addMenu("Hours", menuEntries);
// calcular al iniciar
//calculateHours();
}
function authorize() {
var oauthConfig = UrlFetchApp.addOAuthService("calendar");
var scope = "https://www.googleapis.com/auth/calendar";
oauthConfig.setConsumerKey("anonymous");
oauthConfig.setConsumerSecret("anonymous");
oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");
oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
}

/*
* Count hours of events with same name
*/
function countHours(calId, eventName){
authorize();
var cal = CalendarApp.getCalendarById(calId);
var key = "...";
var query = encodeURIComponent(eventName);
calId = encodeURIComponent(calId);
var params = {
method: "get",
oAuthServiceName: "calendar",
oAuthUseToken: "always",
};
var url = "https://www.googleapis.com/calendar/v3/calendars/"+
calId+"/events?q=" + query + "&key=" + key;
var request = UrlFetchApp.fetch(url, params);
//Logger.log(url);
var response = Utilities.jsonParse(request.getContentText());
var items = response.items;
var start, end;
var hours = 0;
for ( i = 0 ; i < items.length ; i++){
if ( items[i].status != "cancelled" ){
if ( items[i].summary == eventName ){
start = items[i].start.dateTime;
end = items[i].end.dateTime;
start = new Date(start.replace(/-/g,'/').replace(/[A-Z]/,' ').substr(0,19) );
end = new Date(end.replace(/-/g,'/').replace(/[A-Z]/,' ').substr(0,19));
hours = hours + ( end - start ) / ( 1000 * 60 * 60 );
}
}
}
return hours;
}

function calculateHours(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheets()[0];
var rows = s.getDataRange();
var nRows = rows.getNumRows();
var values = rows.getValues();
// from second row
for ( var i = 1; i < nRows ; i ++){
var row = values[i];
var h = countHours(row[0], row[1]);
s.getRange(i+1, 3).setValue(h);
}
}

EDIT

When I change the line 

var url = "https://www.googleapis.com/calendar/v3/calendars/"+
calId+"/events?q=" + query + "&key=" + key;

to 

var url = "https://www.googleapis.com/calendar/v3/calendars/"+
"primary"+"/events?q=" + query + "&key=" + key;

this work, but is only valid for the primary calendar.

JuanPablo
  • 23,792
  • 39
  • 118
  • 164

1 Answers1

0

finally I changed the access to calendar API to CalendarApp service with .getEvents

var cal = CalendarApp.getCalendarById(cal_id);
var this_year = new Date(2013,0,1);
var now = new Date()
var events = cal.getEvents(this_year, now, {search: event_name});
JuanPablo
  • 23,792
  • 39
  • 118
  • 164