1

I have a google script with the following code:

function hoursBetween(fromDate, toDate) {
  var dates = initializeDates();
  var from = dates[fromDate];
  var to = dates[toDate];
  var workCals = CalendarApp.getCalendarsByName('Work');
  var workCal = workCals[0];  
  var events = workCal.getEvents(from, to);
  var predictedMonthlyHours = 0;
 for (var i = 0; i < events.length; i++) {
   var event = events[i];
   var startTime = event.getStartTime();
   var endTime = event.getEndTime();
   var duration = endTime - startTime;
   duration =  parseFloat(duration/(3600*1000));
   duration -= dates['break'];
   predictedMonthlyHours += duration; 
 }
 return predictedMonthlyHours;
}

I have ran in the script editor with no problems (it asked for auth and I accepted) When I try to call the function from my spreadsheet I get the following error:

"You do not have permission to call getCalendarsByName"

I have also tried adding triggers with no result.

aosh5
  • 57
  • 10
  • Possible duplicate of [Google apps script error: "You do not have permission to call protect"](http://stackoverflow.com/questions/36365447/google-apps-script-error-you-do-not-have-permission-to-call-protect) – Rubén Sep 12 '16 at 14:21

2 Answers2

3

How are you trying to call from spreadsheet?

If you're trying to call via a spreadsheet formula =hoursBetween(), then it won't work. See Custom Functions..."Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data,"

Other options to set the cell value:

  • manual button click in a sidebar Add-on
  • manual click on a custom menu option
  • install a timed trigger to run automatically
Bryan P
  • 5,031
  • 3
  • 30
  • 44
  • I ended up using an Add-on. Basically I think it comes down to a Cell can't call a function that edits cells other than itself.... – aosh5 Jul 21 '15 at 02:47
0

Because the method is looking for multiple "Calendars" it's returning an array of calendars. So if you have only one calendar named "Work", you still need to call it and pick which one it is. So try changing the variable "workCals" to `

var workCals = CalendarApp.getCalendarsByName('Work')[0];

Adding the [0] will pick the first one available.

With out seeing the rest of the code, hard to decipher.

LennyZ71
  • 571
  • 4
  • 14
  • This is not relevant, it is an authentication problem. There is no issue with setting my variable to an array. I can call workCals[0] later... – aosh5 Jul 07 '15 at 02:43