TypeScript Code
link - learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges
link - learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges-advanced
link - learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-multiple-ranges
link - learn.microsoft.com/en-us/office/dev/add-ins/excel/performance
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/samples-overview
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/write-large-dataset
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/external-fetch-calls
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/filter-table-get-visible-range
link - learn.microsoft.com/en-us/office/dev/scripts/resources/samples/get-table-data
var _range = context.workbook.names.getItem("myNamedRange").getRange();
var _rangeValues = _range.getRow(1).getBoundingRect(_range.getLastCell());
var _myCol = _range.getColumn(0);
getCell(0,0) = A1
getRange
getWorksheet
getAddress
getValue - only 1 value, doesn't return an array from a larger range
getValues - returns an array
ExcelScript.Range ?
getRange.getSurroundingRegion
let myArray = wshName.getValues()
myArray.shift() // removes the header / first row
Copy the values from Range A1:A2 on the active worksheet to B1:B2.
Assign the previously loaded values to the new range proxy object.
The values will be updated once the following .then() function is invoked.
Excel.run(function (context) {
var _range = context.workbook.worksheets.getActiveWorksheet().getRange("A1:A2");
_range.load ("address, values, range/format");
_return context.sync().then(function() {
context.workbook.worksheets.getActiveWorksheet().getRange("B1:B2").values= range.values;
});
}).then(function() {
console.log("done");
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
Excel.run(function (context) {
var _sheetName = "Sheet1";
var _rangeAddress = "A1:B2";
var _myRange = context.workbook.worksheets.getItem(_sheetName).getRange(_rangeAddress);
_myRange.load(["address", "format/*", "format/fill", "entireRow" ]);
return context.sync().then(function() {
console.log (myRange.address);
console.log (myRange.format.wrapText);
console.log (myRange.format.fill.color);
//console.log (myRange.format.font.color); //not loaded
});
}).then(function() {
console.log("done");
}).catch(function(error) {
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
});
Writing
Set myMatrix in the document.
var _myMatrix = [["1","2","3"],["4","5","6"],["7","8","9"]];
Office.context.document.setSelectedDataAsync(_myMatrix,
function (asyncResult) {
if (asyncResult.status == "failed") {
showMessage("Action failed with error: " + asyncResult.error.message);
}
});
Be aware that a matrix can only be written into the spreadsheet if no other data in the surrounding cells will be overwritten.
Excel.run(function (context) {
context.workbook.worksheets.getItem("Sheet1").getRange("A1:C3").values = 7;
return context.sync();
}).catch(function (error) {
console.log(error);
});
Excel.run(function (context) {
var _range = context.workbook.worksheets.getItem("Sheet1").getRange("A1:C3");
_range.values = [[1, 2, 3], [4, 5, 6], [7, 8, 9]];
return context.sync();
}).catch(function (error) {
console.log(error);
});
var _myMatrix = [[1,2,3],[4,5,6]];
Excel.run(function (context) {
var _sheet = context.workbook.worksheets.getItem('Sheet1');
var _celltop = _sheet.getCell(0,0);
var _cellbottom = _sheet.getCell(_myMatrix.length - 1, _myMatrix[0].length - 1);
var _range = _celltop.getBoundingRect(_cellbottom).insert('down');
_range.values = _myMatrix;
_range.format.font.bold = true;
//_range.delete('up');
return context.sync();
}).catch( function(error) {
console.log(error);
})
Reading
Excel.run(function (context) {
var _range = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C3").load("values");
return context.sync().then(function () {
for (var i = 0; i < _range.values.length; i++) {
for (var j = 0; j < _range.values[i].length; j++) {
console.log(_range.values[i][j]);
}
}
console.log("done");
});
}).catch(function (error) {
console.log(error);
});
Read a matrix from the current selection in the document and display its contents
Office.context.document.getSelectedDataAsync("matrix",
function (asyncResult) {
if (asyncResult.status == "failed") {
showMessage("Action failed with error: " + asyncResult.error.message);
}
else {
showMessage("Selected data: " + asyncResult.value);
}
});
The following example uses the getSelectedDataAsync method to read the currently selected range of cells. To specify that the selection is read as a range of cells, specify the coerctionType parameter as CoercionType.Matrix. The callback function returns an object containing the data currently selected by the user, which can use the asyncResult.value property to access the data.
Clearing
Excel.run(function (context) {
context.workbook.worksheets.getActiveWorksheet().getRange("A1:C1").clear(Excel.ClearApplyTo.contents);
return context.sync()
}).catch(function (error) {
console.log(error);
});
Sorting Cells
var range =
var sortFields =
[
{ key: 4,
ascending: false,
dataOption: 'Normal',
sortOn: FontColor, Icon
color: 'Red'
}
];
range.sort.apply(
sortFields, true, true, "Rows");
context.load(range)
return context.sync();
Inserting Cells
Excel.run(function (context) {
context.workbook.worksheets.getActiveWorksheet().getRange("A1:C3").insert("right");
return context.sync();
}).catch(function (error) {
console.log(error);
});
Get Current Selection
Excel.run(function (context) {
var _rangeSelected = context.workbook.getSelectedRange().load();
return context.sync().then(function() {
console.log(_rangeSelected.address);
});
}).catch(function (error) {
console.log(error);
});
Changing to Upper Case
Excel.run(function(context) {
var _range = context.workbook.getSelectedRange().load("values");
return context.sync()
.then(function() {
var _vals = _range.values;
for (var i = 0; i < _vals.length; i++){
for (var j = 0; j < _vals[i].length; j++){
_vals[i][j] = _vals[i][j].toUpperCase();
}
}
_range.values = _vals;
})
.then(context.sync);
}).catch(function (error) {
console.log(error);
});
Using References
Excel.run(function (context) {
var _rangeOriginal = context.workbook.worksheets.getActiveWorksheet().getRange("A1:C4");
var _rangeNew = _rangeOriginal.insert(Excel.InsertShiftDirection.down);
_rangeOriginal.format.fill.color = "Red";
_rangeNew.format.fill.color = "Yellow";
return context.sync();
}).catch(function (error) {
console.log(error);
});
Get the value in cell A1.
let range = selectedSheet.getRange("A1");
console.log(range.getValue());
Get the current active cell
let cell = workbook.getActiveCell();
console.log(`The current cell's value is ${cell.getValue()}`);
This script applies some formatting to the cell to its right and the cell above.
function main(workbook: ExcelScript.Workbook) {
let activeCell = workbook.getActiveCell();
console.log(`The active cell's address is: ${activeCell.getAddress()}`);
let rightCell = activeCell.getOffsetRange(0,1);
rightCell.setValue("Right cell");
console.log(`The right cell's address is: ${rightCell.getAddress()}`);
rightCell.getFormat().getFont().setColor("Magenta");
rightCell.getFormat().getFill().setColor("Cyan");
let aboveCell = activeCell.getOffsetRange(-1, 0);
aboveCell.setValue("Above cell");
console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
aboveCell.getFormat().getFont().setColor("White");
aboveCell.getFormat().getFill().setColor("Black");
}
This script copies the formatting from the active cell to the 9 neighbouring cells.
Note that this script only works when the active cell isn't on an edge of the worksheet.
function main(workbook: ExcelScript.Workbook) {
let activeCell = workbook.getActiveCell();
let cornerCell = activeCell.getOffsetRange(-1,-1);
let surroundingRange = cornerCell.getResizedRange(2, 2)
surroundingRange.copyFrom(activeCell, ExcelScript.RangeCopyType.formats);
}
This script clears the formatting from the selected range, and loops through every cell changing the fill color to a random color.
function main(workbook: ExcelScript.Workbook) {
let range = workbook.getSelectedRange();
let rows = range.getRowCount();
let cols = range.getColumnCount();
range.clear(ExcelScript.ClearApplyTo.formats);
for (let row = 0; row < rows; row++) {
for (let col = 0; col < cols; col++) {
let colorString = `#${Math.random().toString(16).substr(-6)}`;
range.getCell(row, col).getFormat().getFill().setColor(colorString);
}
}
}
This script gets the current used range and shades any blank cells with a yellow background.
function main(workbook: ExcelScript.Workbook) {
let range = workbook.getActiveWorksheet().getUsedRange();
let blankCells = range.getSpecialCells(ExcelScript.SpecialCellType.blanks);
blankCells.getFormat().getFill().setColor("yellow");
}
This script removes hyperlinks
function main(workbook: ExcelScript.Workbook, sheetName: string = 'Sheet1') {
// Get the active worksheet.
let sheet = workbook.getWorksheet(sheetName);
// Get the used range to operate on.
// For large ranges (over 10000 entries), consider splitting the operation into batches for performance.
const targetRange = sheet.getUsedRange(true);
console.log(`Target Range to clear hyperlinks from: ${targetRange.getAddress()}`);
const rowCount = targetRange.getRowCount();
const colCount = targetRange.getColumnCount();
console.log(`Searching for hyperlinks in ${targetRange.getAddress()} which contains ${(rowCount * colCount)} cells`);
// Go through each individual cell looking for a hyperlink.
// This allows us to limit the formatting changes to only the cells with hyperlink formatting.
let clearedCount = 0;
for (let i = 0; i < rowCount; i++) {
for (let j = 0; j < colCount; j++) {
const cell = targetRange.getCell(i, j);
const hyperlink = cell.getHyperlink();
if (hyperlink) {
cell.clear(ExcelScript.ClearApplyTo.hyperlinks);
cell.getFormat().getFont().setUnderline(ExcelScript.RangeUnderlineStyle.none);
cell.getFormat().getFont().setColor('Black');
clearedCount++;
}
}
}
console.log(`Done. Cleared hyperlinks from ${clearedCount} cells`);
}
RefEdit - Prompt
function event_runTestingCode() {
var _bindingOptions = {
id: "MyBinding",
promptText: "Please select your cells."
};
//creates a binding by prompting the user to make a selection
Office.context.document.bindings.addFromPromptAsync(
Office.BindingType.Matrix,
_bindingOptions,
onBindingCallback);
}
function onBindingCallback(asyncResult) {
if (asyncResult.status === Office.AsyncResultStatus.Failed) {
console.log("Error get_rangecoords. " + asyncResult.error.message, 3);
}
else {
console.log("Added new binding with type: " + asyncResult.value.type + " and id: " + asyncResult.value.id, 1);
Excel.run(function (context) {
//var _binding = context.workbook.bindings.getItem("MyBinding");
var _binding = context.workbook.bindings.getItemAt(0);
var _range = _binding.getRange();
_range.load("address");
return context.sync()
.then(function () {
console.log("Binding range address is " + _range.address);
});
});
}
}
Null-Input (2D array)
null input inside two-dimensional array (for values, number-format, formula) is ignored in the update API. No update will take place when null is sent.
Only some parts of the Range Number Format are set while retaining the existing Number Format on the remaining part (by passing nulls).
range.values = [["Eurasia", "29.96", "0.25", "15-Feb" ]];
range.numberFormat = [[null, null, null, "m/d/yyyy;@"]];
null input for a property
null is not a valid single input for the entire property. For example, the following is not valid as the entire values cannot be set to null or ignored.
range.values= null;
The following is not valid either as null is not a valid color value.
range.format.fill.color = null;
Null-Response
Representation of formatting properties that consists of non-uniform values would result in the return of a null value in the response.
An Excel Range can consist of one of more cells. In cases where the individual cells contained in the Range specified don't have uniform formatting values, the range level representation will be undefined.
"size" : null,
"color" : null,
Blank Input and Output
Blank values in update requests are treated as instruction to clear or reset the respective property. Blank value is represented by two double quotation marks with no space in-between. ""
For values, the range value is cleared out. This is the same as clearing the contents in the application.
For numberFormat, the number format is set to General.
For formula and formulaLocale, the formula values are cleared.
For read operations, expect to receive blank values if the contents of the cells are blanks. If the cell contains no data or value, then the API returns a blank value.
range.values = [["", "some", "data", "in", "other", "cells", ""]];
range.formula = [["", "", "=Rand()"]];
Unbounded Range (Read)
Unbounded range address contains only column or row identifiers and unspecified row identifier or column identifiers (respectively), such as:
C:C, A:F, A:XFD (contains unspecified rows)
2:2, 1:4, 1:1048546 (contains unspecified columns)
When the API makes a request to retrieve an unbounded Range (e.g., getRange('C:C'), the response returned contains null for cell level properties such as values, text, numberFormat, formula, etc.. Other Range properties such as address, cellCount, etc. will reflect the unbounded range.
Unbounded Range (Write)
Setting cell level properties (such as values, numberFormat, etc.) on unbounded Range is not allowed as the input request might be too large to handle.
The following is not a valid update request because the requested range is unbounded. This will return an error.
var range = context.workbook.worksheets.getActiveWorksheet().getRange("A:B");
range.values = 'Due Date';
Large Range
Large Range implies a Range whose size is too large for a single API call.
Many factors such as number of cells, values, numberFormat, formulas, etc. contained in the range can make the response so large that it becomes unsuitable for API interaction.
The API makes a best attempt to return or write to the requested data.
The large size involved might result in an error.
To avoid such a condition, using read or write with smaller range sizes
Single Input Copy
To support updating a range with the same values or number-format or applying same formula across a range, the following convention is used in the set API. In Excel, this behavior is similar to inputting values or formulas to a range in the CTRL+Enter mode.
The API will look for a single cell value and, if the target range dimension doesn't match the input range dimension, it will apply the update to the entire range in the CTRL+Enter model with the value or formula provided in the request.
Function that writes to a div with id='message' on the page.
function write(message){
document.getElementById('message').innerText += message;
}
Displaying YouTube Videos
Dynamically create an html script element
function loadVideoDetails (videoindex) {
var script = document.createElement("script");
script.setAttribute("src","https://. . . ");
document.getElementsByTagName('head')[0].appendChild(script)
}
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrevNext