创建日历事件到Google表格中标识的日历

Question

**我非常新，并且没有应用程序脚本。 / 我正在为高中的设施请求系统。教练，老师等可以为我们校园周围多个地点的请求/保留惯例或活动填写表格。我正在尝试从Google表格创建Google日历事件，其中日历名称由Google表单/工作表中选择的位置（D列）识别。例如，篮球教练将要求主健身房日期和时间（持续时间），并且将在主健身日历上创建一个日历活动。我已经创建和拥有多种日历。 我已经观看了几个YouTube视频，并尝试了一些示例脚本，但无济于事。任何帮助将不胜感激。

我认为我在代码中有几个错误。 [这是电子表格的副本。] [1]

        function createCalendarEvent() {
  let spreadsheet=SpreadsheetApp.getActiveSheet();
  let request=sheet.getDataRange().getValues();
  request.splice(0,1);
  let getOwnedCalendarsByName=Spreadsheet.getDataRange(4);
  let athleticsCalendar=CalendarApp.getOwnedCalendarsByName()
  
  

 request.forEach(function(entry){
 
 athleticsCalendar.creat(entry[2],entry[6],entry[8]);
  });

}


  [1]: https://docs.google.com/spreadsheets/d/1zdevdFr8R0xmQNoO3wveLgty189PrFVTlTe-HpJUfGE/edit?usp=sharing

Here is the code I am using right now.  I am getting the following errors:

12:07:17 PM Error   
ReferenceError: Title is not defined
createEvent @ Code.gs:32
selectCalendarID    @ Code.gs:26



    var calendarId = " ";
var typeId = 4; //define column which data to be use to select calendar id (in your case, "location" column 4)
var title = 3
var startDtId = 7;
var endDtId = 9;
 
function selectCalendarID() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();
 

  var startDt = sheet.getRange(lr, startDtId, 1, 1).getValue();
  var endDt = sheet.getRange(lr, endDtId, 1, 1).getValue();
  var type = sheet.getRange(lr, typeId, 1, 1).getValue();
    //select calendar id based on schedule type
    if(type == "Aux Gym" || type == "OL 2") {
    calendarId = "[email protected]"; 
  } else if (type == "Main Soccer Field" || type == "BK 2") {
    calendarId = "[email protected]"; 
  } else if (type == "GT 1" || type == "GT 2") {
    calendarId = "[email protected]";
  }
  createEvent(calendarId,title,startDt,endDt);
}
 
 
function createEvent(calendarId, title, startDt, endDt) {
  var cal = CalendarApp.getCalendarById(calendarId);
  var title = new Title(title);
  var start = new Date(startDtId);
  var end = new Date(endDtId);
  end.setDate(end.getDate() + 1);
  var type = type;
  var event = cal.createEvent(title,start, end, {
    }); 
}

Answer 1

下面的代码应该工作，但我没有测试。在您的工作方法中，您将需要通过ID获得日历，然后您可以在该日历下创建事件。

首先，您需要删除标题行：

  request.slide(1)

第二，您可以创建持有日历ID的变量，以便以后在循环中使用它们：

  let aux_gym_calendar_id = ""
  let main_soccer_field_calendar_id = ""
  ...

第三， switch 语句以在下面创建事件指定的日历。您应该使用 getCalendarById 而不是 getownedcalendarsbyname 因为 getCalendarByid 返回可以使用该日历的实例，您可以使用该实例来创建事件。

  switch (calendarsName) {
    case "Aux Gym":
     cal = CalendarApp.getCalendarById(aux_gym_calendar_id)
     cal.createEvent(entry[2], entry[6], entry[8])
     break;
    case "Main Soccer Field":
     cal = CalendarApp.getCalendarById(main_soccer_field_id)
     cal.createEvent(entry[2], entry[6], entry[8])
  }   

这是完整的代码：

 function createCalendarEvent() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let request = sheet.getDataRange().getValues()

  //remove header row
  request = request.slice(1)

  let cal = ""
 //Declare and init calendar id variables
  let aux_gym_calendar_id = ""
  let main_soccer_field_id = ""

  request.forEach(function (entry) {
   //get the calendar name
   let calendarsName = entry[3];

  //create event on selected calendar
  switch (calendarsName) {
    case "Aux Gym":
     cal = CalendarApp.getCalendarById(aux_gym_calendar_id)
     cal.createEvent(entry[2], entry[6], entry[8])
     break;
    case "Main Soccer Field":
     cal = CalendarApp.getCalendarById(main_soccer_field_id)
     cal.createEvent(entry[2], entry[6], entry[8])
  }
  });

  }

资源：

Answer 2

只是为了分享我在选择多个日历ID的工作。经过大量搜索（我不是程序员我的自我），我发现使用是否可以完成工作。

var calendarId = " ";
var typeId = 2; //define column which data to be use to select calendar id (in your case, "location" column 4)
var startDtId = 4;
var endDtId = 5;

然后，只需插入其他陈述是否

function selectCalendarID() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();

  var startDt = sheet.getRange(lr, startDtId, 1, 1).getValue();
  var endDt = sheet.getRange(lr, endDtId, 1, 1).getValue();
  var type = sheet.getRange(lr, typeId, 1, 1).getValue();
    //select calendar id based on schedule type
    if(type == "OL 1" || type == "OL 2") {
    calendarId = "[email protected]"; 
  } else if (type == "BK 1" || type == "BK 2") {
    calendarId = "[email protected]"; 
  } else if (type == "GT 1" || type == "GT 2") {
    calendarId = "[email protected]";
  }
  createAllDayEvent(calendarId,startDt,endDt);
}
}

function createAllDayEvent(calendarId, startDt, endDt) {
  var cal = CalendarApp.getCalendarById(calendarId);
  var start = new Date(startDt);
  var end = new Date(endDt);
  end.setDate(end.getDate() + 1);
  var type = type;
  var event = cal.createAllDayEvent(start, end, {
    }); 
}

希望这样的帮助

p/s
从这里有一个想法
如何更改Google嵌入事件颜色？
在这里