Google Sheets to Calendar语言 - 日期语法有问题

Question

我尝试使用从电子表格创建谷歌日历事件的脚本，但防止重复，但我不能使它工作。在我看来，日期格式有问题，尽管我在电子表格中使用标准格式。

function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Export Events",
functionName : "exportEvents"
}];
sheet.addMenu("Calendar Actions", entries);
};
/**
* Export events from spreadsheet to calendar
*/
function exportEvents() {
var sheet = SpreadsheetApp.getActiveSheet();
var headerRows = 1;  // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var data = range.getValues();
var calId = "YOUR_CALENDAR_ID";
var cal = CalendarApp.getCalendarById(calId);
for (i=0; i<data.length; i++) {
if (i < headerRows) continue; // Skip header row(s)
var row = data[i];
var date = new Date(row[0]);  // First column
var title = row[1];           // Second column
var tstart = new Date(row[2]);
tstart.setDate(date.getDate());
tstart.setMonth(date.getMonth());
tstart.setYear(date.getYear());
var tstop = new Date(row[3]);
tstop.setDate(date.getDate());
tstop.setMonth(date.getMonth());
tstop.setYear(date.getYear());
var loc = row[4];
var desc = row[5];
var id = row[6];              // Sixth column == eventId
// Check if event already exists, update it if it does
try {
var event = cal.getEventSeriesById(id);
}
catch (e) {
// do nothing - we just want to avoid the exception when event doesn't exist
}
if (!event) {
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
row[6] = newEvent;  // Update the data array with event ID
}
else {
event.setTitle(title);
event.setDescription(desc);
event.setLocation(loc);
// event.setTime(tstart, tstop); // cannot setTime on eventSeries.
// ... but we CAN set recurrence!
var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
event.setRecurrence(recurrence, tstart, tstop);
}
debugger;
}
// Record all event IDs to spreadsheet
range.setValues(data);
}

请，你可以看看什么是错的(我的电子表格:https://docs.google.com/spreadsheets/d/1OIZLOjG5cg32EbV-bI5c2qDWtbhbggDTTobPK_dYfyQ/edit?usp=sharing)。脚本工作，但没有结果在我的谷歌日历。

Answer 1

看到你的展示脚本时，我想date.getYear()可能需要修改为date.getFullYear()。我想这可能是你们本期The script works but there is no result in my Google Calendar.的原因。

并且，在您的脚本中，var date = new Date(row[0]),var tstart = new Date(row[2])和var tstop = new Date(row[3])可以写成var date = row[0],var tstart = row[2]和var tstop = row[3]。因为单元格值是日期对象。那么，下面的修改如何?

:

var date = new Date(row[0]);  // First column
var title = row[1];           // Second column
var tstart = new Date(row[2]);
tstart.setDate(date.getDate());
tstart.setMonth(date.getMonth());
tstart.setYear(date.getYear());
var tstop = new Date(row[3]);
tstop.setDate(date.getDate());
tstop.setMonth(date.getMonth());
tstop.setYear(date.getYear());

:

var date = row[0];
var title = row[1];
var tstart = row[2];
tstart.setDate(date.getDate());
tstart.setMonth(date.getMonth());
tstart.setYear(date.getFullYear());
var tstop = row[3];
tstop.setDate(date.getDate());
tstop.setMonth(date.getMonth());
tstop.setYear(date.getFullYear());

注意:

• 在var event = cal.getEventSeriesById(id);的情况下，我认为即使id为空且无效值，也不会发生错误。因此，try - catch可能不需要使用。
参考:

• getFullYear ()