Google Apps Script - Changing values in a range of google sheets cells

user_11899141 0
Viewed 771

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);
  }
}
}
google-apps-script google-sheets javascript
1 Answers

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.

user_15954510
Related