Selecting the day for a recurring event in CalendarApp on a spreadsheet

Question

I want to have google sheets create a recurring event on google calendar.

Is there a way to make this section of code dependent on variables from a spreadsheet? I would like to be able to select the days of the week on my spreadsheet instead of having to type them in.

.onlyOnWeekday([CalendarApp.Weekday.MONDAY,CalendarApp.Weekday.WEDNESDAY])

Here is my function

function newStudentCalendar() {

  var app = SpreadsheetApp;
  var sheet = app.getActiveSpreadsheet().getActiveSheet();   
  var studentName = sheet.getRange(2, 1).getValue();


  // set time variables for lesson creation
  var year = sheet.getRange(2, 5).getValue();
  var month = sheet.getRange(2, 3).getValue();
  var day1 = sheet.getRange(2, 4).getValue();
  var startTime = sheet.getRange(2, 6).getValue();
  var endTime = sheet.getRange(2, 7).getValue();


  //Creates the new students calendar  
 var calendar = CalendarApp.createCalendar(studentName);
Logger.log('Created the calendar "%s", with the ID "%s".',
    calendar.getName(), calendar.getId());

  var calendarID = calendar.getId()

  //Creates the recuring lessons 
 var lessons = CalendarApp.getCalendarById(calendarID).createEventSeries(studentName + " lesson",                                                                     
     new Date(month + day1 + ',' + year + ' ' + startTime),
     new Date(month + day1 + ',' + year + ' ' + endTime),
    CalendarApp.newRecurrence().addWeeklyRule()
        .onlyOnWeekdays([CalendarApp.Weekday.MONDAY, CalendarApp.Weekday.WEDNESDAY]));
Logger.log('Event Series ID: ' + lessons.getId());
}

Hello @Phaylontis I provided an answer. Kindly let me know if that's what you were looking for. Cheers! — carlesgg97, Nov 19 '19 at 15:02

score 1 · Answer 1 · answered Nov 19 '19 at 15:01

That is totally possible. In order to do so, simply add a line that reads a value from a specific cell. That specific cell will hold the name of the recurring day. Something like this:

var weekDay = SpreadsheetApp.getActive().getSheetByName('YOUR SHEET')
                .getRange('YOUR_WEEKDAY_RANGE').getValue();

Afterwards, and assuming the weekday is properly typed and in capitals, you can run this code to create the event with the appropriate repeating day:

// This will throw an error if the weekday is not valid.
var weekDayObject = CalendarApp.Weekday[weekDay];
CalendarApp.newRecurrence().addWeeklyRule()
           .onlyOnWeekdays([weekDayObject]));

Furthermore, if you would like to have multiple days in a cell, such as: MONDAY, WEDNESDAY, FRIDAY.

You could use the following code:

var weekDays = SpreadsheetApp.getActive().getSheetByName('YOUR SHEET')
                 .getRange('YOUR_WEEKDAY_RANGE').getValue();

var days = weekDays.split(',').map(function(i) { return CalendarApp.Weekday[i]; });
CalendarApp.newRecurrence().addWeeklyRule()
           .onlyOnWeekdays(days);

The general idea is that CalendarApp.Weekday is a regular Javascript/GAS object, and so you can access its properties both using the . operator or the [] operator.

Almost there. This works great for one day, is there a way to draw from an array of cells instead of having to put all of the days into a single cell? — Phaylontis, Nov 21 '19 at 01:57
Got it. Just loaded the two cells into a var before running function (i). — Phaylontis, Nov 21 '19 at 02:25
@Phaylontis No worries! Please consider [upvoting/accepting](https://stackoverflow.com/help/someone-answers) in case my answer was useful to you. Have a good one! — carlesgg97, Nov 21 '19 at 08:27

Selecting the day for a recurring event in CalendarApp on a spreadsheet

1 Answers1