这里绝对是菜鸟!
背景:
但是......还有另一个要求,我失败的地方:
我到目前为止使用的代码如下:
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Sync to Calendar')
.addItem('Sync Now', 'sync')
.addToUi();
}
function sync() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var calendar = CalendarApp.getCalendarById('[email protected]');
var startRow = 2; // First row from which data should process > 2 exempts my header row
var numRows = sheet.getLastRow(); // Number of rows to process
var numColumns = sheet.getLastColumn();
var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var name = row[1]; //Name of Guest
var place = row[4]; //Add2
var room = row[9]; //Room Number
var inDate = new Date(row[10]); //Check-In Date
var outDate = new Date(row[11]); //Check-Out Date
var check1 = row[23]; //Booked/Blocked/Cancelled
var check2 = row[24]; //Event created and EventID (iCalUID) populated
if (check2 == "") {
var currentCell = sheet.getRange(startRow + i, numColumns);
var event = calendar.createEvent(room, inDate, outDate, {
description: 'Booked by: ' + name + ' / ' + place + '\nFrom: ' + inDate + '\nTo: ' + outDate
});
var eventId = event.getId();
currentCell.setValue(eventId);
}
}
}
我相信你的目标如下。
Cancelled
且“Y”列为空时,您要创建一个新事件。Cancelled
列不为空而“Y”列不为空时,您要删除现有事件。Cancelled
,您不想创建新事件。在这种情况下,如何进行以下修改?
在这个脚本中,为了检查事件是否已经被删除,使用了日历 API。因此,请在 Advanced Google services 中启用 Calendar API。
function sync() {
var calendarId = '[email protected]'; // Please set your calendar ID.
var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
var calendar = CalendarApp.getCalendarById(calendarId);
var startRow = 2; // First row from which data should process > 2 exempts my header row
var numRows = sheet.getLastRow(); // Number of rows to process
var numColumns = sheet.getLastColumn();
var dataRange = sheet.getRange(startRow, 1, numRows - 1, numColumns);
var data = dataRange.getValues();
var done = "Done"; // It seems that this is not used.
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var name = row[1]; //Name of Guest
var place = row[4]; //Add2
var room = row[9]; //Room Number
var inDate = new Date(row[10]); //Check-In Date
var outDate = new Date(row[11]); //Check-Out Date
var check1 = row[23]; //Booked/Blocked/Cancelled
var check2 = row[24]; //Event created and EventID (iCalUID) populated
// I modified below script.
if (check1 != "Cancelled" && check2 == "") {
var currentCell = sheet.getRange(startRow + i, numColumns);
var event = calendar.createEvent(room, inDate, outDate, {
description: 'Booked by: ' + name + ' / ' + place + '\nFrom: ' + inDate + '\nTo: ' + outDate
});
var eventId = event.getId();
currentCell.setValue(eventId);
} else if (check1 == "Cancelled" && check2 != "") {
var status = Calendar.Events.get(calendarId, check2.split("@")[0]).status;
if (status != "cancelled") {
calendar.getEventById(check2).deleteEvent();
}
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句