CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
McChief
McChief

Reputation: 425

Create Calendar Events in Google Sheets triggered by Date

I want my Google Spreadsheet to automatically create a calendar event triggered by a date.

This is my Spreadsheet:

Google Spreadsheet

This is my sheet "H&S Reviews":

enter image description here

I want to automatically create a calendar event when a date in column K equals the date in K1.

I have created the following calendarEvent Google Apps Script: 

//Send Calendar Invites for H&S Reviews
function createCalendarEvent() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("H&S Reviews").activate();
  var lastRow = sheet.getLastRow();

  for (var i = 3;i<=lastRow;i++){

    var emailAddress = sheet.getRange(i, 9).getValue();
    var todaysDate = sheet.getRange(1, 11).getValue();
    var startDate = sheet.getRange(i, 11).getValue();
    var endDate = sheet.getRange(i, 7).getValue();  
    var eventName = sheet.getRange(i, 3).getValue();
    var description = sheet.getRange(i, 4).getValue();
        
 if (todaysDate == startDate){
 var calendar = CalendarApp.getCalendarById({embed?src=tga.assist%40uzabus.co.nz&ctz=Pacific%2FAuckland});
 var event =  calendar.createEvent(eventName, new Date(startDate), new Date(endDate)).setDescription(description);
Logger.log('Event ID: ' + event.getId());
    }
  }
}

The script doesnt create an event. I am also not sure if I have the setDescription coding correct. Would really appreciate help with this please.

Upvotes: 0

Views: 2592

Answers (1)

Rob
Rob

Reputation: 575

There are a few things that I think are tripping you up:

CalendarApp.getCalendarById(emailAddress)

This should be reading the calendar ID that you're wanting to modify, and not the email address string. Are you meaning to do getCaledarsByName(emailAddress) in that you have the calendars stored with the email address of the person as their name? If that's the case then I'll need to modify this answer a bit since getting the calendars by Name returns an array.

From the example it looks like you're wanting to create a calendar events in one calendar only. If that is the case, the calendar ID could be hard coded -- you can get the calendar ID by opening it in Google Calendars, clicking on the dots next to the calendar and then the 'ID' is everything after the /calendar/ part of the URL. 

var calendarDescription = CalendarApp.setDescription(description);

This should really be after you create the event since you're not wanting to modify the description of the CALENDAR or the Description of your calendars as a whole, but you are wanting to modify the description of the EVENT. This part of the code should be: 

if (todaysDate == startDate){
var calendar = CalendarApp.getCalendarById({{ID OF THE CALENDAR}});
var event =  calendar.createEvent(eventName, new Date(startDate), new Date(endDate));
event.setDescription(description);

I've also included the call to create the calendar variable and call to the CalendarApp after the comparison of the date strings so that it doesn't do this unnecessarily. You don't need to assign an event description variable since the return of the .setDescription is the calendar event itself and doesn't require anything done with the return. If you wanted to shorten this even more you can even chain them: 

var event = calendar.createEvent(eventName, new Date(startDate), new Date(endDate)).setDescription(description);

Next, the event that you're going to be creating has a start date that is multiple days before the end date. You might want to consider what the intent is here.

If you are wanting to create a short event to remind you to do something at a certain time of the day then you really ought to make sure that you simply add a start time and then create a new date variable that adds a certain amount of time to the start time to assign the 'endDate' to the event. For example, the 'Send Date' in the first row of your example sheet is '26/11/2019' and the 'Review' Date is '10/12/2019', which correspond to your 'startDate' and 'endDate' variables respectively.

Based on what the task name is, would creating an all day event possibly be better for your needs: 

var event = calendar.createAllDayEvent(title, date);

Lastly, some words of caution from having worked a lot with dates. Be very careful of the format when you are comparing dates to make sure everything is in the same format and time zone. If you continue to test if one is == to the other, then if you miss a day in running the script then you could miss adding events as a consequence.

Hope this helps.

EDIT: I hit post and I noticed a couple places where I believe 1 was accidentally used in place of the variable 'i' when referencing the ranges in the cells in your script. i.e: 

var description = sheet.getRange(1, 4).getValue();

Should be:

var description = sheet.getRange(i,4).getValue();

Upvotes: 1

Related Questions