Create Recurring All Day Calendar Event from List of Dates in Google Sheet

0
votes

I have a list of dates in column A (starting at A2) paired with text for a title in column B (starting at B2). I have my calendar ID listed in cell E2.

I would like to send this data to Google Calendar to create recurring, all-day calendar events. This calendar should update when the spreadsheet is changed.

2
google-apps-scriptgoogle-sheetscalendarrecurring-events
What type of recurrence do you want your events to have? Weekly? Monthly? Yearly? – Iamblichus
I would like to have the event recur yearly. – N. Wass
So you want several events to be created in your calendar every time your spreadsheet is edited. I'm not sure this is a very good idea, since soon you will have mountains of duplicate events, even though this could be avoided by adding some condition that checks whether an event with those characteristics already exists. More critically, what should be done with previously created events that don't exist in the sheet anymore? Should they be deleted? – Iamblichus
Hello, did your issue get solved? Remember that in order to mark your question as solved you should accept whatever answer provided a solution to your problem. If that's not the case and your issue is not solved, consider explaining why that's not the case so that this community can help you. – Iamblichus
I definitely do not want to create mountains of multiple events. I have not found a resolution yet. I have tried the Google Sheets scripts and have not had success there. The Zapier zap is working except that it is creating events 1 day off from what they are listed as in my Google Sheet. – N. Wass

2 Answers

0
votes

Here is what I found for you. it's based on my understanding, may be I'm wrong. Just want to help you.

https://www.quora.com/How-do-I-automatically-add-events-to-a-Google-Calendar-from-a-Google-Sheet

or you can use app to do this task for you here is step by step guide

https://zapier.com/apps/google-calendar/tutorials/how-to-create-calendar-events-from-spreadsheet-tutorial

0
votes

I wrote this small piece of code that creates recurring events using the data in sheets.

I didn't write this in a trigger, so you would have to run this manually. It could be written in an onEdit trigger, but I don't think it would be the best idea, since you would soon end up having mountains of duplicate events, even though this could be avoided by adding some condition that checks whether an event with those characteristics already exists:

function createEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var firstRow = 2;
  var firstCol = 1;
  var numRows = lastRow - firstRow + 1;
  var numCols = 2;
  var data = sheet.getRange(firstRow, firstCol, numRows, numCols).getValues();
  var calId = sheet.getRange("E2").getValue();
  var cal = CalendarApp.getCalendarById(calId);
  var recurrence = CalendarApp.newRecurrence().addYearlyRule();
  for(var i = 0; i < data.length; i++) {
    var title = data[i][1];
    var date = new Date(data[i][0]);
    var event = cal.createAllDayEventSeries(title, date, recurrence);
  }
}

Also, if you wanted to delete previously created events when you create new events, you should keep track of all old events and edit this code a bit, but I'm not sure you want to delete them.

Update:

In case you want to create events when the sheet is edited, without having to run the function manually, I'd recommend using an onEdit trigger that creates an event corresponding to the row that has been written. Additionally, a condition can be added to create the event only if the data in the row is valid (columns A and B are not empty, and the value in column A is a valid Date).

The following function accomplishes all previous actions:

function createEvent(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range; // Edited range
  var rowIndex = range.getRow(); // Edited row index
  var firstCol = 1;
  var numCols = 2;
  var data = sheet.getRange(rowIndex, firstCol, 1, numCols).getValues()[0];
  var title = data[1];
  var date = data[0];
  // Check whether column A is a valid Date and column B is not empty:
  if(Object.prototype.toString.call(date) === '[object Date]' && title != "") {
    var calId = sheet.getRange("E2").getValue(); // Get calendar id from cell 'E2'
    var cal = CalendarApp.getCalendarById(calId);
    var recurrence = CalendarApp.newRecurrence().addYearlyRule();
    var event = cal.createAllDayEventSeries(title, date, recurrence); // Create event
  }  
}

In order to run on edit, this function needs an onEdit trigger. This trigger has to be installed, because a simple trigger cannot access services that require authorization.

You can install this trigger manually by following these steps (check this screenshot if you have problems when configuring the type of trigger).

You can also install this trigger programmatically, as explained here.

Please let me know if that works for you now. I hope this is of any help.