Is there a way to upload data from google sheets into calendar with concatenate data?

Question

I am very new to using google script and is something I have taken interest in recently, with no prior knowledge I have so far been trying to self teach. I have been playing around with a excel formula that will grab some data from a table on a daily basis and concatenate into a Date and Time format. So I have the following data in a table:

Event : Time Start : Time End : Location : Description

Work Rota: 24/01/2022 08:30:00 : 24/01/2022 19:30:00 : (postcode) : Work Shift for today

The first row being headers and the 2nd row with the data I am trying to utilise. The data in the Time Start and Time End column is data using the concatenate formula from another spreadsheet. If this was not a concatenate formula, the correct Date and Time format for the script works fine, However I understand due to the concatenate formula, the data for the string format is not suitable for the createEvent function.

I have been playing around with google script to find a way to take this data and being able to import it into my google calendar and after struggling now for multiply days! I cannot find a resolution.

Is there a formula or process, that can read the data in column 2 and 3 to convert the data into a suitable format needed for the createevent function This is the script I have been using.

function addEvents() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TEST");
var lr = ss.getLastRow();
var cal = CalendarApp.getCalendarById("calendarId");

var data = ss.getRange("A2:E" + lr).getValues();

for (var i = 0;i<data.length;i++){

cal.createEvent(data[i][0], data[i][1], data[i][2], {location: data[i][3], description: data[i][4]});
}
}

I appreciate anyone that may be able to help with this and thank you in advance!

Answer 1

I would have tried:

cal.createEvent(data[i][0], new Date(data[i][1]), new Date(data[i][2]), {location: data[i][3], description: data[i][4]});
}