Using column header variables in R1C1 formula

Question

I am attempting to:

1. Have google script perform a function that inserts formulas (I think using R1C1) into Column G in this example based on column F, and using variables for the column reference in the formula. The Formula is =NETWORKDAYS. I want to ensure my function searches for the column header names instead of the number in case the columns are moved.

2. The formula inserted into column G would change changed which column its pulling from, depending on column F

3. For our example here, if Yes in Column F, Column G would have the formula =NETWORKDAYS(A2,D2) with this being input to each cell respectively in column G.

If no, the formula would be =NETWORKDAYS(A2,B2), with the formula inserted into each cell respectively in column G.

Current Issue:

1. I am unsure how to code this so that the formula uses the column header name instead of a hard-coded column number reference like you would do in R1C1 notation
2. I am not great with IF statements, and "passing through" items in a range (i.e. making the function move through the range), this is still a grey area for me

Current Sheet:

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

Yes Formula Example

No Formula Example

Current Code:

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

What I thought my code would do

I thought this code would allow me to insert the column name range into a R1C1 formula and use the setFormulaR1C1 in the cell range. Also I am not sure what kind of IF statement to do for this function to work properly.

What I've Tried:

1. Reviewing some items on stackoverflow but it seems to only relate to changing A1 notation to R1C1 or is excel specific
2. I was hoping to be smart/clever using the text find features to call to the columns and get ranges that way

References:

Using Variables in Columns

Answer 1

See my comment on your original post.

However, sight-unseen and hence untested, you can try this formula (no script involved) in G1:

=ArrayFormula({"Training Days"; IF(A2:A="",,NETWORKDAYS(A2:A,IF(F2:F="Yes",D2:D,B2:B)))})

This single array formula should produce the header and all column results for active rows.

Answer 2

Since you are formulating a script that assumes column headers can be on a different location, I'd recommend using setFormulas instead of the R1C1 version since that will have to make use of offsets relative to your header location.

See overhauled script below:

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()]
}

Output 1:

Output 2 (different column locations):