我的记账表格学会了“读心术”，再也不会忘记软件到期了

function syncExpirationsToCalendar() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets(); // 获取所有 sheet 页面
  const calendar = CalendarApp.getDefaultCalendar(); // 可替换为指定日历
  const existingEvents = calendar.getEvents(new Date(), new Date("2100-01-01"));

  for (let s = 0; s < sheets.length; s++) {
    const sheet = sheets[s];
    const data = sheet.getDataRange().getValues();

    for (let i = 1; i < data.length; i++) {
      const software = data[i][0];
      const expiryRaw = data[i][2];
      const purchaseRaw = data[i][3];

      // 跳过空值或格式异常
      if (!software || !expiryRaw) continue;
      Logger.log(software); // 输出软件名
      Logger.log(expiryRaw); 
      Logger.log(purchaseRaw);
      const expiryDate = new Date(expiryRaw);
      expiryDate.setHours(11, 0, 0, 0);
      const purchaseDate = new Date(purchaseRaw);
      if (isNaN(expiryDate.getTime())) continue;
      if (expiryDate <= new Date()) continue;

      const title = `软件到期提醒: ${software}`;
      const alreadyExists = existingEvents.some(e => {
        return(e.getTitle() === title && e.getStartTime().getTime() === expiryDate.getTime());});
      Logger.log(alreadyExists);
      
      if (!alreadyExists) {
          const expiryEnd = new Date(expiryDate);
          expiryEnd.setHours(11, 30);
          const reminderDate = new Date(expiryDate);
          reminderDate.setDate(reminderDate.getDate() - 3);
          reminderDate.setHours(11, 0, 0, 0); // 设置为中午11点

          const endReminderDate = new Date(reminderDate);
          endReminderDate.setHours(11, 30); // 提醒持续半小时

          // 提前三天建事件（提醒）
          calendar.createEvent(`提前提醒: ${software}`, reminderDate, endReminderDate, {
            description: `软件 ${software} 将于 ${expiryDate.toDateString()} 到期`,
          });

          // 到期日事件
          // calendar.createAllDayEvent(`到期: ${software}`, expiryDate, {
          //   description: `软件 ${software} 今日到期`,
          // });

          calendar.createEvent(title, expiryDate, expiryEnd, {
            description: `软件 ${software} 到期（来自 sheet: ${sheet.getName()}）`,
            reminders: [{method: "popup", minutes: 3 * 24 * 60}],
          });
        
      }
    }
  }
}