Google Sheets脚本开发：弹窗输入范围清除值保留公式

我明白你的需求啦——你想做一个「清除工作表」的自定义菜单项，让用户直接输入像A4:P6这样的单元格范围，执行后只清除范围内的单元格值，公式要完整保留。咱们先看看现有代码里的几个问题，然后直接上修正后的可行方案：

现有代码的主要问题 #

• 目前是让用户分两次输入起始单元格和单元格数量，不符合你想要的「直接输入完整范围」的交互逻辑
• 处理公式和内容的逻辑有偏差：clearContent()会同时清除值和公式，而且你的循环恢复公式的写法也有错误，没法正确还原公式

修正后的完整代码 #

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Clear Range (Keep Formulas)', 'clearRangeKeepFormulas')
    .addSeparator()
    .addSubMenu(ui.createMenu('Email')
      .addItem('Second item', 'menuItem2'))
    .addToUi();
}

function clearRangeKeepFormulas() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Pre op Board');
  
  // 弹窗让用户输入完整的单元格范围
  var result = ui.prompt(
    '请输入要清除的单元格范围（例如：A4:P6）',
    ui.ButtonSet.OK_CANCEL
  );
  
  var button = result.getSelectedButton();
  var rangeStr = result.getResponseText().trim();
  
  // 处理用户取消或未输入的情况
  if (button === ui.Button.CANCEL || rangeStr === '') {
    ui.alert('操作已取消或未输入有效范围');
    return;
  }
  
  try {
    // 获取用户指定的范围
    var targetRange = sheet.getRange(rangeStr);
    
    // 先保存范围内所有的公式（空单元格会返回空字符串）
    var formulas = targetRange.getFormulas();
    
    // 清除范围的所有内容（包括值和公式）
    targetRange.clearContent();
    
    // 把之前保存的公式重新设置回去，只保留公式、清除值
    targetRange.setFormulas(formulas);
    
    ui.alert('已成功清除指定范围的值，公式已保留！');
  } catch (e) {
    // 处理无效范围的报错，给用户友好提示
    ui.alert('输入的范围无效，请检查格式后重试：' + e.message);
  }
}

function menuItem2() {
  SpreadsheetApp.getUi().alert('You clicked the second menu item!');
}

代码关键逻辑解释 #

• 交互优化：改成一次弹窗让用户输入完整范围，同时增加了取消按钮和空输入的判断，避免无效操作
• 公式保留核心逻辑：
  1. 先用getFormulas()捕获目标范围内所有单元格的公式（无公式的单元格会返回空字符串）
  2. 用clearContent()一次性清除范围内的所有内容（值+公式）
  3. 最后用setFormulas(formulas)把之前保存的公式重新写入，这样就实现了「清除值、保留公式」的效果
• 错误处理：用try-catch包裹核心逻辑，捕获无效范围格式的报错，给用户清晰的提示

这样修改后，你的自定义菜单就能完美实现预期功能啦！

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