如何在Google Apps Script的R1C1公式中用列名变量插入NETWORKDAYS公式

• 使用Google Script编写函数，基于F列的值向示例中的G列插入公式（预计用R1C1格式），公式中的列引用使用变量，公式为=NETWORKDAYS。为避免列位置变动导致引用错误，函数需要通过列标题名称查找对应列，而非硬编码列号。
• 插入到G列的公式需要根据F列的值动态调整引用的列。
• 本示例中，若F列值为Yes，G列对应单元格公式为=NETWORKDAYS(A2,D2)，逐行适配行号；若F列值为No，G列对应单元格公式为=NETWORKDAYS(A2,B2)，同样逐行适配行号。

• 不知道如何编写代码，让公式可以通过列标题名称而非R1C1记法中的硬编码列号来引用数据列
• 对IF语句、遍历范围数据的逻辑掌握不熟练，不清楚如何让函数逐行处理范围中的单元格

• 当前表格示例：
• 预期实现的最终效果：
• F列为Yes时的公式示例：
• F列为No时的公式示例：

function trainingDays(){

  //const/variables to find Training Days column
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
  const tf = ws.createTextFinder('Training Days');
  tf.matchEntireCell(true).matchCase(false);//finds text "Training Days" exactly
  const regionCellCol = tf.findNext().getColumn()//finds first instance of training days
  const regionCellRow = tf.findNext().getRow()

  //const/variables to find Race Date Announced
  const tfRaceDateAnnounced = ws.createTextFinder('Race Date Announced');
  tfRaceDateAnnounced.matchEntireCell(true).matchCase(false);//finds text "Race Date Announced" exactly
  const rdaCellCol = tfRaceDateAnnounced.findNext().getColumn()//finds first instance of race date announced
  const rdaCellRow = tfRaceDateAnnounced.findNext().getRow()

  //const/variables to find Training Date Ended
  const tfTrainingDateEnded = ws.createTextFinder('Training Date Ended');
  tfTrainingDateEnded.matchEntireCell(true).matchCase(false);//finds text Training Date Ended
  const tdeCellCol = tfTrainingDateEnded.findNext().getColumn()//finds first instance of training date ended 
  const tdeCellRow = tfTrainingDateEnded.findNext().getRow()

  //const/variables to find Training: Yes or No
  const tfTrain = ws.createTextFinder('Training: Yes or No');
  tfTrain.matchEntireCell(true).matchCase(false);//finds text Training: Yes or No
  const trainCellCol = tfTrain.findNext().getColumn()//finds first instance of Training: Yes or No
  const trainCellRow = tfTrain.findNext().getRow()
  
    //const/variables to find Race Date Commenced 
  const tfRDC = ws.createTextFinder('Race Date Commenced');
  tfRDC.matchEntireCell(true).matchCase(false);//finds text Race Date Commenced
  const rdcCellCol = tfRDC.findNext().getColumn()//finds first instance of race date commenced
  const rdcCellRow = tfRDC.findNext().getRow()



  //variable formulas

  var trainingDaysFormulaNo = [] //is =NETWORKDAYS(Race Date announced, race date commenced) ONLY IF Training is No
  var trainingDaysFormulaYes = [] //is =NETWORKDAYS(race date announced, training date ended) ONLY IF Training is Yes


ws.getRange(regionCellRow+1,regionCellCol,ws.getLastRow(),1).setFormulaR1C1()//not sure if this would work if I can figure out the formula to put in the .setFormulaR1C1 if I could pull the variable formulas and put into this, as an example .setFormulaR1C1(trainingDaysFormulaNo)

}//end of function trainingDays

原本认为以上代码可以将列名对应的范围插入到R1C1公式中，再通过setFormulaR1C1方法批量写入单元格范围。同时不清楚应该编写什么样的IF语句来实现上述动态公式逻辑。

• 查阅Stack Overflow上的相关内容，但大部分仅涉及A1记法转R1C1记法，或是Excel专属的解决方案
• 尝试使用TextFinder功能查找列标题来获取对应列范围，但尚未完成逻辑拼接

R1C1格式中RC[偏移量]代表相对于当前单元格的列偏移，你已经实现了通过列标题获取列号的逻辑，只要计算目标列和公式所在列的偏移量，就能实现无硬编码的列引用。不需要逐行遍历，直接构造带IF判断的R1C1公式，一次性批量写入所有行即可，运行效率更高。

修改后的完整代码如下：

function trainingDays(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ws = ss.getSheetByName('sheet1');

  // 封装获取列号的公共方法，减少重复代码
  function getColumnByTitle(title) {
    const tf = ws.createTextFinder(title)
      .matchEntireCell(true)
      .matchCase(false);
    const match = tf.findNext();
    if(!match) throw new Error(`未找到列标题：${title}`);
    return match.getColumn();
  }

  // 通过列标题获取所有需要用到的列序号
  const trainingDaysCol = getColumnByTitle('Training Days');
  const rdaCol = getColumnByTitle('Race Date Announced'); // 比赛公布日期，对应NETWORKDAYS第一个参数
  const tdeCol = getColumnByTitle('Training Date Ended'); // 训练结束日期，Training为Yes时的第二个参数
  const trainCol = getColumnByTitle('Training: Yes or No'); // Training判断列
  const rdcCol = getColumnByTitle('Race Date Commenced'); // 比赛开始日期，Training为No时的第二个参数

  // 计算各列相对于公式所在列（Training Days列）的R1C1偏移量
  const offsetRda = rdaCol - trainingDaysCol;
  const offsetTde = tdeCol - trainingDaysCol;
  const offsetTrain = trainCol - trainingDaysCol;
  const offsetRdc = rdcCol - trainingDaysCol;

  // 构造动态判断的R1C1公式，写入后会自动适配每一行的相对引用
  const formula = `=IF(RC[${offsetTrain}]="Yes",NETWORKDAYS(RC[${offsetRda}],RC[${offsetTde}]),NETWORKDAYS(RC[${offsetRda}],RC[${offsetRdc}]))`;

  // 批量写入所有需要填充公式的单元格
  const lastRow = ws.getLastRow();
  const writeRange = ws.getRange(2, trainingDaysCol, lastRow - 1, 1); // 假设表头在第1行，从第2行开始写入
  writeRange.setFormulaR1C1(formula);
}

如果你的表头不在第1行，修改getRange方法的第一个参数（起始行号）即可。

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