每日自动创建Google表单响应工作表标签及数据同步问题

问题根源 #

你现有代码的失效原因主要有两点：

1. 单元格范围错误：sheet.getRange("BA") 指定的是整列，而设置公式需要指向单个单元格（如A1），整列设置公式会导致异常。
2. 公式字符串的转义格式存在问题，尤其是日期拼接部分的引号处理。

修复公式设置的代码 #

修正单元格范围和公式转义逻辑后，即可正常生效：

function createNewDailyTab() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // 统一生成"MM-dd"格式的当日名称，避免地区locale差异
  var todayStr = Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), "MM-dd");
  
  // 插入新工作表并移至最前端
  var newSheet = ss.insertSheet(todayStr);
  ss.moveActiveSheet(0);
  
  // 修正：在A1单元格设置公式，调整引号转义确保语法正确
  var formula = '=query(\'2024-11\'!A2:H,"Select * Where A= date \'"&TEXT(TODAY(),"yyyy-mm-dd")&"\'",0)';
  newSheet.getRange("A1").setFormula(formula);
}

更可靠的直接复制数据方案（替代公式） #

如果不想依赖公式的动态引用，直接通过脚本筛选数据并复制到新工作表，稳定性更高：

function createNewDailyTabWithData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName("2024-11"); // 表单响应工作表
  var today = new Date();
  var todayStr = Utilities.formatDate(today, ss.getSpreadsheetTimeZone(), "MM-dd");
  
  // 避免重复创建当日工作表
  if (ss.getSheetByName(todayStr)) {
    return;
  }
  
  var newSheet = ss.insertSheet(todayStr);
  ss.moveActiveSheet(0);
  
  // 获取源表全部数据
  var data = sourceSheet.getDataRange().getValues();
  // 筛选当日数据（保留表头，匹配日期忽略时间）
  var filteredData = data.filter(function(row, index) {
    if (index === 0) return true; // 保留表头行
    var rowDate = row[0];
    return rowDate instanceof Date && 
           rowDate.getDate() === today.getDate() &&
           rowDate.getMonth() === today.getMonth() &&
           rowDate.getFullYear() === today.getFullYear();
  });
  
  // 将筛选结果写入新工作表
  if (filteredData.length > 0) {
    newSheet.getRange(1, 1, filteredData.length, filteredData[0].length).setValues(filteredData);
  }
}

定时触发设置 #

无论使用哪种方案，都需要设置每日定时执行：

• 打开Apps脚本编辑器，点击左侧「触发器」图标
• 添加触发器，选择对应函数名，事件源选「时间驱动」，类型选「日计时器」，设置为你需要的上午时段（如9:00）

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