Canonical code sample: reading data and performing actions on the document

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:

Initial table
The population data, imported into an Excel 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.

The canonical data-retrieval-and-reporting automation task
  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:

The resulting worksheet

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