I have a function to create events for field based workers. Eventually I will add this to an onEdit trigger and give the user (centrally based admin worker) some functionality to edit/delete events after they have been created. To do the latter, I believe I will need to be able to isolate the event ID.
Whilst I can write events to a calendar and retrieve the event ID and index of the event just created. I am having difficulty in writing that information back to the spreadsheet (this would be used for edit/delete later in my program).
ws.getRange(4,13,indexes.length,1).setValues(eventIds);
allows me to write ids to the spreadsheet but they are not writing to the correct row where the event was created.
How can I use the index stored in const indexes = [];
to tell the function the correct row to write the id to. Or is there a better way to achieve this?
Any help would be amazing, I am still very much in the learning stage with Google Apps Script. I have tried to research the problem to no avail and I was hoping that the wisdom of others might be able to show me where I am going wrong.
function createEvents(){
const ss = SpreadsheetApp.getActiveSpreadsheet();
const ws = ss.getSheetByName("Sheet Name");
const lr = Math.max.apply(0, ws.getRange('b:b').getValues().map(function (v, i) { if (v != '') { return i } else { return 0 } })) + 1;
const lc = ws.getLastColumn();
const cal = CalendarApp.getCalendarById("myCalId");
const allData = ws.getRange(4,2,lr-3,lc).getValues();
const eventIds = [];
const indexes = [];
allData.forEach(function(r,idx){
const startDate = new Date(r[4]);
const sDateHrs = startDate.setHours(8,0);
const endaDate = new Date(r[6]);
const eDateHrs = endaDate.setHours(18,0);
if(r[10] === true && r[7] === 'In Progress'){
var event = cal.createEvent(r[0], new Date(sDateHrs), new Date(eDateHrs), {location: r[1], description: r[3] });
var eventId = event.getId();
eventIds.push([eventId]);//pushing event ids to an array to call on later
indexes.push([idx]);//pushing the index of each id to use when writing the ids back to the spreadsheet
ws.getRange(4,13,indexes.length,1).setValues(eventIds); //places event ids into spreadsheet but not in the correct rows. I want the ids to be written to rows corresponding to the indexes array
return event;}});
}