Google Sheet脚本需求：自定义下拉项自动同步及授权问题

之前脚本的问题 #

你之前的两个尝试都存在语法或逻辑漏洞：

• ATTEMPT1：未定义appendSheetPasteRow和appendSheetPasteColumn变量，没有明确粘贴目标位置，逻辑不完整。
• ATTEMPT2：getvalues()拼写错误（正确写法为getValues()），onEdit函数未闭合（缺少}），且仅固定处理B1单元格，无法适配多个下拉框。

正确脚本实现 #

以下脚本支持自定义映射多个下拉框单元格，自动将用户输入的自定义内容添加到lists对应列的首个空行：

function onEdit(e) {
  // 定义Sample表下拉框单元格与lists表对应列的映射关系
  // 格式：Sample单元格A1标识: lists表列号（A=1, B=2...）
  const cellColumnMap = {
    "B2": 1,   // Sample的B2对应lists的A列
    "B3": 2,   // Sample的B3对应lists的B列
    // 按需添加其他映射，比如"B4":3 对应lists的C列
  };

  const editedCell = e.range.getA1Notation();
  // 若编辑的不是目标单元格，直接返回
  if (!cellColumnMap[editedCell]) return;

  const inputVal = e.value;
  if (!inputVal) return; // 空值不做处理

  const ss = e.source;
  const listSht = ss.getSheetByName("lists");
  const targetCol = cellColumnMap[editedCell];

  // 获取对应列的所有现有选项，判断输入内容是否为自定义内容
  const existingOpts = listSht.getRange(1, targetCol, listSht.getLastRow(), 1)
    .getValues()
    .flat()
    .filter(val => val !== "");

  // 若输入内容不在现有选项中，添加到对应列的首个空行
  if (!existingOpts.includes(inputVal)) {
    // 查找对应列的首个空行
    const firstEmptyRow = listSht.getRange(1, targetCol, listSht.getMaxRows(), 1)
      .getValues()
      .findIndex(row => row[0] === "") + 1; // 索引从0开始，加1转为行号

    if (firstEmptyRow > 0) {
      listSht.getRange(firstEmptyRow, targetCol).setValue(inputVal);
    }
  }
}

使用步骤 #

1. 打开你的Google表格，点击「扩展程序」→「Apps脚本」。
2. 删除原有代码，粘贴上述脚本。
3. 根据实际需求，修改cellColumnMap中的映射关系，添加所有需要自动同步的下拉框单元格。
4. 保存脚本，命名为「AutoAddListItems」之类的名称即可。
5. 关闭脚本编辑器，回到表格后，在对应下拉框输入自定义内容，即可自动同步到lists表。

外部用户复制表格后，脚本需要授权才能运行，按以下步骤操作最简便：

1. 给用户的操作指引：

   • 打开复制后的表格，点击「扩展程序」→「Apps脚本」。
   • 在脚本编辑器中，点击顶部的运行按钮（▶️），选择onEdit函数（或直接运行一次脚本）。
   • 首次运行会弹出授权提示，点击「授权访问」，选择自己的Google账号。
   • 当出现「此应用未经验证」提示时，点击左下角的「高级」，再点击「转到[你的脚本名称]（不安全）」，最后完成授权。

2. 优化方案：添加初始化提示
   可以在脚本中添加一个初始化函数，帮助用户快速完成授权：

   function initAuth() {
     SpreadsheetApp.getActiveSpreadsheet().toast("授权完成！现在下拉框自定义内容会自动同步啦", "提示", 5);
   }
   

   告诉用户复制表格后，先运行initAuth函数完成授权，后续onEdit触发器会自动生效。

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