google apps script calendar authorization required

Question

i have a spreadsheet which stores data from a form. once data is submitted on form submit i have a script running that does some distance calculations using google maps and emails relevant information to the relevant stakeholders.

script is also run upon a column in the spreadsheet having certain data which then again emails the relevant information to the relevant stakeholders and is supposed to create an event in the default calendar.

script has been working fine but ever since i have inserted the calendarapp.getdefaultcalendar to start creating events i keep getting the authorization required screen prompting me that the script would to manage my calendars, view and manage my spreadsheets, send email as me and connect to external services. this is all fine and i accept the prompt except it keeps coming back up over and over again.

any suggestions please help. have i reached a some type of quota, what is it?

function myFunction() {

  var DISTANCE_COLUMN = 14;
  var DURATION_COLUMN = 15;
  var COST_COLUMN = 16;
  var ROUTE_COLUMN = 17;
  var STATE_COLUMN = 18;
  var BOOKED_COLUMN = 19;

  var EMAILED_STATE = "EMAILED";
  var BOOKED_STATE = "BOOKED";

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0]; // gets sheet 0 from active spreadsheet

  var data = getRowsData(sheet); // gets all the data from the active spreadsheet using 
                                 // the getRowsData function below 

  for (var i = 0; i < data.length; i++) { // for every row within the active spreadsheet
  var row = data[i];

  row.rowNumber = i + 2;

if(!row.distance) { // check to see if distance already exists
  var mapsURL = 'http://maps.google.com/maps/api/directions/xml?origin=' + row.pickUpAddress + ' Victoria&destination=' + row.dropOffAddress + ' Victoria&sensor=false&units=metric&mode=driving';
  var mapsXML = UrlFetchApp.fetch(mapsURL).getContentText();
  var mapsResponse = Xml.parse(mapsXML, true);
  var distance = mapsResponse.DirectionsResponse.route.leg.distance.getElement("value").getText();
  var duration = mapsResponse.DirectionsResponse.route.leg.duration.getElement("value").getText();

  var distance = Math.round(distance / 1000); // convert distance in to kilometers
  var duration = Math.round(duration / 60); //convert duration in to minutes
  var cost = 0;


  // Changes the date fields in to strings so that we can compare for tarif one. 
  var formattedPickUpTime = Utilities.formatDate(row.pickUpTime, "GMT+1000", "HHmmss");
  var formattedFromTime = Utilities.formatDate(new Date("12/30/1899 23:00:00"), "GMT+1100", "HHmmss");
  var formattedToTime = Utilities.formatDate(new Date("12/31/1899 04:00:00"), "GMT+1100", "HHmmss");


  var mapsRoute = '"http://maps.google.com/maps?f=d&source=s_d&saddr=' + row.pickUpAddress + ' Victoria&daddr=' + row.dropOffAddress + ' Victoria&mode=driving"';

  sheet.getRange(row.rowNumber, DISTANCE_COLUMN).setValue(distance); // set distance value
  sheet.getRange(row.rowNumber, DURATION_COLUMN).setValue(duration); // set duration value
  sheet.getRange(row.rowNumber, COST_COLUMN).setValue(cost); // set cost value
  sheet.getRange(row.rowNumber, ROUTE_COLUMN).setValue(mapsRoute); // set map of route value
}

if(!row.state) { // check to see if emails have already been sent
  stakeholderEmailFYI(row, distance, duration, cost, mapsRoute); // send an FYI email to the stakeholder
  // customerEmailConfirmation(row); // send a confirmation email to the customer
  sheet.getRange(row.rowNumber, STATE_COLUMN).setValue(EMAILED_STATE); // change state value to SENT      
}

if(row.sendBooking == "Y") { // check to see if emails have already been sent
  // customerEmailConfirmation(row); // send a confirmation email to the customer

  var eventStartTime = row.pickUpTime;
  // End time is calculated by adding an hour in the event start time
  var eventEndTime = new Date(row.pickUpTime.valueOf()+60*60*1000);

  var cal = CalendarApp.getDefaultCalendar().createEvent(row.fullName, eventStartTime, eventEndTime, {location: row.pickUpAddress});

  // cal.createEvent(row.fullName, eventStartTime, eventEndTime, {location: row.pickUpAddress});
  sheet.getRange(row.rowNumber, BOOKED_COLUMN).setValue(BOOKED_STATE); // change state value to SENT
}
}
}

Answer 1

Try to revoke authorizations and re-authorize the script, sometimes it solves the problem.

To do so : 2 possibilities :

1. You have received a mail telling you you authorized this app, this mail has a link to an automatic uninstall feature (but this is when the app is written by someone else or another of your accounts
2. You know the name of the script and you don't have so much scripts, go to your Google profile/security/revoke access , there is a list of all the scripts you authorized and a button for each of them to uninstall.

3. (Yes I know I said 2 :) use the url of your script which is something like below :

   https://script.google.com/macros/d/MZMgUL88c4hd6qj_G2Y7-rKtIT_UhbT9n/edit?uiv=2&tz=Europe/Brussels&docTitle=test+sto&csid=tAeLIRLHehPkQGfa6zUYzpg.11577354092571548890.6605670643166911759&mid=ACjPJvG-INu6kmfFfm649tuH-6KYNVpaz5G9uEy4CUMmcKAVn7RYVi8euALNy9dfPWl6gkCloq5D6SKrqczHteazKR2F41v5K5bqbw&hl=en_US

remove everything after "/edit?..." including "/edit?"

and add /manage/uninstall

so that it becomes something like this :

https://script.google.com/macros/d/MZMgUL88c4h_G2Y7-rKtIT_UhbT9n/manage/uninstall

This will bring you to a page like this one :

Click uninstall and your done... virgin as a virgin...

Then re-authorize to see if things go better.