CODEWITHSUNDEEP
office-js
cskr11
cskr11

Reputation: 57

set formula to an entire range office js (excel)

I am trying to apply formula to an entire range in excel using office js. But the formula is getting applied only to first cell but not the range. Here is the code which I am using. The same works fine for VSTO and the formula gets applied 

Excel.run(async (context) => {
 const sheet = context.workbook.worksheets.getItem("Sample");
 const range = sheet.getRange("C5:C100));                                 
 range.formulas = [[ =A5 + B5 ]];
 await context.sync();
}

I would like to have the formula applied to the entire range or an to an entire column in a table rather than the first cell alone. Pls let me know if this is possible.

Upvotes: 0

Views: 1204

Answers (1)

Rick Kirkham
Rick Kirkham

Reputation: 9684

Yes. This is possible. But a formula is considered a characteristic of a cell, not a range. So you need to set the formula to each cell. You need to create an array-of-arrays where there is an inner array for every row, so the array that you assign to range.formulas is effectively the same as: [[ =A5 + B5 ], [ =A6 + B6 ], [ =A7 + B7 ] … ]. However, you do not need to hand code this. You can use a loop or other iterating structure to produce the array-of-arrays. For an example that uses the JavaScript map function to do this in an Office Add-in sample, see this file, especially line 318.

Upvotes: 1

Related Questions