Google Apps Script配置Google Sheets列范围及超链接生成求助

完整实现脚本 #

以下脚本可直接配置目标范围生成可跳转的WhatsApp超链接，并通过单元格保护+条件格式实现启用/禁用逻辑：

function createWhatsAppHyperlink() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("FollowUp"); // 匹配你的工作表名称
  const targetRange = sheet.getRange("C3:Q"); // 目标填充区域
  const values = targetRange.getValues();
  const phoneColumn = 2; // 假设B列存储手机号，可根据实际修改列号（A=1、B=2...）

  // 遍历目标区域生成超链接
  for (let row = 0; row < values.length; row++) {
    for (let col = 0; col < values[row].length; col++) {
      const currentRow = targetRange.getRow() + row;
      // 提取手机号并清除非数字字符
      const phoneNumber = sheet.getRange(currentRow, phoneColumn).getValue().toString().replace(/\D/g, "");
      
      if (phoneNumber.length >= 10) {
        // 生成可直接跳转的WhatsApp链接，用HYPERLINK函数实现点击跳转
        const waLink = `https://wa.me/${phoneNumber}`;
        targetRange.getCell(row + 1, col + 1).setFormula(`=HYPERLINK("${waLink}", "发WhatsApp")`);
        // 移除保护，启用单元格编辑
        removeCellProtection(sheet, currentRow, targetRange.getColumn() + col);
      } else {
        // 清空无效单元格并添加保护，禁用编辑
        targetRange.getCell(row + 1, col + 1).clearContent();
        addCellProtection(sheet, currentRow, targetRange.getColumn() + col);
      }
    }
  }

  // 添加条件格式：标记有效超链接单元格
  const highlightRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=ISHYPERLINK(C3)')
    .setBackground("#b7e1cd") // 有效单元格高亮为浅绿色，可自定义颜色
    .setRanges([targetRange])
    .build();
  
  const existingRules = sheet.getConditionalFormatRules();
  existingRules.push(highlightRule);
  sheet.setConditionalFormatRules(existingRules);
}

// 辅助函数：给单元格添加编辑保护
function addCellProtection(sheet, row, col) {
  const protection = sheet.getRange(row, col).protect();
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

// 辅助函数：移除单元格编辑保护
function removeCellProtection(sheet, row, col) {
  const targetCell = sheet.getRange(row, col);
  const protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  
  for (let p of protections) {
    if (p.getRange().getA1Notation() === targetCell.getA1Notation()) {
      p.remove();
    }
  }
}

使用步骤 #

1. 打开你的FollowUp表格，点击顶部菜单扩展程序 > Apps 脚本
2. 替换现有脚本为上述代码，调整phoneColumn变量为实际存储手机号的列号
3. 保存脚本，点击运行按钮执行createWhatsAppHyperlink，首次运行需完成授权验证
4. 脚本执行后：
   • C3:Q区域内，对应有效手机号的单元格会生成可直接跳转的WhatsApp超链接
   • 无效手机号的单元格会被清空并设置编辑保护（禁用修改）
   • 有效超链接单元格会自动添加浅绿色高亮

自定义说明 #

• 若工作表名称不是"FollowUp"，修改sheet.getSheetByName("FollowUp")中的名称
• 可调整setBackground("#b7e1cd")的颜色代码，自定义有效单元格的高亮样式
• 超链接显示文本可修改"发WhatsApp"为你需要的文字

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