I'm trying to make a task-scheduler that can read an a Google Sheet full of tasks (task name, start time, end time, person-assigned-to) and automatically create a calendar event on the specified person's calendar.

This tutorial:https://cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar

...has basically all I need, except that it only takes a single CalendarID, whereas I think I need the CalendarID to be part of the loop.

The CalendarID's in this case are all email addresses.

Here's what I tried:

function scheduleTasks() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
var signups = spreadsheet.getRange("E9:H13").getValues();
for (x=0; x<signups.length;x  )
{
    var shift = signups[x];
    var calendarID = shift[3];
    var eventCal = CalendarApp.getCalendarById(calendarId);
    var task= shift[0];
    var startTime = shift[1];
    var endTime = shift[2];
    eventCal.createEvent(volunteer, startTime, endTime);
}
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
  .addItem('Schedule tasks now', 'scheduleTasks')
  .addToUi();
}

And this is the error I'm getting:

8:38:40 AM Error TypeError: Cannot read property 'createEvent' of null scheduleTasks @ Code.gs:12

Never used JS/Google Apps Script before now so any advice is appreciated, Thanks

my input data

CodePudding user response：

Modification points:

• calendarId of var eventCal = CalendarApp.getCalendarById(calendarId); is not declared. From your script and your showing sample image, I thought that calendarId is var calendarID = shift[3];.
• volunteer is not declared. From your showing sample image, I thought that var task = shift[0] might be volunteer.

When these points are reflected in your script, it becomes as follows.

Modified script:

function scheduleTasks() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var signups = spreadsheet.getRange("E9:H13").getValues();
  for (x = 0; x < signups.length; x  ) {
    var shift = signups[x];
    var calendarID = shift[3];
    var eventCal = CalendarApp.getCalendarById(calendarID);
    var task = shift[0];
    var startTime = shift[1];
    var endTime = shift[2];
    eventCal.createEvent(task, startTime, endTime);
  }
}

Note:

• In this modified script, when the calendar ID is not existing and you have not the write permission to the calendar of the calendar ID and the values of startTime, endTime are not the date objects, an error occurs. Please be careful about this.

CodePudding user response：

Just gave it a go and this seems to be working:

function scheduleTasks() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
var signups = spreadsheet.getRange("E9:H13").getValues();
for (x=0; x<signups.length;x  )
{
    var shift = signups[x];
    var calendarID = shift[3];
    if(calendarID){
      var eventCal = CalendarApp.getCalendarById(calendarID);
      var task= shift[0];
      var startTime = shift[1];
      var endTime = shift[2];
      eventCal.createEvent(task, startTime, endTime);
    }

}
}