CODEWITHSUNDEEP
google-apps-scriptcalendar
BlueIris
BlueIris

Reputation: 149

How to createEvent based on form submit?

A question I've seen a few times on the major coding support fora, how to add an event to a calendar based on a form submit. Nevertheless, I'm struggling.

Timestamp form Date name startTime endTime descr
25/05/2021 13:05:05 =to_date(A2) Meeting 15:05:00 15:42:00 garden

The event would always take place on the date of the form submit. Nothing seems to come of the following code.

var spreadsheet   = SpreadsheetApp.getActive().getSheetByName("Form1");
var data          = spreadsheet.getRange("A2:F").getValues();

function toCal() {
    let [,  date, name, startTime, endTime, descr] = data;
    var calen = CalendarApp.getCalendarById("[email protected]");
    calen.createEvent(name, 
    new Date(date + startTime), 
    new Date(date + endTime),
    {location: "Brandenburger Tor", description: descr});
  }
}

The event just needs to be based on the last submission, so I can put it on a "on form submit" trigger.

I think the trouble comes from the date time. However if I just put in new Date("25/05/2021 15:05:00") instead of a the current thing, nothing appears in my calendar as well.

Any help would be appreciated.

Upvotes: 0

Views: 66

Answers (1)

Boris Baublys
Boris Baublys

Reputation: 1203

Format B2 to 2021-05-25 enter image description here

and try:

function toCal() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName("Form1");
  var data = spreadsheet.getRange("A2:F2").getDisplayValues();
  start = new Date(data[0][1] + " " + data[0][3]);
  end = new Date(data[0][1] + " " + data[0][4]);
  var calen = CalendarApp.getCalendarById("[email protected]");
  calen.createEvent(data[0][2], start, end,
    {location: "Brandenburger Tor", description: data[0][5]});
}

Upvotes: 1

Related Questions