Google Apps Script日历同步至表格：筛选指定颜色事件与条件判断问题

1. 仅同步特定颜色的日历事件 #

通过CalendarApp.Event.getColor()方法获取事件颜色，利用官方提供的颜色常量筛选目标事件：

• 跳过红色年假事件：判断颜色为CalendarApp.EventColor.RED时直接跳过当前循环
• 仅保留蓝色事件：判断颜色不为CalendarApp.EventColor.BLUE时跳过

在循环内添加颜色判断逻辑：

var eventColor = events[i].getColor();
// 排除红色年假事件，仅处理蓝色事件
if (eventColor === CalendarApp.EventColor.RED || eventColor !== CalendarApp.EventColor.BLUE) {
  continue;
}

2. 修复条件判断的OR逻辑与模糊匹配问题 #

错误原因分析 #

• OR逻辑误用：description.includes("ASM Salford" || "Salford")是语法错误，正确写法是分别调用includes()并通过||连接
• includes()不支持通配符：要实现兼容大小写、拼写不全的模糊匹配，需使用正则表达式

优化的匹配方案 #

多条件OR判断

正确写法示例：

// 匹配"ASM Salford"或"Salford"，区分大小写
if (description.includes("ASM Salford") || description.includes("Salford")) {
  // 执行写入操作
}

模糊匹配（兼容大小写、内容不全）

使用正则表达式的test()方法，添加i标志忽略大小写：

// 匹配包含ASM的内容，忽略大小写
if (/ASM/i.test(description)) {
  // 执行写入操作
}

// 匹配包含ASM或Salford的内容，忽略大小写
if (/ASM|Salford/i.test(description)) {
  // 执行写入操作
}

// 匹配包含ASM且后面跟着Salford的内容（中间允许任意字符），忽略大小写
if (/ASM.*Salford/i.test(description)) {
  // 执行写入操作
}

完整优化后的代码 #

