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

Question

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

Michal · Accepted Answer · 2025-04-17 00:25:21Z

1

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

answered Apr 17 at 0:25

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related