I'm working on a script that takes a list of tasks from Google Sheets and automatically creates events on Google Calendars, putting the event on the Calendar of the team member to whom the task is applied.

All that is working, now my issue is that I want to be able to run the script over the whole range of cells as tasks are added without creating duplicate events.

I tried using the method described here: https://stackoverflow.com/a/57785525/12412425 BUT this only checks the event/task name, which doesn't work for me because I will likely be assigning tasks with the same name, but different dates, etc.

My thought was to make an extra column where, after an event is created, the code writes an "X" in the cell, and as the code executes, it checks this cell, and if it already has an "X", the row will be skipped. But I'm struggling to wrap my head around how to identify the cell I need to write to.

Basically, I would want to iterate through the rows of the specified range, and if the "CalendarID" cell contains a value, you can assume an event was created, therefore in the next cell over, write an "X". The rest would just be a simple if-statement to check for the "X".

My input data

Can anyone advise on how to do this? Or an alternate strategy? (This is my first attempt at using Apps Script/JS)

Many thanks.

Current code:

  function scheduleTasks() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
var myRange = spreadsheet.getRange("E9:H").getValues();
for (x=0; x<myRange.length;x  )
{
    var myRow = myRange[x];
    var calendarID = myRow[3];

    if(calendarID){
      var eventCal = CalendarApp.getCalendarById(calendarID);
      var task= myRow[0];
      var startTime = myRow[1];
      var endTime = myRow[2];
      var existingEvents=Calendar.Events.list(calendarID);
      var eventArray=[]; 
      existingEvents.items.forEach(function(e){eventArray.push(e.summary)});
        if(eventArray.indexOf(task)==-1){ 
        eventCal.createEvent(task, startTime, endTime);
            }else{
      Logger.log('event exists already');
    }
    }

}

Where (I think) I'm trying to go with it (See comments)

  function scheduleTasks() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
var myRange = spreadsheet.getRange("E9:H").getValues();
for (x=0; x<myRange.length;x  )
{
    var myRow = myRange[x];
    var calendarID = myRow[3];

    if(calendarID){
      //if cell in "Scheduled" column, row x is false
      var eventCal = CalendarApp.getCalendarById(calendarID);
      var task= myRow[0];
      var startTime = myRow[1];
      var endTime = myRow[2];
      eventCal.createEvent(task, startTime, endTime);
      //write "X" in cell in "Scheduled" column, Row x
    }

}
}

CodePudding user response：

In your situation, how about the following modification?

Modified script 1:

In this sample script, the value of X is put in the "Scheduled" column.

function scheduleTasks() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var myRange = spreadsheet.getRange("E9:I"   spreadsheet.getLastRow()).getValues();
  var rangeList = [];
  for (x = 0; x < myRange.length; x  ) {
    var myRow = myRange[x];
    var calendarID = myRow[3];
    var scheduled = myRow[4];
    if (calendarID && scheduled != "X") {
      var eventCal = CalendarApp.getCalendarById(calendarID);
      var task = myRow[0];
      var startTime = myRow[1];
      var endTime = myRow[2];
      eventCal.createEvent(task, startTime, endTime);
      rangeList.push(`I${x   9}`)
    }
  }
  if (rangeList.length == 0) return;
  spreadsheet.getRangeList(rangeList).setValue("X");
}

• When this script is run, when "calendarID" is existing and "Scheduled" column has no value of "X", the script in the if statement is run. In this case, in order to put the value of "X", I used RangeList.

Modified script 2:

In this sample script, the value of the event ID is put in the "Scheduled" column.

function scheduleTasks() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var lastRow = spreadsheet.getLastRow();
  var myRange = spreadsheet.getRange("E9:I"   lastRow).getValues();
  var scheduledAr = [];
  for (x = 0; x < myRange.length; x  ) {
    var myRow = myRange[x];
    var calendarID = myRow[3];
    var scheduled = myRow[4];
    if (calendarID && !scheduled) {
      var eventCal = CalendarApp.getCalendarById(calendarID);
      var task = myRow[0];
      var startTime = myRow[1];
      var endTime = myRow[2];
      var event = eventCal.createEvent(task, startTime, endTime);
      scheduledAr.push([event.getId()]);
    } else {
      scheduledAr.push([scheduled]); // Modified
    }
  }
  spreadsheet.getRange("I9:I"   lastRow).setValues(scheduledAr);
}

• When this script is run, when "calendarID" is existing and "Scheduled" column has no value, the script in the if statement is run. In this case, in order to put the value of event ID, I used setValues.

References:

• getRangeList(a1Notations)
• setValues(values)