function getCalendarEvents() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 存储表格映射，减少重复代码
  var sheetMap = {
    "ASM Salford": { sheet: spreadsheet.getSheetByName("ASM Salford"), rowOffset: 12 },
    "ASM Mcr": { sheet: spreadsheet.getSheetByName("ASM Mcr"), rowOffset: 13 },
    "CCE Tameside": { sheet: spreadsheet.getSheetByName("CCE Tameside"), rowOffset: 9 },
    "Len Johnson": { sheet: spreadsheet.getSheetByName("Len Johnson"), rowOffset: 11 },
    "WMW Salford": { sheet: spreadsheet.getSheetByName("WMW Salford"), rowOffset: 6 },
    "Rad- Leeds": { sheet: spreadsheet.getSheetByName("Rad- Leeds"), rowOffset: 12 },
    "Consent": { sheet: spreadsheet.getSheetByName("Consent"), rowOffset: 3 },
    "B&B Leeds, Enhanced (Creative Response)": { sheet: spreadsheet.getSheetByName("B&B Leeds, Enhanced (Creative Response)"), rowOffset: 22 },
    "Rad- Sheffield": { sheet: spreadsheet.getSheetByName("Rad- Sheffield"), rowOffset: 10 },
    "Rad- Lancs": { sheet: spreadsheet.getSheetByName("Rad- Lancs"), rowOffset: 8 },
    "Rad- Mcr": { sheet: spreadsheet.getSheetByName("Rad- Mcr"), rowOffset: 12 },
    "Rad- Lpool": { sheet: spreadsheet.getSheetByName("Rad- Lpool"), rowOffset: 11 },
    "Rad- Calderdale": { sheet: spreadsheet.getSheetByName("Rad- Calderdale"), rowOffset: 8 },
    "Rad-Derby": { sheet: spreadsheet.getSheetByName("Rad-Derby"), rowOffset: 10 },
    "Rad- Bradford": { sheet: spreadsheet.getSheetByName("Rad- Bradford"), rowOffset: 10 },
    "St Catherines": { sheet: spreadsheet.getSheetByName("St Catherines"), rowOffset: 7 },
    "Rad- Burnley Blackpool": { sheet: spreadsheet.getSheetByName("Rad- Burnley Blackpool"), rowOffset: 17 }
  };
  var monitoringsheet = spreadsheet.getSheetByName("Monitoring- Apr 23-Mar 24");
  var cal = CalendarApp.getCalendarById('HIDDEN FOR SECURITY');
  var events = cal.getEvents(new Date("4/6/2023 1:00 AM"), new Date("4/5/2024 11:59 PM"));

  // 封装写入表格的函数，减少冗余代码
  function writeEventToSheet(sheetInfo, startTime, title, location, endTime) {
    var row = i + sheetInfo.rowOffset;
    sheetInfo.sheet.getRange(row, 1).setValue(startTime);
    sheetInfo.sheet.getRange(row, 2).setValue(title);
    sheetInfo.sheet.getRange(row, 4).setValue(location);
    sheetInfo.sheet.getRange(row, 7).setValue(startTime);
    sheetInfo.sheet.getRange(row, 8).setValue(endTime);
  }

  for (var i = 0; i < events.length; i++) {
    var event = events[i];
    var title = event.getTitle();
    var startTime = event.getStartTime();
    var endTime = event.getEndTime();
    var location = event.getLocation();
    var description = event.getDescription() || ""; // 处理描述为空的情况
    var eventColor = event.getColor();

    // 颜色筛选：排除红色事件，只保留蓝色事件
    if (eventColor === CalendarApp.EventColor.RED || eventColor !== CalendarApp.EventColor.BLUE) {
      continue;
    }

    // 写入监控表格
    monitoringsheet.getRange(i+3,2).setValue(title);
    monitoringsheet.getRange(i+3,3).setValue(startTime);
    monitoringsheet.getRange(i+3,4).setValue(endTime);
    monitoringsheet.getRange(i+3,6).setValue(location);
    monitoringsheet.getRange(i+3,7).setValue(description);

    // 模糊匹配写入对应表格，忽略大小写
    if (/ASM.*Salford|Salford.*ASM/i.test(description)) {
      writeEventToSheet(sheetMap["ASM Salford"], startTime, title, location, endTime);
    }
    if (/ASM.*Mcr|Mcr.*ASM/i.test(description)) {
      writeEventToSheet(sheetMap["ASM Mcr"], startTime, title, location, endTime);
    }
    if (/CCE.*Tameside|Tameside.*CCE/i.test(description)) {
      writeEventToSheet(sheetMap["CCE Tameside"], startTime, title, location, endTime);
    }
    if (/Len.*Johnson|Johnson.*Len/i.test(description)) {
      writeEventToSheet(sheetMap["Len Johnson"], startTime, title, location, endTime);
    }
    if (/WMW.*Salford|Salford.*WMW/i.test(description)) {
      writeEventToSheet(sheetMap["WMW Salford"], startTime, title, location, endTime);
    }
    if (/Rad.*Leeds|Leeds.*Rad/i.test(description)) {
      writeEventToSheet(sheetMap["Rad- Leeds"], startTime, title, location, endTime);
    }
    if (/Consent/i.test(description)) {
      writeEventToSheet(sheetMap["Consent"], startTime, title, location, endTime);
    }
    if (/B&B.*Leeds|Leeds.*B&B/i.test(description)) {
      writeEventToSheet(sheetMap["B&B Leeds, Enhanced (Creative Response)"], startTime, title, location, endTime);
    }
    // Sheffield表格的列位置特殊，单独处理
    if (/Sheffield/i.test(description)) {
      var row = i + sheetMap["Rad- Sheffield"].rowOffset;
      sheetMap["Rad- Sheffield"].sheet.getRange(row,3).setValue(startTime);
      sheetMap["Rad- Sheffield"].sheet.getRange(row,4).setValue(title);
      sheetMap["Rad- Sheffield"].sheet.getRange(row,6).setValue(location);
      sheetMap["Rad- Sheffield"].sheet.getRange(row,9).setValue(startTime);
      sheetMap["Rad- Sheffield"].sheet.getRange(row,10).setValue(endTime);
    }
    if (/Lancs/i.test(description)) {
      writeEventToSheet(sheetMap["Rad- Lancs"], startTime, title, location, endTime);
    }
    if (/Rad.*Mcr|Mcr.*Rad/i.test(description)) {
      writeEventToSheet(sheetMap["Rad- Mcr"], startTime, title, location, endTime);
    }
    // Lpool表格列位置特殊，单独处理
    if (/Lpool/i.test(description)) {
      var row = i + sheetMap["Rad- Lpool"].rowOffset;
      sheetMap["Rad- Lpool"].sheet.getRange(row,4).setValue(startTime);
      sheetMap["Rad- Lpool"].sheet.getRange(row,5).setValue(title);
      sheetMap["Rad- Lpool"].sheet.getRange(row,7).setValue(location);
      sheetMap["Rad- Lpool"].sheet.getRange(row,10).setValue(startTime);
      sheetMap["Rad- Lpool"].sheet.getRange(row,11).setValue(endTime);
    }
    if (/Calderdale/i.test(description)) {
      writeEventToSheet(sheetMap["Rad- Calderdale"], startTime, title, location, endTime);
    }
    // Derby表格列位置特殊，单独处理
    if (/Derby/i.test(description)) {
      var row = i + sheetMap["Rad-Derby"].rowOffset;
      sheetMap["Rad-Derby"].sheet.getRange(row,2).setValue(startTime);
      sheetMap["Rad-Derby"].sheet.getRange(row,3).setValue(title);
      sheetMap["Rad-Derby"].sheet.getRange(row,5).setValue(location);
      sheetMap["Rad-Derby"].sheet.getRange(row,8).setValue(startTime);
      sheetMap["Rad-Derby"].sheet.getRange(row,9).setValue(endTime);
    }
    if (/Bradford/i.test(description)) {
      writeEventToSheet(sheetMap["Rad- Bradford"], startTime, title, location, endTime);
    }
    if (/St.*Catherines|Catherines.*St/i.test(description)) {
      writeEventToSheet(sheetMap["St Catherines"], startTime, title, location, endTime);
    }
    if (/Burnley/i.test(description)) {
      writeEventToSheet(sheetMap["Rad- Burnley Blackpool"], startTime, title, location, endTime);
    }
  }
}

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