Google Sheets脚本执行完成无数据填充及双向同步需求求助

1. 数据筛选导入需求 #

• 将Transportation工作表D2:K区域的数据，按Dashboard工作表D2:D3的日期范围匹配Transportation表C2:C列的日期，同时筛选出K2:K列为FALSE的未确认行程数据，导入至Dashboard表U7:AB区域
• Dashboard表Y7:Y、AB7:AB列需显示复选框，分别对应Transportation表H2:H、K2:K列的复选框状态

2. 现有问题 #

提供的fetchDataFromSheet1脚本执行后无数据填充，即使Transportation表K列存在FALSE的测试行

3. 新增双向同步需求 #

实现Dashboard与Transportation双向同步：在Dashboard表AB列勾选确认行程后，同步将Transportation表对应行K列从FALSE改为TRUE，使该数据在Dashboard已确认表格中显示

现有脚本 #

function fetchDataFromSheet1() {
  var transportationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transportation");
  var dashboardSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Dashboard");
  var unconfirmedFilter = "False"; // Update with the desired transportation option

  var dashboardDate = dashboardSheet.getRange("D2").getValue();
  var transportationData = transportationSheet.getRange("C2:C").getValues();

  var filteredData = transportationData.filter(function(row) {
    var transportationDate = row[0]; // Assuming dates are in column C (index 0)
      return (
      transportationDate instanceof Date &&
      transportationDate.toDateString() === dashboardDate.toDateString()
    );
  });

  var data = transportationSheet.getDataRange().getValues();

  var filteredData = data.filter(function(row) {
  return row[10] === unconfirmedFilter; // Filter based on column K (index 10)
  });

  // Check if filteredData is empty
  if (filteredData.length === 0) {
    return; // Exit the function without making any changes
  }

  // Write filtered data from Transportation to Dashboard starting from row 7 and column U
  targetSheet.getRange(7, 21, filteredData.length, filteredData[0].length).setValues(filteredData);

  // Insert checkboxes in column Y (index 25) and column AB (index 28)
  var checkboxRangeY = targetSheet.getRange(7, 25, filteredData.length, 1);
  checkboxRangeY.insertCheckboxes();

  var checkboxRangeAB = targetSheet.getRange(7, 28, filteredData.length, 1);
  checkboxRangeAB.insertCheckboxes();
}

问题分析与修正方案 #

现有脚本核心问题 #

1. 重复覆盖筛选逻辑：先按日期筛选后，又重新赋值filteredData为仅按K列筛选的结果，日期筛选完全失效
2. 未使用日期范围：仅读取D2单个日期，未用到D3的结束日期，不符合范围筛选要求
3. 类型不匹配：unconfirmedFilter设为字符串"False"，但Google Sheets复选框的FALSE是布尔值，导致筛选无结果
4. 变量未定义：使用targetSheet但未赋值，实际应为dashboardSheet
5. 数据范围错误：取整个工作表数据而非需求的D2:K区域，会导入多余列
6. 复选框未同步状态：直接插入空白复选框，未对应Transportation表原有复选框状态

修正后完整脚本（含双向同步） #

// 筛选并导入未确认行程数据到Dashboard
function fetchUnconfirmedTrips() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const transSheet = ss.getSheetByName("Transportation");
  const dashSheet = ss.getSheetByName("Dashboard");

  // 获取Dashboard的日期范围
  const startDate = dashSheet.getRange("D2").getValue();
  const endDate = dashSheet.getRange("D3").getValue();
  
  // 获取Transportation表C2:K数据（含日期列用于筛选）
  const transRange = transSheet.getRange("C2:K");
  const transData = transRange.getValues();

  // 筛选：日期在范围内、K列（索引8）为布尔值FALSE
  const filteredData = transData.filter(row => {
    const tripDate = row[0];
    return tripDate instanceof Date &&
           tripDate >= startDate &&
           tripDate <= endDate &&
           row[8] === false;
  });

  // 清空Dashboard目标区域旧数据与复选框
  dashSheet.getRange("U7:AB").clearContent();
  dashSheet.getRange("U7:AB").clearDataValidations();
  dashSheet.getRange("AC7:AC").clearContent(); // 清空原行号存储列

  if (filteredData.length === 0) return;

  // 写入D-K列数据到U-AB区域（去掉C列日期）
  const dataToWrite = filteredData.map(row => row.slice(1));
  dashSheet.getRange(7, 21, dataToWrite.length, dataToWrite[0].length).setValues(dataToWrite);

  // 同步H列（Dashboard Y列，第25列）复选框状态
  const hColumnValues = filteredData.map(row => [row[5]]);
  const yRange = dashSheet.getRange(7, 25, hColumnValues.length, 1);
  yRange.insertCheckboxes();
  yRange.setValues(hColumnValues);

  // 同步K列（Dashboard AB列，第28列）复选框状态
  const kColumnValues = filteredData.map(row => [row[8]]);
  const abRange = dashSheet.getRange(7, 28, kColumnValues.length, 1);
  abRange.insertCheckboxes();
  abRange.setValues(kColumnValues);

  // 存储Transportation原行号到Dashboard AC列，用于后续同步
  const matchedRowIndexes = transData
    .map((row, idx) => ({row, originalRow: idx + 2}))
    .filter(item => {
      const tripDate = item.row[0];
      return tripDate instanceof Date &&
             tripDate >= startDate &&
             tripDate <= endDate &&
             item.row[8] === false;
    })
    .map(item => [item.originalRow]);
  
  dashSheet.getRange(7, 29, matchedRowIndexes.length, 1).setValues(matchedRowIndexes);
}

// 监听Dashboard AB列编辑，同步更新Transportation K列
function onEdit(e) {
  const ss = e.source;
  const sheet = ss.getActiveSheet();
  const range = e.range;

  // 仅处理Dashboard表AB列（第28列）、第7行及以下的编辑
  if (sheet.getName() !== "Dashboard" || range.getColumn() !== 28 || range.getRow() <7) return;

  // 获取对应的Transportation原行号
  const originalRow = sheet.getRange(range.getRow(), 29).getValue();
  if (!originalRow) return;

  // 更新Transportation表K列（第11列）状态
  const transSheet = ss.getSheetByName("Transportation");
  transSheet.getRange(originalRow, 11).setValue(e.value === "TRUE");
}

使用说明 #

1. 执行fetchUnconfirmedTrips()，即可按日期范围筛选导入未确认行程，并同步复选框初始状态
2. 在Dashboard AB列勾选复选框时，onEdit触发器会自动同步更新Transportation对应行K列状态
3. 可给fetchUnconfirmedTrips()设置时间驱动触发器，实现定期自动刷新数据

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