Excel Office Script Examples
Content
Introduction
Since July 2020, Microsoft 365 users can record their actions in Excel
on-line into Office Script macros. Earlier, I've posted
an article that shows you how to
get started. On this page, I will list some example scripts which you
cannot record but might come in handy.
Some of these scripts contain literal values, (the values between quotes).
This means things like worksheet names are hard-coded into the example scripts:
"ToC". So if you need a different worksheet name, edit the script accordingly.
Because Microsoft has not yet given us a way to prompt the user for a value,
we must edit the script to change what sheet it affects.
Download
the full code (zip file with all scripts) here.
Video
I've presented parts of this page during
Excel Virtually
Global 2022. Here's the Youtube recording:
Entering zeroes in empty cells
Suppose you want to enter a zero in each empty cell in the selection:
This code does exactly that.
function main(workbook: ExcelScript.Workbook) {
// Your code here
let emptyAreas = workbook.getSelectedRange().getSpecialCells(ExcelScript.SpecialCellType.blanks);
emptyAreas.getAreas().forEach(r => {
r.setValue(0);
});
}
Adding a table of Contents
to your workbook
Working in Excel Online and need a table of Contents for your
workbook quickly? Here's an Office Script that produces one. It generates
a list for all worksheets and all charts, with hyperlinks pointing to their
locations.
First let me show the main function and explain it step by step.
function main(workbook: ExcelScript.Workbook) {
if (workbook.getWorksheet("ToC")) {
let temp = workbook.getWorksheet("ToC")
temp.delete();
}
let allSheets = workbook.getWorksheets();
let tocSheet = workbook.addWorksheet("ToC");
let reportCell = tocSheet.getRange("B2");
let newTable = addTocTable("Worksheets", workbook, reportCell);
reportCell = reportCell.getOffsetRange(2, 0);
reportCell.getResizedRange(0, 1).setValues([["Worksheet", "Link"]]);
for (let i = 0; i < allSheets.length; i++) {
reportCell.getOffsetRange(i + 1, 0).setValue(allSheets[i].getName());
}
reportCell.getOffsetRange(1, 1).setFormulaR1C1("=HYPERLINK(\"#'\"&RC[-1]&\"'!A1\",RC[-1])");
reportCell = reportCell.getOffsetRange(newTable.getRowCount() + 2, 0)
newTable = addTocTable("Charts", workbook, reportCell);
reportCell = reportCell.getOffsetRange(2, 0)
reportCell.getResizedRange(0,3).setValues([["Worksheet", "Name", "Location", "Link"]]);
let k = 0;
for (let i = 0; i < allSheets.length; i++) {
let allCharts = allSheets[i].getCharts();
for (let j = 0; j < allCharts.length; j++) {
k++;
let theSheet = allSheets[i];
reportCell.getOffsetRange(k, 0).setValue(allSheets[i].getName());
reportCell.getOffsetRange(k, 1).setValue(allCharts[j].getName());
let topLeftCell = getCellUnderChart(allCharts[j], allSheets[i]);
let topLeftAddress = topLeftCell.getAddress();
if( topLeftAddress.charAt(0)=="'"){
topLeftAddress="'" + topLeftAddress;
}
reportCell.getOffsetRange(k, 2).setFormula(topLeftAddress);
}
}
reportCell.getOffsetRange(1, 3).setFormulaR1C1("=HYPERLINK(\"#\"&RC[-1],\"Link\")");
tocSheet.activate();
}
These are the important steps in this code:
- check whether a worksheet called ToC exists and if it does, delete
it.
- Fetch a list of all worksheets and store that in a variable called
allSheets
- Add a worksheet called "ToC" and assign it to a variable called
tocSheet
- Assign cell B2 to the variable reportCell
- Add a table to the table of contents using the function addTocTable
(see below)
- To ensure we start adding the sheet names on the first empty row
of the table, we offset the reportCell with two so it now points to
cell B4
- We add the headings (Worksheet and Link) to the newly inserted table
- Now it is time to loop through the allSheets collection and list
the name of each sheet in the first column of the table
- After looping through the sheets, we add the HYPERLINK formula to
the second column, using the name of the worksheet in the first column
to create a link to each worksheet
- We offset the reportCell position by adding the number of rows in
the worksheet table (plus two rows extra for spacing)
- Now we add the heading for the charts table and an empty table to
hold them, using that addTocTable function again
- The Charts table has four columns: Worksheet, Name, Location and
Link
- We run through all worksheets and for every worksheet we
- run through all charts
- fill the chart table with the appropriate information: Worksheet,
Name and Location (location required me to design a function called
getCellUnderChart because Office Script does not have a TopLeftCell
property of a chart like VBA has)
- There's a snag for charts on sheets which contain a space in
their name; the correct address of the topleftCell in sheets like
that have apostrophes around the name: 'Blad 2'!F1. However, if
you write 'Blad 2'!F1 to a cell, Excel strips that first apostrophe.
Hence the if statement that adds an additional apostrophe in such
cases.
- Once we've done all charts on all worksheets, we place the HYPERLINK
formula in the last column again using the address information in the
third column
The main function uses two helper functions, here's the addTocTable one:
function addTocTable(caption: String, workbook: ExcelScript.Workbook, location: ExcelScript.Range): ExcelScript.Table {
location.setValue(caption);
location.getResizedRange(0, 1).setPredefinedCellStyle("Heading 1");
location = location.getOffsetRange(2, 0);
let newTable = workbook.addTable(location, true);
return newTable;
}
All it does is add the caption in the location cell, style it as "Heading
1" and add a new one-column table. The function returns the table as an
object so we can use that object in our calling routine.
Note that this script is language-dependent. If your Excel
doesn't display "Heading 1" as one of the cell styles, the script will fail
with an error. Edit the Heading 1 text to match one of your available styles
in your Home tab, Cell Styles gallery.
And here is the getCellUnderChart function:
function getCellUnderChart(cht: ExcelScript.Chart, ws: ExcelScript.Worksheet): ExcelScript.Range {
let topLeftCell = ws.getRange("A1");
do {
topLeftCell = topLeftCell.getOffsetRange(0, 1)
}
while (topLeftCell.getLeft() < cht.getLeft());
do {
topLeftCell = topLeftCell.getOffsetRange(1, 0)
}
while (topLeftCell.getTop() < cht.getTop());
return topLeftCell.getOffsetRange(-1, -1);
}
The basic principle is simple: the function first loops through the cells
of the worksheet horizontally, starting from cell A1, until it has found
a cell with a Left value that is greater than or equal to the chart's Left
position. After that it does the same vertically to find the Top position.
Because the two loops take us one column too far to the right and one row
too far down, we offset the found cell by (-1, -1) so we return the correct
cell which denotes where the top-left corner of the chart is located.
Adding a list of Range names
Desktop Excel has a simple feature to add a list of range names to your
workbook (Formulas tab, Use in Formula drop-down, Paste List). But Excel
on-line does not (yet) have that feature. Here is an Office script which
adds a worksheet called "Names List" to your workbook, listing all range
names and some of their properties.
function main(workbook: ExcelScript.Workbook) {
var RowCt = 1;
var sheetName = "";
let MyNames = workbook.getNames();
let selectedSheet = workbook.getWorksheet("Names list");
if (selectedSheet) {
selectedSheet.delete();
}
let worksheets = workbook.getWorksheets();
selectedSheet = workbook.addWorksheet("Names list");
selectedSheet.getRange("B2:E2").setValues([["Scope", "Name", "RefersTo", "Visible"]]);
for (let i = 0; i < MyNames.length; i++) {
RowCt++
selectedSheet.getCell(RowCt, 1).getResizedRange(0, 3).setValues([
["Workbook", MyNames[i].getName(), "'" + MyNames[i].getFormula(), "'" + MyNames[i].getVisible()]]);
}
for (let i = 0; i < worksheets.length; i++) {
sheetName = worksheets[i].getName();
var myNames = worksheets[i].getNames();
for (let j = 0; j < myNames.length; j++) {
RowCt++;
selectedSheet.getCell(RowCt, 1).getResizedRange(0, 3).setValues([
[sheetName, myNames[j].getName(), "'" + myNames[j].getFormula(), "'" + myNames[j].getVisible()]
]);
}
}
let newTable = workbook.addTable(selectedSheet.getRange("B2").getSurroundingRegion(), true);
newTable.getRange().getFormat().autofitColumns();
}
The resulting table looks like this:
OfficeScript does not have a feature like the VBA InputBox, nor can you
design input screens. So we need a work-around to get information from the
user. The script below checks for presence of a worksheet called "Script_Input".
If that sheet is not in the workbook, it gets added. Next a function is
called which allows you to pass it a prompt and an array of inputs, which
will be added to the Script_Input sheet. The resulting input sheet looks
like this:
If the script detects that the "Script_Input" sheet is available, it
assumes all input has been completed and runs an action with that input
(writes to the console).
This is the entire script:
function main(workbook: ExcelScript.Workbook) {
if (workbook.getWorksheet("Script_Input")) {
//we assume input is there, here are your script's main actions
let inputSheet = getInputSheet(workbook, "Script_Input");
let name = inputSheet.getRange("B3").getValue();
let dob = inputSheet.getRange("B4").getValue();
console.log(name + " " + dob);
}
else {
createPromptSheet(workbook, "Please enter your details below", ["Name", "DoB"])
//It
may be a good idea to delete the Script_Input sheet now
}
}
function createPromptSheet(workbook: ExcelScript.Workbook, prompt: string, questions: string[]) {
let inputSheet = getInputSheet(workbook, "Script_Input");
let titleCell = inputSheet.getRange("A1")
titleCell.setValue(prompt);
titleCell.setPredefinedCellStyle("Heading 1");
titleCell.getFormat().autofitColumns();
let questionCell = inputSheet.getRange("A3");
let rw = 0;
for (let i = 0; i < questions.length; i++) {
rw++;
questionCell.getOffsetRange(i, 0).setValue(questions[i]);
questionCell.getOffsetRange(i, 0).setPredefinedCellStyle("Heading 4");
questionCell.getOffsetRange(i, 1).setPredefinedCellStyle("Input");
}
inputSheet.activate();
questionCell.getOffsetRange(rw + 2, 0).setValue("After entering all data, click the script button again.");
questionCell.getOffsetRange(rw + 2, 0).setPredefinedCellStyle("Heading 4");
questionCell.getOffsetRange(0, 1).select();
}
function getInputSheet(workbook: ExcelScript.Workbook, name: string) {
if (workbook.getWorksheet(name)) {
return workbook.getWorksheet(name);
}
else {
return workbook.addWorksheet(name);
}
}
Download
Download
the full code (zip file with all scripts) here.
Next step: Call this script from a Power Automate Flow
In my next article I describe how to
apply the Table
Of Content script to all Excel files in a Sharepoint folder using Power
Automate
