如何基于日期等双条件获取并设置Google Sheets单元格值？

需求说明 #

• 工作表包含「Roster」和「Holiday Request」两个标签页
• 需要在「Roster」中找到对应姓名且日期处于「Holiday Request」中休假日期区间内的单元格，将其值设为Holiday
• 当前困境：无法实现多日期区间的遍历处理，仅能提取单个日期值，无法获取多日期索引

现有代码问题分析 #

第一段代码的核心问题 #

function Holidays() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var master = ss.getSheetByName("Holiday Request");
  var target = ss.getSheetByName("Roster");
  
  var values = target.getRange("A3:NI20").getValues();
  var lc = target.getRange(3, 1, 1,target.getLastColumn()-1).getValues();

  var appdis = master.getRange("G2:G250").getValues();
  var name = master.getRange(2,3, master.getLastRow()-1, 1).getValues();
  var sdate = master.getRange(2,4, master.getLastRow()-1, 1).getValues();
  var edate = master.getRange(2,5, master.getLastRow()-1, 1).getValues();

  sdate.map(d =>{
    d[0]
    var shd = sdate.find(n => n[0] == d[0]) // 冗余：d本身就是当前遍历的sdate元素
      
      name.map(n =>{
        n[0] // 无效代码：未赋值或使用
        var sname = values.find(r => r[0] == n[0]) // 返回的是行数组，不是索引i
        
      // 拼写错误：lenght → length；逻辑错误：i是数字，sname是数组，i==sname永远不成立
      for(i=0, j=0; i<values.lenght, j<values[i].lenght; i++, j++){
        if(i == sname && j == shd)
          // values是数组，没有getRange方法；cells是数组的话也没有setValues方法
          var cells = values.getRange(i, j).getValues();
          cells.setValues("Holiday"); // setValues需要二维数组，且调用对象错误
      }
    })
  })
}

• 拼写错误：lenght应为length
• 逻辑混淆：将数组和Range对象的方法混用（数组没有getRange/setValues）
• 冗余代码：sdate.find(n => n[0] == d[0])完全没必要，d就是当前遍历的元素
• 索引判断错误：values.find返回的是行数组，不是数字索引，无法和i做相等判断

第二段代码的核心问题 #

function Holidays() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var hsheet = ss.getSheetByName("Holiday Request");
  var target = ss.getSheetByName("Roster");
  
  var rosvalues = target.getRange(1, 1, target.getLastRow()-1,target.getLastColumn()-1).getValues();
  var [, , dates, , ...rvalues] = target.getDataRange().getDisplayValues();

  // 错误：getDisplayValue()只取单个单元格，应该用getDisplayValues()获取多单元格
  var sdate = hsheet.getRange(3, 4, hsheet.getLastRow()-1, 1).getDisplayValue();
  var col = dates.indexOf(sdate); // 仅处理单个日期，未覆盖日期区间
  
  var sname = hsheet.getRange(2, 3, hsheet.getLastRow()-1, 1).getValues();
  
  var fcol = rvalues.reduce((o, r) => {
    if(r[0]) o[r[0]] = r[col]; // 依赖单个col，无法处理多日期
    return o;
  }, {});

  // 错误：sname是[[姓名1],[姓名2]]结构，val2为undefined
  var nameObj = sname.reduce((a, [val1, val2]) => {
    a[val1] = val2;
    return a;
  },{});
  
  var textObj = { "08 - 17": "Holiday", "17 - 02": "Holiday", "11 - 20": "Holiday"};
  var key1 = Object.keys(textObj);
  var key2 = Object.keys(nameObj);
  // 逻辑偏离需求：未关联姓名和日期区间
  var cell = rosvalues.map((r, i) => r.map((c, j) => fcol[c] && key1.includes(fcol[c]) && key2.includes(fcol[c]) ? textObj[fcol[c]] : null));

  cell.setValue("Holiday"); // cell是数组，没有setValue方法
}

• 数据获取错误：getDisplayValue()仅获取单个单元格，应使用getDisplayValues()批量获取
• 未处理日期区间：仅针对单个日期处理，未覆盖「开始日期-结束日期」的区间逻辑
• 结构解析错误：sname是二维数组，val2始终为undefined，导致nameObj无效
• 对象方法误用：数组没有setValue方法，只有Range对象可调用

解决方案代码 #

function setHolidays() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const holidaySheet = ss.getSheetByName("Holiday Request");
  const rosterSheet = ss.getSheetByName("Roster");

  // 1. 获取并处理休假申请数据：姓名 → 休假日期区间数组
  const holidayData = holidaySheet.getRange(2, 3, holidaySheet.getLastRow()-1, 3).getDisplayValues();
  const holidayMap = {};
  holidayData.forEach(row => {
    const [name, startDateStr, endDateStr] = row;
    if (!name || !startDateStr || !endDateStr) return; // 跳过空行
    
    const startDate = new Date(startDateStr);
    const endDate = new Date(endDateStr);
    
    if (!holidayMap[name]) holidayMap[name] = [];
    holidayMap[name].push({ start: startDate, end: endDate });
  });

  // 2. 获取排班表数据：日期表头、姓名行数据
  const rosterRange = rosterSheet.getDataRange().getDisplayValues();
  const dateHeaders = rosterRange[2]; // 第三行是日期表头（索引从0开始）
  const nameRows = rosterRange.slice(4); // 第五行及以后是姓名行（根据表格结构调整）

  // 3. 将日期表头转换为可比较的日期对象，并记录列索引
  const dateColMap = {};
  dateHeaders.forEach((dateStr, colIndex) => {
    if (!dateStr) return;
    dateColMap[colIndex] = new Date(dateStr);
  });

  // 4. 遍历排班表，标记休假单元格
  const updatedRoster = nameRows.map(row => {
    const name = row[0]; // 第一列是姓名
    if (!holidayMap[name]) return row; // 无休假记录，返回原行
    
    return row.map((cellValue, colIndex) => {
      const currentDate = dateColMap[colIndex];
      if (!currentDate) return cellValue; // 非日期列，返回原值
      
      // 判断当前日期是否在该姓名的任意休假区间内
      const isHoliday = holidayMap[name].some(interval => {
        return currentDate >= interval.start && currentDate <= interval.end;
      });
      
      return isHoliday ? "Holiday" : cellValue;
    });
  });

  // 5. 将修改后的数据写回排班表
  const writeRange = rosterSheet.getRange(5, 1, updatedRoster.length, updatedRoster[0].length);
  writeRange.setValues(updatedRoster);
}

代码说明 #

1. 数据映射：将休假申请数据整理为姓名→日期区间数组的结构，便于快速查找
2. 日期处理：将所有日期字符串转换为Date对象，确保区间判断的准确性
3. 批量处理：一次性读取所有数据，在内存中完成修改后再批量写回，减少Google Apps Script的API调用次数（提升性能）
4. 边界处理：跳过空行、非日期列，避免无效数据干扰

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