Set and get range values, text, or formulas using the Excel JavaScript API
This article provides code samples that set and get range values, text, or formulas with the Excel JavaScript API. For the complete list of properties and methods that the Range object supports, see Excel.Range class.
Note
The Excel JavaScript API doesn't have a "Cell" object or class. Instead, the Excel JavaScript API defines all Excel cells as Range objects. An individual cell in the Excel UI translates to a Range object with one cell in the Excel JavaScript API. A single Range object can also contain multiple contiguous cells. See Work with cells using the Excel JavaScript API to learn more.
Set values or formulas
The following code samples set values and formulas for a single cell or a range of cells.
Set value for a single cell
The following code sample sets the value of cell C3 to "5" and then sets the width of the columns to best fit the data.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let range = sheet.getRange("C3");
range.values = [[ 5 ]];
range.format.autofitColumns();
await context.sync();
});
Data before cell value is updated
Data after cell value is updated
Set values for a range of cells
The following code sample sets values for the cells in the range B5:D5 and then sets the width of the columns to best fit the data.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let data = [
["Potato Chips", 10, 1.80],
];
let range = sheet.getRange("B5:D5");
range.values = data;
range.format.autofitColumns();
await context.sync();
});
Data before cell values are updated
Data after cell values are updated
Set formula for a single cell
The following code sample sets a formula for cell E3 and then sets the width of the columns to best fit the data.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let range = sheet.getRange("E3");
range.formulas = [[ "=C3 * D3" ]];
range.format.autofitColumns();
await context.sync();
});
Data before cell formula is set
Data after cell formula is set
Set formulas for a range of cells
The following code sample sets formulas for cells in the range E2:E6 and then sets the width of the columns to best fit the data.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let data = [
["=C3 * D3"],
["=C4 * D4"],
["=C5 * D5"],
["=SUM(E3:E5)"]
];
let range = sheet.getRange("E3:E6");
range.formulas = data;
range.format.autofitColumns();
await context.sync();
});
Data before cell formulas are set
Data after cell formulas are set
Get values, text, or formulas
These code samples get values, text, and formulas from a range of cells.
Get values from a range of cells
The following code sample gets the range B2:E6, loads its values property, and writes the values to the console. The values property of a range specifies the raw values that the cells contain. Even if some cells in a range contain formulas, the values property of the range specifies the raw values for those cells, not any of the formulas.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let range = sheet.getRange("B2:E6");
range.load("values");
await context.sync();
console.log(JSON.stringify(range.values, null, 4));
});
Data in range (values in column E are a result of formulas)
range.values (as logged to the console by the code sample above)
[
[
"Product",
"Qty",
"Unit Price",
"Total Price"
],
[
"Almonds",
2,
7.5,
15
],
[
"Coffee",
1,
34.5,
34.5
],
[
"Chocolate",
5,
9.56,
47.8
],
[
"",
"",
"",
97.3
]
]
Get text from a range of cells
The following code sample gets the range B2:E6, loads its text property, and writes it to the console. The text property of a range specifies the display values for cells in the range. Even if some cells in a range contain formulas, the text property of the range specifies the display values for those cells, not any of the formulas.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let range = sheet.getRange("B2:E6");
range.load("text");
await context.sync();
console.log(JSON.stringify(range.text, null, 4));
});
Data in range (values in column E are a result of formulas)
range.text (as logged to the console by the code sample above)
[
[
"Product",
"Qty",
"Unit Price",
"Total Price"
],
[
"Almonds",
"2",
"7.5",
"15"
],
[
"Coffee",
"1",
"34.5",
"34.5"
],
[
"Chocolate",
"5",
"9.56",
"47.8"
],
[
"",
"",
"",
"97.3"
]
]
Get formulas from a range of cells
The following code sample gets the range B2:E6, loads its formulas property, and writes it to the console. The formulas property of a range specifies the formulas for cells in the range that contain formulas and the raw values for cells in the range that do not contain formulas.
await Excel.run(async (context) => {
let sheet = context.workbook.worksheets.getItem("Sample");
let range = sheet.getRange("B2:E6");
range.load("formulas");
await context.sync();
console.log(JSON.stringify(range.formulas, null, 4));
});
Data in range (values in column E are a result of formulas)
range.formulas (as logged to the console by the code sample above)
[
[
"Product",
"Qty",
"Unit Price",
"Total Price"
],
[
"Almonds",
2,
7.5,
"=C3 * D3"
],
[
"Coffee",
1,
34.5,
"=C4 * D4"
],
[
"Chocolate",
5,
9.56,
"=C5 * D5"
],
[
"",
"",
"",
"=SUM(E3:E5)"
]
]
See also
Office Add-ins