Google Apps Script中range.setValues()报Spreadsheets服务异常求助

核心错误原因 #

1. 范围选择错误：原代码固定从第2行第1列开始选择操作范围，但筛选出的上周/本周/下周行是表格中分散的非连续行，直接覆盖第2行开始的区域会导致数据不匹配，触发Service error: Spreadsheets。
2. 日期计算逻辑偏差：原代码中本周一的计算方式在周日场景下会出错，导致周范围判断不准确。
3. 列索引不符合需求：需求要求修改L列（对应数组索引11），但代码中本周错误修改了M列（索引12）、下周修改了N列（索引13）。
4. 方法调用无效：valLastWeek.insertCheckboxes('yes') 是对数组调用Range专属方法，完全不生效，需对目标单元格范围调用该方法。
5. 空数组未处理：当某周无匹配行时，valLastWeek[0].length 会抛出undefined错误。

修复后的完整代码 #

function weeksRange() {
  const sheetName = '📅 Todos los eventos';
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const [header, ...values] = sheet.getDataRange().getValues();
  const lastRow = sheet.getLastRow();
  const lColumn = 12; // L列对应表格第12列（列号从1开始）

  // 修正周日期计算：兼容周日场景，正确获取本周一
  const now = new Date();
  const dayOfWeek = now.getDay(); // 0=周日, 1=周一...6=周六
  const mondayOfThisWeek = new Date(now);
  mondayOfThisWeek.setDate(now.getDate() - (dayOfWeek === 0 ? 6 : dayOfWeek - 1));
  mondayOfThisWeek.setHours(0, 0, 0, 0); // 统一设置为当天0点，避免时间干扰

  // 定义三周的时间范围（转时间戳）
  const weekRanges = {
    lastWeek: {
      start: new Date(mondayOfThisWeek.getTime() - 7 * 24 * 60 * 60 * 1000).getTime(),
      end: new Date(mondayOfThisWeek.getTime() - 1).getTime() // 本周一0点前1毫秒，即上周日23:59:59.999
    },
    thisWeek: {
      start: mondayOfThisWeek.getTime(),
      end: new Date(mondayOfThisWeek.getTime() + 13 * 24 * 60 * 60 * 1000 - 1).getTime() // 本周日23:59:59.999
    },
    nextWeek: {
      start: new Date(mondayOfThisWeek.getTime() + 7 * 24 * 60 * 60 * 1000).getTime(),
      end: new Date(mondayOfThisWeek.getTime() + 20 * 24 * 60 * 60 * 1000 - 1).getTime() // 下周日23:59:59.999
    }
  };

  // 记录各周匹配行的表格行号（从2开始，因第1行是表头）
  const targetRows = {
    lastWeek: [],
    thisWeek: [],
    nextWeek: []
  };

  values.forEach((row, index) => {
    const date = row[0];
    if (!(date instanceof Date)) return; // 跳过A列非日期的行
    const dateTime = date.getTime();

    // 判断当前行所属周
    if (dateTime >= weekRanges.lastWeek.start && dateTime <= weekRanges.lastWeek.end) {
      targetRows.lastWeek.push(index + 2);
    } else if (dateTime >= weekRanges.thisWeek.start && dateTime <= weekRanges.thisWeek.end) {
      targetRows.thisWeek.push(index + 2);
    } else if (dateTime >= weekRanges.nextWeek.start && dateTime <= weekRanges.nextWeek.end) {
      targetRows.nextWeek.push(index + 2);
    }
  });

  // 批量处理各周的"yes"设置与复选框插入
  Object.keys(targetRows).forEach(week => {
    const rows = targetRows[week];
    if (rows.length === 0) return; // 无匹配行则跳过

    // 批量设置L列为"yes"
    const yesRange = sheet.getRangeList(rows.map(row => `${sheet.getRange(row, lColumn).getA1Notation()}`));
    yesRange.setValue("yes");

    // 为这些行的L列插入复选框，值为"yes"时默认勾选
    yesRange.insertCheckboxes("yes", "no");
  });

  // 为L列所有已有数据的行插入复选框
  const allLColumnRows = sheet.getRange(2, lColumn, lastRow - 1, 1);
  const lValues = allLColumnRows.getValues();
  const checkBoxRows = [];
  lValues.forEach((val, index) => {
    if (val[0] !== "") {
      checkBoxRows.push(index + 2);
    }
  });
  if (checkBoxRows.length > 0) {
    sheet.getRangeList(checkBoxRows.map(row => `${sheet.getRange(row, lColumn).getA1Notation()}`)).insertCheckboxes("yes", "no");
  }
}

关键修复说明 #

1. 精准周范围计算：调整周一计算逻辑兼容周日场景，将时间范围设置到当天最后一刻，避免遗漏周日的行。
2. 分散行批量操作：通过记录匹配行的表格行号，使用getRangeList批量操作分散单元格，避免覆盖错误区域。
3. 符合需求的列操作：统一对L列进行修改，完全匹配需求要求。
4. 正确调用复选框方法：对Range对象调用insertCheckboxes，指定勾选值为"yes"实现默认勾选效果。
5. 异常场景处理：跳过A列非日期的行，无匹配行时直接跳过处理，避免报错。

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