Google Sheets按工作表名自动填日期及脚本并发错误解决问询

1. 更优方案：使用内置函数替代自定义函数 #

无需编写脚本，直接通过Google Sheets内置函数组合实现日期计算，彻底规避脚本并发限制问题。

在工作表2-31的A1单元格中输入以下公式：

='1'!A1 + VALUE(MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 99)) - 1

原理说明： #

• CELL("filename", A1)：返回当前工作表的完整标识（格式为[工作簿名]工作表名）
• FIND("]", CELL("filename", A1)) + 1：定位到工作表名称的起始位置
• MID(..., ..., 99)：提取出工作表名称（即数字1-31）
• VALUE(...)：将提取的文本转换为数字
• 最后与工作表'1'的A1日期相加并减1，得到对应日期

如果需要批量设置所有工作表的公式，可运行以下脚本一次性完成（仅需执行一次）：

function setDateFormulas() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const baseDateSheet = ss.getSheetByName('1');
  if (!baseDateSheet) return;
  
  const sheets = ss.getSheets();
  sheets.forEach(sheet => {
    const sheetName = sheet.getName();
    if (sheetName === '1' || isNaN(Number(sheetName))) return;
    
    sheet.getRange('A1').setFormula(`='1'!A1 + VALUE(MID(CELL("filename", A1), FIND("]", CELL("filename", A1)) + 1, 99)) - 1`);
  });
}

2. 若坚持使用自定义函数：优化脚本避免并发错误 #

如果必须保留自定义函数，可通过以下两种方式优化：

2.1 缓存自定义函数结果 #

利用CacheService缓存工作表名称，减少重复调用SpreadsheetApp服务的次数：

/**
 * Gets the Sheet Name of a selected Sheet.
 *
 * @param {number} option 0 - Current Sheet, 1 All Sheets, 2 Spreadsheet filename
 * @return The sheet name or list of names
 * @customfunction
 */
function SHEETNAME(option) {
  const cache = CacheService.getDocumentCache();
  const cacheKey = `sheetName_${option}`;
  let result = cache.get(cacheKey);
  
  if (result) {
    return option === 1 ? JSON.parse(result) : result;
  }
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const thisSheet = sheet.getName();
  
  if (option === 0) {
    result = thisSheet;
  } else if (option === 1) {
    const sheetList = ss.getSheets().map(val => val.getName());
    result = JSON.stringify(sheetList);
  } else if (option === 2) {
    result = ss.getName();
  } else {
    result = "#N/A";
  }
  
  cache.put(cacheKey, result, 3600); // 缓存1小时，可按需调整
  return option === 1 ? JSON.parse(result) : result;
}

2.2 改用批量更新脚本替代实时自定义函数 #

放弃在单元格中使用自定义函数，改用触发器或手动触发脚本批量更新所有工作表的A1单元格：

function updateAllDates() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const baseDateSheet = ss.getSheetByName('1');
  if (!baseDateSheet) return;
  
  const baseDate = baseDateSheet.getRange('A1').getValue();
  if (!(baseDate instanceof Date)) return;
  
  const sheets = ss.getSheets();
  sheets.forEach(sheet => {
    const sheetName = sheet.getName();
    const sheetNum = Number(sheetName);
    if (sheetName === '1' || isNaN(sheetNum)) return;
    
    const targetDate = new Date(baseDate);
    targetDate.setDate(baseDate.getDate() + sheetNum - 1);
    
    sheet.getRange('A1').setValue(targetDate);
  });
}

设置方法：

• 打开脚本编辑器，粘贴上述代码
• 点击「编辑」→「当前项目的触发器」
• 添加触发器，选择updateAllDates函数，触发事件可选择「打开时」或定时触发（如每天一次）

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