I have the name for event and end date (without start date) in Google Sheets.

Column B is the name of the event and column C is end date.

The script says execution successful but events are not getting added in Google Calendar.

I am the owner and have full rights/permission.

Here's the script:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Sync to Calendar');
  var item = menu.addItem('Commit', 'syncCalendar');
  item.addToUi();
  }
function calendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById("calendarid**");
  var name = spreadsheet.getRange('B3:B250').getValue();
  var start = new Date(spreadsheet.getRange('C3:C250').getValue()).getTime();
  var end =  new Date(spreadsheet.getRange('C3:C250').getValue()).getTime();
  eventCal.createEvent(name,new Date(start),new Date(end));
  Logger.log('Reminder is added to your calendar');
}

CodePudding user response：

You have to make a loop as follows

const cal = '################@gmail.com'

function calendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById(cal);
  var lastRow = spreadsheet.getLastRow()
  var name = spreadsheet.getRange('B3:B'   lastRow).getValues().flat();
  var start = spreadsheet.getRange('C3:C'   lastRow).getValues().flat();
  var end = spreadsheet.getRange('C3:C'   lastRow).getValues().flat();
  name.forEach((n, i) => {
    try {
      var id = eventCal.createEvent(n, new Date(start[i].getTime()), new Date(end[i].getTime())).getId();
      console.log(id)
    } catch (e) {
      console.log(i   ' '   e)
    }
  })
  Logger.log('Reminder is added to your calendar');
}

flat()

forEach()

CodePudding user response：

With the help of @Mike Steelson this script is working.

However, when you run the code again, it creates duplicates in the calendar.

Is there any way to avoid this?

const cal = '[email protected]'

function calendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById(cal);
  var lastRow = spreadsheet.getLastRow()
  var name = spreadsheet.getRange('B3:B'   lastRow).getValues().flat();
  var start = spreadsheet.getRange('C3:C'   lastRow).getValues().flat();
  var end = spreadsheet.getRange('C3:C'   lastRow).getValues().flat();
  name.forEach((n, i) => {
    try {
      var id = eventCal.createEvent(n, new Date(start[i].getTime()), new Date(end[i].getTime())).getId();
      console.log(id)
    } catch (e) {
      console.log(i   ' '   e)
    }
  })
  Logger.log('Reminder is added to your calendar');
}