Office Scripts

Set Number Format

Excel.run(function (context) {

context.workbook.worksheets.getActiveWorksheet().getRange("A1").numberFormat = "d-mmm";

return context.sync();

}).catch(function (error) {

console.log(error);

});

Table Rows

Excel.run(function (context) {

var rows = context.workbook.tables.getItem("Table1").rows.load("values");

return context.sync()

.then(function () {

for (var i = 0; i < rows.items.length; i++){

var rng = rows.getItemAt(i).getRange();

if (rows.items[i].values[0][1] > 2){

rng.format.fill.color = "#ff0000";

}

else{

rng.format.fill.color = "#00ff00";

}

}

})

.then(context.sync);

}).catch(function (error) {

console.log(error);

});

Table Rows based on largest value

Excel.run(function (context) {

var rows = context.workbook.tables.getItem("Table1").rows.load("values");

return context.sync()

.then(function () {

var largestRow = 0;

var largestValue = 0;

for (var i = 0; i < rows.items.length; i++){

if (rows.items[i].values[0][1] > largestValue){

largestRow = i;

largestValue = rows.items[i].values[0][1];

}

}

var largestRowRng = rows.getItemAt(largestRow).getRange();

largestRowRng.format.fill.color = "#ff0000";

})

.then(context.sync);

}).catch(function (error) {

console.log(error);

});

Clear table row formatting

Excel.run(function (context) {

context.workbook.tables.getItem("Table1").getDataBodyRange().clear(Excel.ClearApplyTo.formats);

return context.sync();

}).catch(function (error) {

console.log(error);

});

Changing the font

Excel.run( function(context) {

var wks = context.workbook.worksheets.getActiveWorksheet();

var range = wks.getRange("A1");

range.values = "something";

range.format.font.name = "Arial";

range.format.font.size = 16;

return context.sync();

}

indenting selection

Excel.run(function (context) {

var selectedRange = context.workbook.getSelectedRange().load();

context.sync().then( function () {

var selectedAddress = selectedRange.Address;

var selectedCells = wsh.getRange(selectedAddress);

selectedCell.load('values');

FirstCell.load('rowIndex');

FirstCell.load('columnIndex');

context.sync().then( function () {

var vals = selectedCells.values;

var count = vals.length;

var row = FirstCell.rowIndex;

var col = FirstCell.columnIndex;

for (var rowno = row; rowno < (row + count); rowno ++) {

var Cell = wsh.getCell(rowno, col);

Cell.values = " " + Cell.values;

}

context.sync();

}

var _range = _worksheet.getRange("A1:D10");

_range.load = (["format/borders"]);

for var _bordertype in ['EdgeTop', 'EdgeBottom','EdgeLeft','EdgeRight','InsideHorizontal','InsideVertical']) {

var _item = _range.format.borders.items[_bordertype];

var _style = _item.style;

var _weight = _item.weight;

}