How to createEvent based on form submits?

Question

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
26/05/2021 14:58:55	=to_date(A3)	Chim	09:05:00	11:45:00	chimeney

The event would always take place on the date of the form submit. The following code neatly makes an event from the the first submission, but I cannot get it to move on to the rest.

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

function toCale() {
  start     = new Date(data[0][1] + " " + data[0][3]);
  end       = new Date(data[0][1] + " " + data[0][4]);
  var calen = CalendarApp.getCalendarById("example@group.calendar.google.com");
  calen.createEvent(data[0][2], start, end,
    {location: "Arc du Triomphe", description: data[0][5]});
}

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

Any help would be appreciated.

Answer 1

I added location to the form and I don't write them to a sheet because they're already in the linked sheet but it would be trivial to add it to a sheet so I'll leave that for you to do.

function createEventsOnFormSubmit(e) {
  Logger.log(JSON.stringify(e));
  if (e.range.getSheet().getName() == "Linked Sheet Name") {
    const ss = SpreadsheetApp.getActive();
    const sh = ss.getSheetByName("Events");
    const cal = CalendarApp.getCalendarById('calendar id');
    let t = e.values[1].split('/')
    let y = Number(t[2]);
    let m = Number(t[0] - 1);
    let d = Number(t[1]);
    let st = e.values[3].split(/:| /);
    let shr;
    if (st[3] == "PM" && Number(st[0]) < 12) {
      shr = Number(st[0]) + 12;
    } else {
      shr = Number(st[0]);
    }
    let smin = Number(st[1]);
    let ssec = Number(st[2]);
    let startTime = new Date(y, m, d, shr, smin, ssec);
    let et = e.values[4].split(/:| /);
    let ehr;
    if (et[3] == "PM" && Number(et[0]) < 12) {
      ehr = Number(et[0]) + 12
    } else {
      ehr = Number(et[0]);
    }
    let emin = Number(et[1]);
    let esec = Number(et[2]);
    let endTime = new Date(y, m, d, ehr, emin, esec);
    cal.createEvent(e.values[2], startTime, endTime, { description: e.values[5], location: e.values[6] })
  }
}

My Linked Sheet:

Timestamp	Date	Name	StartTime	endTime	Description	Location
7/28/2021 13:53:17	7/29/2021	Redacted	12:00:00 PM	1:00:00 PM	Redacted	Redacted
7/28/2021 14:06:08	7/28/2021	Redacted	12:00:00 PM	1:00:00 PM	Redacted	Redacted
7/28/2021 14:10:25	7/28/2021	Redacted	12:00:00 PM	1:00:00 PM	Redacted	Redacted
7/28/2021 15:32:35	7/29/2021	Redacted	12:00:00 PM	1:00:00 PM	Redacted	Redacted

You can use this function to run the function without using a form submission:

function testnextfunction() {
  const e = JSON.parse('{"authMode":"FULL","namedValues":{"Timestamp":["7/28/2021 14:10:25"],"StartTime":["12:00:00 PM"],"Description":["Description"],"Date":["7/28/2021"],"endTime":["1:00:00 PM"],"":[""],"Location":["Location"],"Name":["Labs"]},"range":{"columnEnd":7,"columnStart":1,"rowEnd":4,"rowStart":4},"source":{},"triggerUid":"","values":["7/28/2021 14:10:25","7/28/2021","Labs","12:00:00 PM","1:00:00 PM","Description","Location",""]}');
  e.range = SpreadsheetApp.getActive().getSheetByName('Linked Sheet Name').getRange('A1');//any range in the sheet will suffice
  createEventsOnFormSubmit(e);//called the onFormSubmit function and supply event object
}

My Form:

Answer 2

Solution:

Your code is hardcoded to take event information from Row 2, as defined by getRange("A2:F"). Since you are using an onFormSubmit trigger, you would need to take the last row, which corresponds to the latest submission.

Sample Code:

var spreadsheet   = SpreadsheetApp.getActive().getSheetByName("sheet1");
var lr            = spreadsheet.getLastRow();
var data          = spreadsheet.getRange(lr,1,1,6).getDisplayValues();

function toCale() {
  start     = new Date(data[0][1] + " " + data[0][3]);
  end       = new Date(data[0][1] + " " + data[0][4]);
  var calen = CalendarApp.getCalendarById("example@group.calendar.google.com");
  calen.createEvent(data[0][2], start, end,
    {location: "Arc du Triomphe", description: data[0][5]});
}

Reference:

Class Sheet