0

I found this absolutely wonderful answer, which helped me cobble together a script. Bear with me, I'm not the most educated at the Google Apps Script, but I'm getting there!

Create Google Calendar Events from Spreadsheet but prevent duplicates

This is specifically to show how to prevent duplicates, but this is unfortunately my specific problem. I've worked out that it's due to my script not creating the eventids on the spreadsheet, that my script is supposed to recognise and therefore avoid a duplicate.

Can anyone help me with where I've gone wrong?

This is how the headers on my spreadsheet are lined up, the bolded ones actually apply to the script:

0 Date Deposited | 1 Customer | 2 Date Finals | 3 Outbound |4 Inbound| 5 AST - TY |6 AST - BV|7 Seats|8 Meals|9 FF |10 Bedding |11 Special |12 WHC|13 Docs|14 Blank |15 Start Time |16 End Time|17 EventID WHC|18 WHC Title |19 Docs Title |20 EventID Docs

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export WHCs",
    functionName : "exportWHCs"
  },
                {
    name : "Export Docs",
    functionName : "exportDocs"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportWHCs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 4;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "pma5g2rd5cft4lird345j7pke8@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[12]);  // First column
    var title = row[18];           // Second column
    var tstart = new Date(row[15]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[16]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var id = row[17];              // Sixth column == eventId
    // Check if event already exists, update it if it does

 try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"));
      var newEvent = cal.createEvent(title, tstart, tstop).addEmailReminder(5).getId();
      row[17] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);


}

function exportDocs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 4;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "pma5g2rd5cft4lird345j7pke8@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[13]);  // First column
    var title = row[19];           // Second column
    var tstart = new Date(row[15]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[16]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var id = row[20];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"));
      var newEvent = cal.createEvent(title, tstart, tstop).addEmailReminder(5).getId();
      row[20] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

EDIT: This is what I tried, after seeing the comments from Serge, same columns as above, but it still won't populate the eventID column, eg 17 :(

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 4;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "pma5g2rd5cft4lird345j7pke8@group.calendar.google.com";// use default claendar for tests
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[12]);  // First column
    var title = row[18];           // Second column
    var tstart = setTimeToDate(date,row[15]);
    var tstop = setTimeToDate(date,row[16]);
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var type = row[21];
    var times = row[22]
    var id = row[17]; 
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');// this is to "force error" if the event does not exist, il will never show for real ;-)
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop); // create a "normal" event
      row[17] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[17]);// make it an event Serie
    }
    event.setTitle(title);
    if(type=='PM'){
      var recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);// we need to keep start and stop otherwise it becomes an AllDayEvent if only start is used
    }else if(type=='PW'){
      var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);
   }
    debugger;
    data[i]=row;// update data array (row contains ID from row[17]=newEvent)
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}
Community
  • 1
  • 1
Ingrid
  • 15
  • 2
  • 8
  • please read [this post](http://stackoverflow.com/questions/25515765/create-google-calendar-recurring-events-from-spreadsheet), you are making the same errors (about dates/time for example) so I won't explain again. – Serge insas Sep 11 '14 at 11:54
  • Thanks so much for responding Serge! I did indeed see your post, but after directly duplicating it and altering the column numbers only, I still had the same issue, ie. it not populating the eventID column. I'm sure it's something I'M doing wrong, I just can't for the life of me work out what I'm doing! I've edited the above question to show you the duplicate I tried, combining the code from your other post, and the additional info you popped below. – Ingrid Sep 12 '14 at 06:12
  • 1
    Here is [the link to a testSheet](https://docs.google.com/spreadsheets/d/1EOLfICNewy4UB4OaFW5QR24lBPRW2SqX_svMaXRXWRc/edit?usp=sharing) I made following your setup. It works as expected and writes the ID in the right column. Give it a try and feel free to comment is something goes wrong. (sheet in read only, make a copy to use and update calendar ID before use) – Serge insas Sep 12 '14 at 07:55
  • You're my hero Serge - you just helped me work it out! That testsheet also worked perfectly for me, and I realised that because I've setup the spreedsheet on my system to pre-populate rows with text (up to 1000), the script itself isn't finishing (timeout error), which means it doesn't even get to the point of creating the eventID. At least I think that's the reason! Here's the link [link](https://docs.google.com/spreadsheets/d/1KYKBTyP8sxaoP0L2RvWx8xuqF9dV5l7spvcozVgHelM/edit?usp=sharing) - do you think I might be right? Thank you SO much again Serge... I really appreciate your help. – Ingrid Sep 12 '14 at 08:34
  • Waow ! first time I'm being a hero for someone ;-) great moment in a boy's life ! thanks There are multiple solutions for timeout errors, could you start a new thread on this particular aspect maybe ? – Serge insas Sep 12 '14 at 09:14

1 Answers1

1

Here is the script adapted to your column setup. I removed the parts that you didn't use.

test sheet available here

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "insas.be_79s808un50h66cm5d2pi19oeb0@group.calendar.google.com";// use default claendar for tests
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[12]);  // WHC
    var title = row[18];           // WHC Title
    var tstart = setTimeToDate(date,row[15]);// start time
    var tstop = setTimeToDate(date,row[16]);// end time
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var id = row[17]; //EventID WHC
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');// this is to "force error" if the event does not exist, il will never show for real ;-)
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop); // create a "normal" event
      row[17] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[17]);// make it an event Serie
    }
    event.setTitle(title);
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131