To set the context for the sort of code you’ll be writing, let’s take a very simple but canonical example of an automation task. This particular example will use Excel, but the exact same concepts apply to any of the other applications (Word, OneNote) that have adopted the new host-specific/Office 2016+ API model.
Scenario: Imagine I have some data on the population of the top cities in the United States, taken from “Top 50 Cities in the U.S. by Population and Rank” at http://www.infoplease.com/ipa/a0763098.html. The data – headers and all, just like I found it on the website – describes the population over the last 20+ years.
Let’s say the data is imported into Excel, into a table called “PopulationData”. The table could just as easily have been a named range, or even just a selection – but having it be a table makes it possible to address columns by name rather than index. Tables are also very handy for end-users, as they can filter and sort them very easily. Here is a screenshot of a portion of the table:

Now, suppose my task is to find the top 10 cities that have experienced the most growth since 1990. How would I do that?
The code in the next few pages shows how to perform this classic automation scenario. As you look through the code, if not everything will be immediately obvious – and it probably won’t be – don’t worry: the details of this code is what the rest of this chapter (and, to some extent, the book) is all about! But I think it’s still worth reading through the sample as is for the first time, to gain a general sense of how such task would be done via Office.js.
Note: In a more real-world scenario, this sample would be broken down into ~4 functions: one to read the data, another to calculate the top 10 changed cities, a third to write out a table, and a forth to bind a chart to the data. For purposes of this sample, though – and in order to make it easily readable from top to bottom, rather than having the reader jump back and forth – I will do it in one long function.
1
Excel
.
run
(
function
(
context
)
{
2
// Create proxy objects to represent the "real" workbook objects
3
// that we'll be working with. More information on proxy objects
4
// will be presented in the very next section of this chapter.
5
6
var
table
=
context
.
workbook
.
tables
.
getItem
(
"PopulationTable"
);
7
8
var
nameColumn
=
table
.
columns
.
getItem
(
"City"
);
9
var
latestPopulationColumn
=
table
.
columns
.
getItem
(
10
"7/1/2014 population estimate"
);
11
var
earliestCensusColumn
=
table
.
columns
.
getItem
(
12
"4/1/1990 census population"
);
13
14
// Now, load the values for each of the three columns that we
15
// want to read from. Note that, to support batching operations
16
// together (again, you'll see more in the upcoming sections
17
// of this chapter), the load doesn't *actually* happen until
18
// we do a "context.sync()", as below.
19
20
nameColumn
.
load
(
"values"
);
21
latestPopulationColumn
.
load
(
"values"
);
22
earliestCensusColumn
.
load
(
"values"
);
23
24
return
context
.
sync
()
25
.
then
(
function
()
{
26
// Create an in-memory array of the retrieved data,
27
// where each object represents information about the city.
28
var
cityData
=
[];
29
30
// Start at i = 1 (that is, 2nd row of the table --
31
// remember the 0-indexing) in order to skip the header.
32
for
(
var
i
=
1
;
i
<
nameColumn
.
values
.
length
;
i
++
)
{
33
// A couple of the cities don't have data for 1990,
34
// so skip over those.
35
36
// Note that because the "values" is a 2D array
37
// (even though, in this particular case, it's just
38
// a single column), need to extract out the 0th
39
// element of each row.
40
var
population1990
=
earliestCensusColumn
.
values
[
i
][
0
];
41
42
// Otherwise, push the data into the in-memory store
43
cityData
.
push
(
44
{
45
name
:
nameColumn
.
values
[
i
][
0
],
46
growth
:
47
latestPopulationColumn
.
values
[
i
][
0
]
-
48
earliestCensusColumn
.
values
[
i
][
0
]
49
}
50
);
51
}
52
53
var
sorted
=
cityData
.
sort
(
function
(
city1
,
city2
)
{
54
return
city2
.
growth
-
city1
.
growth
;
55
// Note the opposite order from the usual
56
// "first minus second" -- because want to sort in
57
// descending order rather than ascending.
58
});
59
var
top10
=
sorted
.
slice
(
0
,
10
);
60
61
// Now that we've computed the data, create a new worksheet
62
// for the output. Note that, per Excel behavior,
63
// no two worksheets may share a name. Since we explicitly
64
// specify the name that we'd like to give to the sheet,
65
// this call will fail if an existing sheet with the same
66
// name already exists. A later section in the book
67
// describes methods for checking an object's existence.
68
69
var
outputSheet
=
context
.
workbook
.
worksheets
.
add
(
70
"Top 10 Growing Cities"
);
71
72
var
sheetHeader
=
outputSheet
.
getRange
(
"B2:D2"
);
73
sheetHeader
.
values
=
74
[[
"Top 10 Growing Cities"
,
""
,
""
]];
75
sheetHeader
.
merge
();
76
sheetHeader
.
format
.
font
.
bold
=
true
;
77
sheetHeader
.
format
.
font
.
size
=
14
;
78
79
var
tableHeader
=
outputSheet
.
getRange
(
"B4:D4"
);
80
tableHeader
.
values
=
81
[[
"Rank"
,
"City"
,
"Population Growth"
]];
82
var
table
=
outputSheet
.
tables
.
add
(
83
"B4:D4"
,
true
/*hasHeaders*/
);
84
85
86
// Could use a standar loop:
87
// "for i = 0; i < array.length; i++",
88
// but opting for an often-more-convenient
89
// "array.forEach" approach instead:
90
top10
.
forEach
(
function
(
item
,
index
)
{
91
table
.
rows
.
add
(
92
null
/* null means "add to end" */
,
93
[
94
[
index
+
1
,
item
.
name
,
item
.
growth
]
95
]);
96
// Note: even though adding just a single row,
97
// the API still expects a 2D array for consistency
98
// and interoperability with Range.values.
99
});
100
101
// Auto-fit the column widths, and set uniform
102
// thousands-separator number formatting on the
103
// "Population" column of the table.
104
table
.
getRange
().
getEntireColumn
().
format
105
.
autofitColumns
();
106
table
.
getDataBodyRange
().
getLastColumn
()
107
.
numberFormat
=
[[
"#,##"
]];
108
109
110
// Finally, with the table in place, add a chart:
111
112
var
fullTableRange
=
table
.
getRange
();
113
114
// For the chart, no need to show the "Rank", so only use
115
// the columns for the citys' names and population deltas:
116
var
dataRangeForChart
=
117
fullTableRange
.
getColumn
(
1
).
getBoundingRect
(
118
fullTableRange
.
getLastColumn
());
119
120
// A note on the function call above:
121
// Range.getBoundingRect can be thought of like a
122
// "get range between" function, creating a new range
123
// between this object (in our case, the column at
124
// index 1, which is the "City" column), and
125
// the last column of the table ("Population Growth").
126
127
var
chart
=
outputSheet
.
charts
.
add
(
128
Excel
.
ChartType
.
columnClustered
,
129
dataRangeForChart
,
130
Excel
.
ChartSeriesBy
.
columns
);
131
132
chart
.
title
.
text
=
"Population Growth: 1990 to 2014"
;
133
134
var
tableEndRow
=
135
3
/* row #4 -- remember that we're 0-indexed */
+
136
1
/* the table header */
+
137
top10
.
length
/* presumably 10 */
;
138
139
var
chartPositionStart
=
outputSheet
.
getRange
(
"F2"
);
140
chart
.
setPosition
(
141
chartPositionStart
,
142
chartPositionStart
.
getOffsetRange
(
143
19
/* 19 rows down, i.e., 20 rows in total */
,
144
9
/* 9 columns to the right, so 10 in total */
145
)
146
);
147
148
outputSheet
.
activate
();
149
})
150
.
then
(
context
.
sync
);
151
})
152
.
catch
(
function
(
error
)
{
153
console
.
log
(
error
);
154
// Log additional information, if applicable:
155
if
(
error
instanceof
OfficeExtension
.
Error
)
{
156
console
.
log
(
error
.
debugInfo
);
157
}
158
});
If you run the above code, here is what the resulting sheet looks like:

Now, let’s dive in and see how this sample works.