解决Google Apps Script为带加号开头值设置单元格批注失败问题

问题场景与现象 #

• 用Data Validation（基于区域的下拉列表）选择带加号开头的值（如+1 to modifier），通过Apps Script的onEdit触发器将所选值设置为单元格批注
• 下拉选择的值显示正常，但运行原代码时报错，无法添加批注；尝试用cell.setNote('"' & value);修改后仍报错

原代码：

function addNotescToRange() {
// Get the "GraveLayout" sheet
const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GraveLayout");

// Define the range (A1:B2) (to set Notes for each cell based off its value)
const range1 = sheet1.getRange("A1:B2");

// Get the values in the range as a two-dimensional array
const values = range1.getValues();

// Loop through each cell in the range
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      const cell = range1.getCell(i + 1, j + 1); // +1 for zero-based indexing
      const value = values[i][j];

      // Set the note for the cell using its value
      cell.setNote(value);
    }

}
}

错误原因 #

1. 当下拉值以加号开头时，如果数据源单元格未设置为文本格式，Google Sheets会将其识别为无效公式，getValues()获取到的是公式计算后的错误值（而非显示的文本），而setNote()无法接受错误值作为参数
2. 修改代码时误用了Excel的字符串拼接符&，Google Apps Script基于JavaScript，需用+做字符串拼接

解决方案 #

1. 确保数据源为文本格式 #

选中下拉列表的数据源区域，设置单元格格式为「文本」，再输入带加号开头的内容（如+1 to modifier），避免被识别为公式

2. 修改代码使用getDisplayValues() #

替换getValues()为getDisplayValues()，该方法获取的是单元格显示的文本内容，而非计算后的值，确保拿到正确的下拉选项文本

修改后的代码：

function addNotescToRange() {
  // 获取"GraveLayout"工作表
  const sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("GraveLayout");
  // 定义目标区域
  const range1 = sheet1.getRange("A1:B2");
  // 获取单元格显示的文本内容（而非计算值）
  const values = range1.getDisplayValues();

  // 遍历区域内的每个单元格
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      const cell = range1.getCell(i + 1, j + 1);
      const value = values[i][j];
      // 设置批注
      cell.setNote(value);
    }
  }
}

3. 若需给批注添加引号（可选） #

将设置批注的代码行改为：

cell.setNote('"' + value + '"');

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