如何修改Gcal转Gsheets脚本以自动获取本地当前时间作为结束日期

核心修改说明 #

1. 替换手动输入的结束日期为本地当前时间，无需每日手动更新
2. 可选优化：将固定年份的7月1日起始日期改为动态获取当年7月1日（避免每年手动修改年份）

版本1：固定2022年7月1日为起始日期 #

修改后的完整脚本：

function export_gcal_to_gsheet() {
    var mycal = "email"; // 替换为你的日历ID/邮箱
    var cal = CalendarApp.getCalendarById(mycal);
    // 起始日期固定为2022年7月1日0点，结束日期为本地当前时间
    var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date());
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents();  
    var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]];
    var range = sheet.getRange(6, 1, 1, 14);
    range.setValues(header);
    for (var i = 0; i < events.length; i++) {
        var row = events.length + 6 - i;
        var myformula_placeholder = '';
        var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
        var range=sheet.getRange(row,1,1,14);
        range.setValues(details);
        var cell=sheet.getRange(row,7);
        cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
        cell.setNumberFormat('.00');
    }
}

版本2：动态获取当年7月1日为起始日期（推荐） #

如果希望每年自动使用当年的7月1日作为起始日期，修改起始日期的生成逻辑：

function export_gcal_to_gsheet() {
    var mycal = "email"; // 替换为你的日历ID/邮箱
    var cal = CalendarApp.getCalendarById(mycal);
    // 动态生成当年7月1日0点（月份从0开始计数，7月对应6），结束日期为本地当前时间
    var currentYear = new Date().getFullYear();
    var startDate = new Date(currentYear, 6, 1, 0, 0, 0);
    var endDate = new Date(); // 获取本地当前时间
    var events = cal.getEvents(startDate, endDate);
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents();  
    var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]];
    var range = sheet.getRange(6, 1, 1, 14);
    range.setValues(header);
    for (var i = 0; i < events.length; i++) {
        var row = events.length + 6 - i;
        var myformula_placeholder = '';
        var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
        var range=sheet.getRange(row,1,1,14);
        range.setValues(details);
        var cell=sheet.getRange(row,7);
        cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
        cell.setNumberFormat('.00');
    }
}

额外优化（可选） #

如果需要结束日期固定为当天的23:59:59而非脚本运行的当前时刻，可将endDate替换为：

var endDate = new Date(currentYear, new Date().getMonth(), new Date().getDate(), 23, 59, 59);

内容的提问来源于stack exchange，提问作者RoosterMagic22