Creating a google calendar entry from a google form submission

Question

Trying to set up a google form that will automatically make a calendar entry for a community calendar. Google form questions and how they appear in the related google sheet are

1. Email (column B)
2. eventName (column C)
3. eventDate (column D)
4. startTime (column E)
5. enfTime (column F)
6. eventLocation (column G)
7. eventDescription (column H)

Using App script in the Google Sheet I have written the following:

function myFunction() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form responses 1");
  var last_row = sheet.getLastRow();
  var data = sheet.getRange("A1:H" + last_row).getValues();
  var cal = CalendarApp.getCalendarById("******@gmail.com");
  //Logger.log(data);

  for(var i = 0; i< data.length;i++){
    //index 0 =
    var event = CalendarApp.getDefaultCalendar()
    .createEvent(data[i][2],
    new Date(data[i][4]),
    new Date(data[i][3]),
    new Date(data[i][5]),
    new Date(data[i][7]),
    {location: data[i][6]});

 Logger.log('Event ID: ' + event.getId());
    
  }

}

I now get this error on run

Error - Exception: The parameters (String,(class),(class),(class),(class),(class)) don't match the method signature for CalendarApp.Calendar.createEvent. myFunction @ Code.gs:13

I am not experienced enough to solve it!

I was expecting it to populate a single calendar entry from a single form submission.

Answer 1

The syntax for createEvent() is:

• createEvent(title, startTime, endTime, options)
• String, Date, Date, options
• startTime and endTime are "the date and time when the event starts" - this requires add the date and time from the form submission.

The variables that you used were:

• Title, starttime, eventdate, endtime, description (as a date)

---

The script below follows the logic of your script. The main differences are:

• getDataRange() avoids the need to calculate last row, etc
• getDisplayValues() returns the date and time as strings, and can make it easier to add the time and date.
• created date/time (h/t How to combine date and time into one element in google app script?. There may be a "better" way to do this; I shall leave that to others to suggest.

---

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form responses 1");
  var data = sheet.getDataRange().getDisplayValues()
  var cal = CalendarApp.getCalendarById("<insert calendar id>");

  // start i at 1 to exclude header row
  for (var i = 1; i < data.length; i++){
    // get the date and time
    var month = data[i][3].substring(4, 5);
    var day = data[i][3].substring(0, 2);
    var year = data[i][3].substring(6);
    // Logger.log("DEBUG: month: "+month+", day:"+day+", year: "+year)
    var startMinutes = data[i][4].substring(0, 2);
    var startHours = data[i][4].substring(3, 5);
    // Logger.log("DEBUG: start minutes: "+startMinutes+", start hours: "+startHours)
    var endMinutes = data[i][5].substring(0, 2);
    var endHours = data[i][5].substring(3, 5);
    // Logger.log("DEBUG: end minutes: "+endMinutes+", end hours: "+endHours)
    var startDate = new Date(month + " " + day + ", " + year + " " + startHours + ":" + startMinutes + ":00");
    var endDate = new Date(month + " " + day + ", " + year + " " + endHours + ":" + endMinutes + ":00");
    // Logger.log("DEBUG: start Date: "+startDate+", end date: "+endDate)

    // get the other variables
    var title = data[i][2]
    var desc = data[i][7]
    var loc = data[i][6]
    var options = {
      description: desc,
      location: loc
    }

    // create the event
    var event = cal.createEvent(title,startDate,endDate,options)

    Logger.log('Event ID: ' + event.getId())
  }
}

---

Form SUBMISSION

---

CALENDAR

Note time difference is due to Time Zone (this calendar is in Austrealia)

---

UPDATE

This function, when installed as an onFormSubmit trigger will create an event as each new response is submitted.

// create an installable "onFormSubmit" trigger for this function

function formEventCreate(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form responses 1");
  var cal = CalendarApp.getCalendarById("<insert ID>");

  // get the variables
  var title = e.values[2]
  var desc = e.values[7]
  var loc = e.values[6]
  var options = {
    description: desc,
    location: loc
  }

  // get the date and time
  var month = e.values[3].substring(4, 5);
  var day = e.values[3].substring(0, 2);
  var year = e.values[3].substring(6);
  // Logger.log("DEBUG: month: "+month+", day:"+day+", year: "+year)
  var startMinutes = e.values[4].substring(3, 5);
  var startHours = e.values[4].substring(0,2);
  // Logger.log("DEBUG: start minutes: "+startMinutes+", start hours: "+startHours)
  var endMinutes = e.values[5].substring(3, 5);
  var endHours = e.values[5].substring(0, 2);
  // Logger.log("DEBUG: end minutes: "+endMinutes+", end hours: "+endHours)
  var startDate = new Date(month + " " + day + ", " + year + " " + startHours + ":" + startMinutes + ":00");
  var endDate = new Date(month + " " + day + ", " + year + " " + endHours + ":" + endMinutes + ":00");
  // Logger.log("DEBUG: start Date: "+startDate+", end date: "+endDate)

    // create the event
    var event = cal.createEvent(title,startDate,endDate,options)

    Logger.log('Event ID: ' + event.getId())
}

---