exceloffice-jsoffice-scriptsms-office-script

How to set a hardcoded formula to dynamic in Excel office script?


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([["=???"],["=???"]]);

Solution

  • 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]`); 
    }