如何让Google Apps Script忽略隐藏行？日历事件更新脚本优化

以下是修改后的脚本，核心加入行隐藏状态判断，跳过所有用户隐藏的行，减少不必要的日历API调用以缩短运行时间：

function updateEvents() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("Working");
  const data = sheet.getDataRange().getValues();

  if (data.length < 2) {
    console.warn("No data to process.");
    return;
  }

  const [headers, ...rows] = data;
  const eventIdIndex = headers.indexOf("onCalendar");
  const descriptionIndex = headers.indexOf("description");
  const locationIndex = headers.indexOf("location");
  const docUrlIndex = headers.indexOf("docURL");

  if (eventIdIndex === -1 || descriptionIndex === -1) {
    console.error("Required columns 'onCalendar' or 'Description' are missing.");
    return;
  }

  const calendarIds = [
    "bus.transportation@robinson.k12.tx.us",
    "c_c17913bb97e7da2d5ea98cb36acd5d216ecae11f6bf8bd044d6d3e85009f8dca@group.calendar.google.com"
  ];

  calendarIds.forEach(calendarId => {
    const calendar = CalendarApp.getCalendarById(calendarId);

    rows.forEach((row, index) => {
      // 获取表格中实际行号（数据从第2行开始）
      const rowNum = index + 2;
      // 跳过用户隐藏的行
      if (sheet.isRowHiddenByUser(rowNum)) return;

      const eventId = row[eventIdIndex];
      if (!eventId) return;

      try {
        const event = calendar.getEventById(eventId);
        if (!event) {
          console.warn(`onCalendar ${eventId} not found (Row ${rowNum})`);
          return;
        }

        event.setDescription(row[descriptionIndex] || "");
        if (locationIndex !== -1) {
          event.setLocation(row[locationIndex] || "");
        }

        if (docUrlIndex !== -1 && row[docUrlIndex] != "") {
          const calendarApiEventId = eventId.replace("@google.com", "");
          const resource = Calendar.Events.get(calendarId, calendarApiEventId);

          resource["attachments"] = [{
            fileUrl: row[docUrlIndex],
            title: "Original Trip Sheet"
          }];

          Calendar.Events.update(
            resource,
            calendarId,
            calendarApiEventId, {
              supportsAttachments: true
            }
          )
        }

        console.info(`Updated event ID ${eventId} in calendar ${calendarId} (Row ${rowNum})`);

      } catch (error) {
        console.error(`Failed to update event ID ${eventId} in calendar ${calendarId} (Row ${rowNum}): ${error.message}`);
        console.error(`Error details: ${error.stack}`);
      }
    });
  });
}

关键修改说明 #

• 遍历行时通过sheet.isRowHiddenByUser(rowNum)判断行是否被隐藏，隐藏则直接跳过处理逻辑
• 统一用rowNum指代表格实际行号，提升代码可读性与维护性

额外优化建议 #

若隐藏行数量极多，可提前过滤未隐藏行数据，减少后续循环次数：

// 替换原有的const [headers, ...rows] = data;部分
const headers = data[0];
// 提前过滤未隐藏行
const rows = data.slice(1).filter((_, index) => !sheet.isRowHiddenByUser(index + 2));

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