在 R1C1 公式中使用列标题变量

我正在尝试：

1. 让谷歌脚本执行一个函数，在本示例中基于 F 列将公式（我认为使用 R1C1）插入到 G 列中，并在公式中使用变量作为列引用。公式为=NETWORKDAYS。我想确保我的函数搜索列标题名称而不是数字，以防列被移动。

2. 插入 G 列的公式将更改其从哪一列中提取，具体取决于 F 列

3. 对于我们这里的示例，如果 F 列中为“是”，G 列将具有公式 =NETWORKDAYS(A2,D2)，并将其分别输入到 G 列中的每个单元格。

如果否，公式将为 =NETWORKDAYS(A2,B2)，并将公式分别插入到 G 列的每个单元格中。

当前的问题：

1. 我不确定如何对此进行编码，以便公式使用列标题名称，而不是像在 R1C1 表示法中那样使用硬编码的列号引用
2. 我不太擅长 IF 语句，以及“传递”范围内的项目（即使函数在范围内移动），这对我来说仍然是一个灰色区域

Current Sheet:

What I would like to have happen/End Result of script:

Yes Formula Example

No Formula Example

当前代码：

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 语句才能使该函数正常工作。

我尝试过的：

1. 查看 stackoverflow 上的一些项目，但它似乎只与将 A1 表示法更改为 R1C1 相关，或者是 excel 特定的
2. 我希望能够聪明/聪明地使用文本查找功能来调用列并以这种方式获取范围

参考：

在列中使用变量

由于您正在制定一个假设列标题可以位于不同位置的脚本，因此我建议使用setFormulas而不是 R1C1 版本，因为它必须使用相对于标头位置的偏移量。

请参阅下面的大修脚本：

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

  // search headers (converted to separate function to return row and column)
  const [tfRow, tfCol] = getLocationOfText(ws, 'Training Days');
  const [tfTYNRow, tfTYNCol] = getLocationOfText(ws, 'Training: Yes or No');
  // get columns only for other headers
  const [, tfRDACol] = getLocationOfText(ws, 'Race Date Announced');
  const [, tfRDCCol] = getLocationOfText(ws, 'Race Date Commenced');
  const [, tfTDECol] = getLocationOfText(ws, 'Training Date Ended');
  // convert number to letter for formula substition later 
  const tfRDA = String.fromCharCode(96 + tfRDACol).toUpperCase();
  const tfRDC = String.fromCharCode(96 + tfRDCCol).toUpperCase();
  const tfTDE = String.fromCharCode(96 + tfTDECol).toUpperCase();

  // offset lastRow with row index since data is not starting at 1
  const lastRow = ws.getLastRow();
  const tfTYNVal = ws.getRange(tfTYNRow + 1, tfTYNCol, lastRow - tfTYNRow, 1)
                     .getValues();

  // placeholder for output
  var output = [];

  // generate an array with the formula with each value in Yes/No column
  tfTYNVal.forEach((cell, index) => {
    // get row by offsetting index with row of header (+1 due to 0-indexing)
    var row = (tfTYNRow + index + 1);
    if(cell == 'Yes')
      output.push([`=NETWORKDAYS(${tfRDA}${row}, ${tfTDE}${row},)`]);
    else if(cell == 'No')
      output.push([`=NETWORKDAYS(${tfRDA}${row}, ${tfRDC}${row},)`]);
    // write blank if not yes or no (to avoid setFormulas from breaking)
    else
      output.push(['']);
  });

  // I personally avoided using R1C1 version since that uses offsets on range
  // It will be an issue when header location is changed since it will conform 
  // to the offset given based on your original header location
  ws.getRange(tfRow + 1, tfCol, lastRow - tfRow, 1).setFormulas(output);
}

function getLocationOfText(sheet, text) {
  const tf = sheet.createTextFinder(text);
  tf.matchEntireCell(true).matchCase(false);
  const tfNext = tf.findNext();

  return [tfNext.getRow(), tfNext.getColumn()]
}

输出1：

输出 2（不同的列位置）：