如何使用Google Apps Script实现Round Robin销售线索轮循分配

表格结构约定（和你的场景对齐） #

• AE姓名横向排列在第2行，从B列开始依次放置
• X标记放在第3行对应AE姓名的下方，标记当前轮到分配的AE
• 销售线索从第5行开始排列，A列为公司名称，B列为分配人填写列，空白即为待分配线索

可运行完整代码 #

const STRINGX = 'X';
// 配置项，可根据你的实际表格调整
const CONFIG = {
  AE_NAME_ROW: 2, // AE姓名所在行号
  X_MARK_ROW: 3, // X标记所在行号
  LEAD_START_ROW: 5, // 线索开始行号
  ASSIGN_COLUMN: 2, // 分配人填写列（B列对应索引2）
  COMPANY_COLUMN: 1 // 公司名称列（A列对应索引1）
}

// 主函数：运行即可自动分配所有待分配线索
function runRoundRobinAssign() {
  let openLeadRow;
  // 循环直到没有待分配线索
  while((openLeadRow = findNextOpenLead()) !== null) {
    const currentAE = getNextPerson();
    assignPersonToLead(currentAE, openLeadRow);
    moveXDown();
  }
  SpreadsheetApp.getActiveSpreadsheet().toast('所有线索分配完成');
}

// 获取当前轮到分配的AE姓名
function getNextPerson() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const xRowValues = sheet.getRange(CONFIG.X_MARK_ROW, 1, 1, sheet.getLastColumn()).getValues()[0];
  // 找到X所在的列
  const xColIndex = xRowValues.findIndex(val => val === STRINGX);
  if(xColIndex === -1) throw new Error('未找到X标记，请先在AE列表下方放置X');
  // 获取对应列上方的AE姓名
  return sheet.getRange(CONFIG.AE_NAME_ROW, xColIndex + 1).getValue();
}

// 查找下一个待分配的线索行号
function findNextOpenLead() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getLastRow();
  // 从线索起始行开始遍历分配列
  for(let row = CONFIG.LEAD_START_ROW; row <= lastRow; row++) {
    const cellValue = sheet.getRange(row, CONFIG.ASSIGN_COLUMN).getValue();
    const companyValue = sheet.getRange(row, CONFIG.COMPANY_COLUMN).getValue();
    // 分配列为空且有公司名称的即为待分配线索
    if(cellValue === '' && companyValue !== '') {
      return row;
    }
  }
  // 没有待分配线索返回null
  return null;
}

// 将AE姓名填入对应线索的分配单元格
function assignPersonToLead(personName, leadRow) {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(leadRow, CONFIG.ASSIGN_COLUMN).setValue(personName);
}

// 将X标记移动到下一位AE，到末尾自动回到第一个AE
function moveXDown() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const xRowValues = sheet.getRange(CONFIG.X_MARK_ROW, 1, 1, sheet.getLastColumn()).getValues()[0];
  const currentXCol = xRowValues.findIndex(val => val === STRINGX);
  if(currentXCol === -1) throw new Error('未找到X标记，请先在AE列表下方放置X');
  
  // 清空当前X
  sheet.getRange(CONFIG.X_MARK_ROW, currentXCol + 1).setValue('');
  
  // 计算下一个AE的列索引
  const aeNames = sheet.getRange(CONFIG.AE_NAME_ROW, 2, 1, sheet.getLastColumn() - 1).getValues()[0].filter(val => val !== '');
  const nextXCol = (currentXCol - 1 + 1) % aeNames.length + 2; // AE从B列（索引2）开始，做偏移处理
  
  // 写入新的X
  sheet.getRange(CONFIG.X_MARK_ROW, nextXCol).setValue(STRINGX);
}

使用说明 #

• 打开你的Google表格，点击顶部菜单「扩展程序」-「Apps Script」进入脚本编辑器
• 删除编辑器默认的空白代码，将上述代码全部粘贴进去
• 对照你的实际表格结构调整顶部CONFIG配置项的参数
• 点击保存，第一次运行选择runRoundRobinAssign函数，按提示授权权限即可运行

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