Add events from Google sheets to google calendar

Question

I am trying to write scirpt to add events from a spreadsheet to my google calendar. This is the script that I am using.

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("c_fe882662e583725f15fd4faa8c8fdf5124f3affe543778ecdcf0838d6eb17f26@group.calendar.google.com")

  var data = ss.getRange("A3:D"+lr).getValues();

    for(var i = 0;i<data.length;i++){ 

      cal.createEvent(data[i][2],data[i][4],data[i][5],{location: data[i][6], description: data[i][7]});

}
}

When I run the script I am getting the following error. Error Exception: Invalid argument: startTime addEvents @ Code.gs:10

This is the sheet that I am using with my dates.

https://docs.google.com/spreadsheets/d/1qG68-NLnq9LscPPzlnzRLCfHFIsN3v7V5zvWiVsG0qU/edit?usp=sharing

I want the title of the event to be column C, the start time to be Column E, the endtime to be Column F, Location G, and Description H.

Answer 1

Try changing this: cal.createEvent(data[i][2],data[i][4],data[i][5],{location: data[i][6], description: data[i][7]}); to this cal.createEvent(data[i][2],new Date(data[i][4]),new Date(data[i][5]),{location: data[i][6], description: data[i][7]});

Try changing this var data = ss.getRange("A3:D"+lr).getValues(); to this var data = ss.getRange("A3:H"+lr).getValues();

Try this:

function addEvents() {
  const ss = SpreadsheetApp.getActive();
  var sh = ss.getActiveSheet();
  var cal = CalendarApp.getCalendarById("jimesteban@jimesteban.com")
  var data = sh.getRange("A3:H" + sh.getLastRow()).getValues();
  for (var i = 0; i < data.length; i++) {
    cal.createEvent(data[i][2], data[i][4], data[i][5], { location: data[i][6], description: data[i][7] });
  }
}

Answer 2

Modification points:

• In your script, in the for loop, data[i][4],data[i][5] is used as the start and end time. And also, data[i][7] is used. But, atvar data = ss.getRange("A3:D"+lr).getValues();, 4 columns of "A" to "D" are retrieved. I thought that this might be the reason for your issue. In this case, it is required to be var data = ss.getRange("A3:H" + lr).getValues().

• But, when I saw your Spreadsheet, the start and end times don't have the year and month. In this case, 1899 year is used. Please be careful about this. From your Spreadsheet, I guessed that you might have wanted to use the year, month, and date from column "A".

When my understanding of your current issue and your goal, how about the following modification?

Modified script:

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("c_fe882662e583725f15fd4faa8c8fdf5124f3affe543778ecdcf0838d6eb17f26@group.calendar.google.com");
  var data = ss.getRange("A3:I" + lr).getValues();
  while (data[data.length - 1][0] == '') data.pop();
  for (var i = 0; i < data.length; i++) {
    var year = data[i][8].getFullYear();
    var month = data[i][8].getMonth();
    var date = data[i][8].getDate();
    data[i][4].setFullYear(year);
    data[i][4].setMonth(month);
    data[i][4].setDate(date);
    data[i][5].setFullYear(year);
    data[i][5].setMonth(month);
    data[i][5].setDate(date);
    cal.createEvent(data[i][2], data[i][4], data[i][5], { location: data[i][6], description: data[i][7] });
  }
}

• When this script is run, the start and end times are retrieved from the columns "E" and "F", respectively. And also, the year, month, and date are retrieved from column "A". Using these values, the start and end date are created and they are used with createEvent.

• When you want to use other values of year, month, and date instead of column "A", please tell me.

Note:

• From your reply of This sounds promising, the sheet that I am using is actually setting up a mail merge as well and the date in column A is for the mail merge and not for the calendar. I would actually like column I to be the date for the calendar events. , I modified the above script.

• From your reply of If I run this script twice (or multiple times) as I will continue to add events, it seems to duplicate the events that are already added. Any idea how to eliminate that?, I updated the above script as follows.

  function addEvents() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lr = ss.getLastRow();
    var cal = CalendarApp.getCalendarById("c_fe882662e583725f15fd4faa8c8fdf5124f3affe543778ecdcf0838d6eb17f26@group.calendar.google.com");
    var data = ss.getRange("A3:R" + lr).getValues();
    while (data[data.length - 1][0] == '') data.pop();
    var rangeList = [];
    for (var i = 0; i < data.length; i++) {
      if (data[i][17] == "created") continue;
      var year = data[i][8].getFullYear();
      var month = data[i][8].getMonth();
      var date = data[i][8].getDate();
      data[i][4].setFullYear(year);
      data[i][4].setMonth(month);
      data[i][4].setDate(date);
      data[i][5].setFullYear(year);
      data[i][5].setMonth(month);
      data[i][5].setDate(date);
      cal.createEvent(data[i][2], data[i][4], data[i][5], { location: data[i][6], description: data[i][7] });
      rangeList.push(`R${i + 3}`);
    }
    if (rangeList.length == 0) return;
    ss.getRangeList(rangeList).setValue("created");
  }