如何简化Google Apps Script多IF条件并解决表格更新异常？

问题背景 #

我不是开发人员，目前基于Stack Overflow用户的代码修改Google Sheets脚本：需求是当表格编辑选择下拉列表中的对应名称时触发邮件，用户勾选任务完成时触发另一封邮件。但添加大量IF条件后，Google Sheets后端停止更新（原因未知），且需支持50+用户的邮件触发，想知道有没有简化IF条件的方法。

原代码 #

function editCell(props){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Data")
  const idCellMatched = ws.getRange("A2:A").createTextFinder(props.id).matchEntireCell(true).matchCase(true).findNext()

  const columnCellMatched = ws.getRange("1:1").createTextFinder(props.field).matchEntireCell(true).matchCase(true).findNext()

  if(idCellMatched === null) throw new Error("No Matching Record")
  if(columnCellMatched === null) throw new Error("Invalid Field")

  const recordRowNumber = idCellMatched.getRow()
  const recordColumnNumber = columnCellMatched.getColumn()
 
//Added new condition to send email
 const task = ws.getRange(recordRowNumber, recordColumnNumber-1).getValue()
 const comment= ws.getRange(recordRowNumber, recordColumnNumber-2).getValue()
 const body = HtmlService.createHtmlOutput("A <b> new task </b> have been added to the Task Manager <br> <a href='https://sites.google.com/'>Visit Dashboard To Update</a>")
 const bodyCompleted = HtmlService.createHtmlOutput("Task has been completed. <br> Comments - "+ comment+ "<br> <a href='https://sites.google.com/'>Visit Dashboard To Update</a>")
 
 
//----------------------------------Jonh Start
//Assign a name
if (props.val == "John") {
  GmailApp.sendEmail("john@abc.com", task, null, { name: "ABC",htmlBody: body.getContent() });
}
//click Task is completed
if (props.val == 1 ) {
  GmailApp.sendEmail("john@abc.com", ws.getRange(recordRowNumber, recordColumnNumber-5).getValue() + " Task is Completed", null, { name: "ABC",htmlBody: bodyCompleted.getContent() });
}

//----------------------------Peter Sart

//Assign a name
if (props.val == "Peter") {
  GmailApp.sendEmail("john@abc.com", task, null, { name: "ABC",htmlBody: body.getContent() });
}
//click Task is completed
if (props.val == 1 ) {
  GmailApp.sendEmail("Peter@abc.com", ws.getRange(recordRowNumber, recordColumnNumber-5).getValue() + " Task is Completed", null, { name: "ABC",htmlBody: bodyCompleted.getContent() });
}

//---------------------------Mark
//Assign a name
if (props.val == "Mark") {
  GmailApp.sendEmail("mark@abc.com", task, null, { name: "ABC",htmlBody: body.getContent() });
}
//click Task is completed
if (props.val == 1 ) {
  GmailApp.sendEmail("mark@abc.com", ws.getRange(recordRowNumber, recordColumnNumber-5).getValue() + " Task is Completed", null, { name: "ABC",htmlBody: bodyCompleted.getContent() });
}
}

简化方案 #

核心是用用户映射对象替代重复的IF判断，同时减少Google Sheets API调用次数（这很可能是后端停止更新的原因）。

1. 核心优化点

• 用键值对存储所有用户的姓名和对应邮箱，新增用户只需在对象里添加条目，不用写新的IF
• 一次性获取整行数据，避免多次调用getRange导致的性能问题

2. 重构后代码

function editCell(props){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Data")
  const idCellMatched = ws.getRange("A2:A").createTextFinder(props.id).matchEntireCell(true).matchCase(true).findNext()
  const columnCellMatched = ws.getRange("1:1").createTextFinder(props.field).matchEntireCell(true).matchCase(true).findNext()

  if(idCellMatched === null) throw new Error("No Matching Record")
  if(columnCellMatched === null) throw new Error("Invalid Field")

  const recordRowNumber = idCellMatched.getRow()
  const recordColumnNumber = columnCellMatched.getColumn()

  // 一次性获取当前行所有数据，减少API调用
  const rowData = ws.getRange(recordRowNumber, 1, 1, ws.getLastColumn()).getValues()[0]
  // 根据列索引提取所需值（注意数组索引从0开始，列号从1开始，要减1）
  const task = rowData[recordColumnNumber - 2] // 对应原代码的columnNumber-1
  const comment = rowData[recordColumnNumber - 3] // 对应原代码的columnNumber-2
  const taskTitle = rowData[recordColumnNumber - 6] // 对应原代码的columnNumber-5

  // 用户映射：姓名 -> 邮箱，新增用户直接在这里添加
  const userMap = {
    "John": "john@abc.com",
    "Peter": "peter@abc.com",
    "Mark": "mark@abc.com",
    // ... 其他用户
  }

  const body = HtmlService.createHtmlOutput("A <b> new task </b> has been added to the Task Manager <br> Visit Dashboard To Update")
  const bodyCompleted = HtmlService.createHtmlOutput(`Task has been completed. <br> Comments - ${comment}<br> Visit Dashboard To Update`)

  // 处理任务分配（下拉选择用户名）
  if (userMap[props.val]) {
    GmailApp.sendEmail(userMap[props.val], task, null, { name: "ABC", htmlBody: body.getContent() })
  }

  // 处理任务完成（勾选值为1）
  if (props.val == 1) {
    // 假设任务对应的用户在某个固定列，比如这里用columnNumber-5对应的列（原代码逻辑），需要根据实际调整
    const userName = rowData[recordColumnNumber - 6]
    if (userMap[userName]) {
      GmailApp.sendEmail(userMap[userName], `${taskTitle} Task is Completed`, null, { name: "ABC", htmlBody: bodyCompleted.getContent() })
    } else {
      console.log(`未找到用户 ${userName} 的邮箱配置`)
    }
  }
}

3. 额外说明

• 关于后端停止更新：原代码多次调用getRange，Google Apps Script对API调用次数有限制，频繁调用会导致脚本卡顿甚至停止。重构后一次性获取整行数据，大幅减少API调用，能解决这个问题。
• 新增用户：只需在userMap对象里添加"用户名": "邮箱地址"的条目即可，无需修改其他代码。
• 列索引调整：代码里的数组索引需要根据你的表格实际列位置调整，确保和原逻辑一致。

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