how to automatically add a schedule from Google Sheets into Calendar without create a duplicate even-CodePudding

This part works... now i need automate and create only new records add in the spreadsheet. Please, i need a help!

function scheduleShifts() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("Dados!CJ3").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var signups = spreadsheet.getRange("Dados!CI5:CK3500").getValues();
  for (x=0; x<signups.length;x  ) {
    var shift = signups[x];
    var startTime = new Date(shift[0]);
    var endTime = new Date(shift[1]);
    var volunteer= shift[2];
    eventCal.createEvent(volunteer,startTime,endTime);
    Utilities.sleep(300)
  }
}

CodePudding user response：

In your situation, for example, in order to check the duplicated event, how about using a status column? When your script is modified, it becomes as follows.

Modified script:

function scheduleShifts() {
  var statusColumn = "CL"; // As a sample, this script uses the column "CL".
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("Dados!CJ3").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var signups = spreadsheet.getRange("Dados!CI5:CL3500").getValues();
  var rangeList = [];
  for (x = 0; x < signups.length; x  ) {
    var shift = signups[x];
    if (shift[3] == "done") continue;
    rangeList.push(statusColumn   (x   5));
    var startTime = new Date(shift[0]);
    var endTime = new Date(shift[1]);
    var volunteer = shift[2];
    eventCal.createEvent(volunteer, startTime, endTime);
    Utilities.sleep(300)
  }
  spreadsheet.getRangeList(rangeList).setValue("done");
}

This script uses the column "CL" as the status column. When this script is run, when the colum "CL" has no value of "done", the event is created and put the value of "done" to the column "CL". When the script is run again, the rows which have the value of "done" at the column "CL" are skipped. By this, the duplicated events are not created.

Note:

This script uses the column "CL" as the status column. When you want to put this for other column, please tell me.

Reference:

getRangeList(a1Notations)