0

In my mind, the script below takes a form submission gathered on a sheet and books it into a calendar (calId). In reality, however, the script falls apart at var event = thisCalendar.createEvent(:

Exception: Invalid argument: booker.

function calendarUpload() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");
  var Avals = ss.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
   
  for (var i = 2; i <= lastRow ; i++) {
    var approvalStatus = sheet.getRange(i,1).getValue();
    var name = sheet.getRange(i,8).getValue();            //Event name.
    var description = sheet.getRange(i,9).getValue();     //Event description, agenda.
    var date = sheet.getRange(i,5).getValue();            //Event date.
      var formattedStart = Utilities.formatDate(new Date(date), 'Europe/London', 'MMMM dd, yyyy');
    var startTime = sheet.getRange(i,6).getValue();       //Event starting time.
      var formattedSTime = Utilities.formatDate(new Date(startTime), 'Europe/London','hh:mm');
    var endTime = sheet.getRange(i,7).getValue();         //Estimated end time of event.
      var formattedETime = Utilities.formatDate(new Date(endTime), 'Europe/London','hh:mm');
    var guests = sheet.getRange(i,15,1,10).getValues();   //Event guests by email address.
    var staffMember = sheet.getRange(i,10).getValue();    //Meeting with... Determines Calendar ID (CalID).
    var calId = sheet.getRange(i,11).getValue();          //Calendar.
    var booker = sheet.getRange (i,3).getValue();         //The person booking the meeting.
    var bookerEmail = sheet.getRange(i,4).getValue();     //Email booker, adds to guest list.
    var eventStatus = sheet.getRange(1,1,i,12).getCell(i,12);

    //Create eventName based on reason for meeting.
    if (name == "one-on-one"){
      var title = "ℳ " + booker + " and " + staffMember;
    } else {
      var title = name
    }

    Logger.log(eventStatus);  
    Logger.log(title);
    Logger.log(formattedStart);
    Logger.log(formattedSTime);
    Logger.log(formattedETime);
    Logger.log(guests);
   
    var startDateandTime = (formattedStart+" "+formattedSTime);
    var endDateandTime = (formattedStart+" "+formattedETime);
    Logger.log(startDateandTime);
  
    if (approvalStatus == "Approved" && eventStatus.isBlank()){
      var thisCalendar = CalendarApp.getCalendarById(calId);
      Logger.log('calId: '+calId);
      Logger.log(thisCalendar );
      var event = thisCalendar.createEvent(
        title,
        new Date(startDateandTime),
        new Date(endDateandTime),
        {guests: guests && bookerEmail, description: description});
      Logger.log('Event Series ID: ' + eventSeries.getId());
      var setEventStatus = sheet.getRange(i,12).setValue('Event Series ID: ' + event.getId());
    } else {
     Logger.log("No Events Found"); 
    }
  }
}

The logs seem to show that things go smoothly. Is there anything that you'd do differently? What can I do about the Exception error?

Example sheet.

BlueIris
  • 129
  • 1
  • 6
  • I've tested your script and it works on my end without getting the exception . However, I made a tweak to the date format on my end from this part "Mmmm" to "MMMM" because I'm unable see the created event on my test calendar with correct month (e.g. 5/17/2021, "5000" is shown when using 'Mmmm' format for month instead of "May"). It would be better if you can also share a sample sheet with sample data to precisely replicate your issue. You can also check this similar post about what is "[Ljava.lang.Object;@”? at https://stackoverflow.com/a/58600084/15384825 – SputnikDrunk2 May 17 '21 at 16:22
  • Please add the textual error message and a [mcve] in this case add some same data input and the corresponding expected results. – Rubén May 17 '21 at 20:58

0 Answers0