I're recorded an action in office script and this is one part that was recorded.
selectedSheet.getRange("AN4:AN5").setFormulas([["=AN2-AM2"],["=AN3-AM3"]]);
I wanted to make the script dynamic, then if instead the column letter I have the column index number, how would be written the code? in this example column index are 39 (AM) and 40(AN).
something like
const col1_index = 39;
const col2_index = 40;
selectedSheet.getRange(???).setFormulas([["=???"],["=???"]]);
You can set a variable for the formula destination and use a relative formula for the calculation, for example:
function main(workbook: ExcelScript.Workbook) {
const selectedSheet = workbook.getWorksheet("Sheet1");
const col1_index = 39; //AN
// Set formulas dynamically using row and column indices
selectedSheet.getCell(3, col1_index).setFormulaR1C1(`=R[0]C[-1]-R[0]C[-2]`);
selectedSheet.getCell(4, col1_index).setFormulaR1C1(`=R[0]C[-1]-R[0]C[-2]`);
}