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.