0

Use case: User fills in a Slack workflow form with start date, end date and description > this data writes to a google spreadsheet > From spreadsheet: On change trigger runs the create_Events() function > see code below.


    function create_Events(){
    Utilities.sleep(1000);
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheetByName("Sheet2");
        var last_row = sheet.getLastRow();
      var data = sheet.getRange("A1:E" + last_row).getValues();
      var cal = CalendarApp.getCalendarById("xxx@group.calendar.google.com");
      var range = sheet.getRange("A2:E");
        Logger.log(data);
    
      for(var i = 1;i< data.length;i++){
            var title = data[i][3];
        Logger.log(title);
        var event = CalendarApp.getCalendarById("xxx@group.calendar.google.com").createEvent(data[i][0] + " >> " + title,
        new Date(data[i][1]),
        new Date(data[i][2]),
        {description: data[i][3]});
     Logger.log('Event ID: ' + event.getId());
        }
    range.clearContent();
    }

Issue: this is creating duplicate event entries on the calendar intermittently. 3 out of 5 times there is a duplicate event created on the calendar. Spreadsheet and Calendar are both shared with multiple users. I have ruled out multiple users creating simultaneous entries into the spreadsheet. I have tried altering the sleep duration but does not help. Appreciate any help here. Thank you.

Rubén
  • 34,714
  • 9
  • 70
  • 166
user18916265
  • 1
  • You need to create a column in the table that prevents duplicate events. The calendar has no problem with creating duplicate events and the script has no memory of previously created events. This is a design problem – Cooper Apr 23 '22 at 16:10
  • Thank you @Cooper, can you suggest how to code this? ( I am not a coder and hence asking for help here) – user18916265 Apr 24 '22 at 09:29
  • 1
    Stack Overflow is for professional and enthusiast programmers, people who write code because they love it. We feel the best Stack Overflow questions have a bit of source code in them. [on topic](https://stackoverflow.com/help/on-topic) – Cooper Apr 24 '22 at 13:43
  • The script logic doesn't look to be very good (using Utilities.sleep, creating events for all the rows...) , this might be better https://stackoverflow.com/a/15790894/1595451 – Rubén Apr 24 '22 at 20:07

0 Answers0