In the following example I use Apps Script to schedule Google calendar events based on spreadsheet input. This code works just fine however I need to tweak it to do a small manipulation on the source sheet as well. You can see here that I filter the range to only include rows that have "Pending" value in column D (r[3]). However I need to include a line of code in the loop so that after the filtered rows are synced to my Google Calendar the same cell value in column D changes to "Scheduled" for the respective cell. I have tried following this solution but could not implement it since I am new to JS.
Google Apps Script - .setValue in cell based on for loop matching
function calendarSync() {
var ss = SpreadsheetApp.getActiveSheet();
var calendarId = "My Calendar ID";
var eventCal = CalendarApp.getCalendarById(calendarId);
var eventArray = ss.getRange('A2:I500').getValues().filter(r => r[3] == "Pending Schedule");
for (x=0; x<eventArray.length; x++) {
var event = eventArray[x];
var eventName = event[0];
var startTime = event[1];
var endTime = event[2];
var exisEvents = eventCal.getEvents(startTime, endTime, {search: eventName}) //prevents creating duplicate events;
if (exisEvents.length == 0) {
eventCal.createEvent(eventName, startTime, endTime);
}
}
}
One simple solution is to change a bit logic of your script. Instead of using filter use an if statement then overwrite the whole range.
function calendarSync() {
var ss = SpreadsheetApp.getActiveSheet();
var calendarId = "My Calendar ID";
var eventCal = CalendarApp.getCalendarById(calendarId);
var eventArray = ss.getRange('A2:I500').getValues();
for (x = 0; x < eventArray.length; x++) {
var event = eventArray[x];
var eventName = event[0];
var startTime = event[1];
var endTime = event[2];
var status = event[3]; // Used to in the following comparison expression instead of filter
if (status === "Pending Schedule") {
var exisEvents = eventCal.getEvents(startTime, endTime, {
search: eventName
}) //prevents creating duplicate events;
if (exisEvents.length == 0) {
eventCal.createEvent(eventName, startTime, endTime);
eventArray[x][3] = "Scheduled"; // Update the status
}
}
}
ss.getRange('A2:I500').setValues(eventArray); // Overwrite the source data with the modified array
}
P.S. If you are using the default runtime instead of var it's better to use const and let, specially when writing complex scripts.