Question

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

2022-06-03

257

google-apps-script google-calendar-api

**我对 Apps Script 还很陌生。/ 我正在为我们的高中开发一个设施请求系统。教练、老师等可以填写表格来请求/预订校园内多个地点的练习或活动。我正在尝试从 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])
  }
  });

  }

资源： https://developers.google.com/apps-script/reference/calendar/calendar-app#getOwnedCalendarById(String)

Answer 2

只是想分享一下我选择多个日历 ID 的方法。经过大量搜索（我自己不是程序员），我发现使用 if 也可以完成工作。

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;

然后，只需插入这样的 if else 语句

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 日历嵌入事件颜色？这里 https://www.considerednormal.com/2014/04/adding-a-google-calendar-event-using-google-forms/