Access the Degree Days.net API from an Office Add-in for Excel
Office Add-ins are written in JavaScript (or TypeScript), and use the Office JavaScript API (Office.js for short) to control Microsoft Office applications such as Excel. The technology makes it easy to write a cross-platform Excel add-in that will run in Excel for Windows, Excel for Mac, and Excel Online (running in a web browser).
Office Add-ins are effectively a modern replacement for VBA. But we have VBA sample code too, so you can call our API using that if you prefer.
Another technology option for an Excel add-in is VSTO, which uses .NET (e.g. C# or Visual Basic.NET) rather than JavaScript. It will only run on Excel for Windows, but, apart from that limitation, it's a good programming environment that makes it easy to call the Degree Days.net API using our .NET client library. However, Microsoft is now steering developers towards their cross-platform Office Add-ins (using Office.js), and the rest of this page focuses on that as it appears to be the best choice for most new development.
You can create a full-featured Office Add-in with forms and buttons and so on, but on this page we are going to keep it simple and show you how to make a simple Office Add-in that provides a few custom functions that you can call from within an Excel worksheet, to fetch degree days from the Degree Days.net API. Simple, but very useful. The animation below shows one of our custom functions in action:
The sample code on this page provides three custom functions for use within Excel – the same three functions you can see listed in the animation above. Each function returns the latest daily heating and cooling degree days, in the base temperatures you specify (in C or F), and covering the number of days that you specify. Each function provides a different way to specify the location you want data for (by postal/zip code, by longitude/latitude position, or by station ID). And they all return the results as a multi-dimensional array, which appears in Excel as a table with three columns: Date, HDD, and CDD.
Although the three custom functions we have written on this page may be useful, they're really just intended as examples. You can use the code here as a starting point to write exactly the custom functions you need, or to get your own Office Add-in fetching data out of the Degree Days.net API in response to a button click or similar (instead of using custom functions).
Microsoft has detailed documentation on Office Add-ins for Excel and Excel custom functions, but here we'll give you a step-by-step walkthrough to get you up and running as quickly as possible.
Note that, at the time of writing, you need an Office 365 subscription to run Office.js custom functions. Oddly you don't need an Office 365 subscription to run a more typical Office Add-in for Excel (just Excel 2016 or later), so maybe this will change in future – you could check the Microsoft docs above to see.
Install Node.js, NPM, and the Yeoman generator
To get started with Office Add-ins, you will need to have Node.js and NPM installed. If you download and install an up-to-date version of Node from nodejs.org, NPM will be included automatically.
You will also need Yeoman, and the Yeoman generator for Office Add-ins, to create a project template with all the necessary dependencies for Office.js. To install both of these, open your command prompt and run the following command:
npm install -g yo generator-office
Create a new project using the Yeoman generator
From the command prompt, navigate to the location where you want your project code to go (it doesn't matter where), and type the following command to start the process of creating an Office Add-in project using the Yeoman generator:
yo office
The Yeoman generator will give you a few options; give it the following:
- Choose a project type:
Excel Custom Functions Add-in project - Choose a script type:
JavaScript(because our sample code here is written in JavaScript, not TypeScript) - What do you want to name your add-in?
DegreeDaysExample
This will create a DegreeDaysExample project folder containing the various files necessary for an Excel add-in.
Add crypto-js to your project
We need the crypto-js v3.3.0 package (a JavaScript package) for the cryptography necessary to get past the API's security scheme. At the time of writing we have to use crypto-js v3.3.0 specifically, because later versions use native JavaScript cryptography functions that are not yet available in Excel JavaScript's runtime.
From the command prompt, navigate into our DegreeDaysExample project folder using:
cd DegreeDaysExample
Then run the following command to install crypto-js v3.3.0:
npm install --save-dev crypto-js@3.3.0
Edit Functions.Namespace in your project's manifest.xml file
Your new DegreeDaysExample project should contain a file called manifest.xml in its root directory. manifest.xml contains the definition of the add-in. You don't need to understand it all, but you should edit the Functions.Namespace property as it determines how you call your custom functions from an Excel worksheet. By default it will say CONTOSO (the name of the fictional company in Microsoft's sample code), we suggest you change it to DEGREEDAYS.
Just search the manifest.xml file for Functions.Namespace and you should quickly see where to change CONTOSO to DEGREEDAYS.
Copy our code into your project's functions.js file
Your new DegreeDaysExample project should contain a file called functions.js, at DegreeDaysExample/src/functions/functions.js. Delete the example functions in functions.js (you can delete everything in that file), then copy in our sample code below:
// Copy the contents of this file into /src/functions/functions.js
// See notes on CryptoJS at www.degreedays.net/api/excel#crypto-js
const CryptoJS = require('crypto-js');
// ************* CUSTOM FUNCTION DEFINITIONS START HERE ***********************
// The metadata provides the name and the parameters used in the function.
// This example provides three functions to return the latest heating and
// cooling degree days to Excel. The different functions provide the options
// for specifying the location: PostalCode, Longitude and Latitude, Station ID.
// Each function takes the heating and cooling base temperatures, temperature
// unit (C or F) and the number of days of data required.
// They all return a dynamic array of data, which will span multiple rows and
// columns in the Excel worksheet, rather than populating a single cell.
/**
* @customfunction
* @param {string} postalCode postal code
* @param {string} countryCode ISO country code
* @param {number} hddBaseTemp heating degree days base temperature
* @param {number} cddBaseTemp cooling degree days base temperature
* @param {string} tempUnit C or F
* @param {number} numberDays number of days of data
* @returns {string[][]} dynamic array with multiple rows.
*/
function latestDailyForPostalCode(postalCode, countryCode, hddBaseTemp,
cddBaseTemp, tempUnit, numberDays) {
const location = {
type: 'PostalCodeLocation',
postalCode: postalCode,
countryCode: countryCode
};
return processRequest(location, hddBaseTemp, cddBaseTemp, tempUnit,
numberDays);
}
/**
* @customfunction
* @param {number} longitude (-90 to +90)
* @param {number} latitude (-180 to +180)
* @param {number} hddBaseTemp heating degree days base temperature
* @param {number} cddBaseTemp cooling degree days base temperature
* @param {string} tempUnit C or F
* @param {number} numberDays number of days of data
* @returns {string[][]} dynamic array with multiple rows.
*/
function latestDailyForLongLat(longitude, latitude, hddBaseTemp, cddBaseTemp,
tempUnit, numberDays) {
const location = {
type: 'LongLatLocation',
longLat: {
longitude: longitude,
latitude: latitude
}
};
return processRequest(location, hddBaseTemp, cddBaseTemp, tempUnit,
numberDays);
}
/**
* @customfunction
* @param {string} stationId station ID
* @param {number} hddBaseTemp heating degree days base temperature
* @param {number} cddBaseTemp cooling degree days base temperature
* @param {string} tempUnit C or F
* @param {number} numberDays number of days of data
* @returns {string[][]} dynamic array with multiple rows.
*/
function latestDailyForStationId(stationId, hddBaseTemp, cddBaseTemp,
tempUnit, numberDays) {
const location = {
type: 'StationIdLocation',
stationId: stationId
};
return processRequest(location, hddBaseTemp, cddBaseTemp, tempUnit,
numberDays);
}
// ************* CUSTOM FUNCTION DEFINITIONS END HERE **************************
// ************* EXCEL HELPER FUNCTIONS START HERE *****************************
// Returning error information to Excel:
// If something goes wrong during the API call, we can display the error
// message directly in the Excel worksheet, by returning the error information
// as a multi-dimensional array (i.e. in the same type as a successful result).
// Note that Microsoft are working on error handling for Excel Custom Functions,
// but this is currently in 'Preview' mode only.
// https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-errors
function errorResult(errorType, message){
return [[errorType, message]];
}
// Function to first validate the temperature unit, then call the function that
// makes the request to the Degree Days.Net API.
function processRequest(location, hddBaseTemp, cddBaseTemp, tempUnit,
numberDays) {
// validate unit
if (!(tempUnit === 'C' || tempUnit === 'F')) {
const errorMessage = 'Invalid temperature unit' + tempUnit +
'. Should be C (celsius) or F (fahrenheit)';
return errorResult('Validation Error', errorMessage);
}
try {
// generate request object
const requestData = generateRequestData(location, hddBaseTemp,
cddBaseTemp, tempUnit, numberDays);
// fetch degree days (returns a Promise)
return fetchDegreeDaysFromServer(requestData)
.then(data => {
// if the API returned an failure response (eg invalid location)
// then return the error code and message
const response = data.response;
if (response.type === 'Failure') {
return errorResult('Failure: ' + response.code,
response.message);
} else {
return parseData(response);
}
})
.catch(error => {
return errorResult('API Error', error.message);
});
} catch (error) {
return errorResult('Unexpected Error', error.message);
}
}
// ************* EXCEL HELPER FUNCTIONS END HERE *******************************
// ************* STEP 1: Create the request ************************************
// First we create a JSON request that specifies what we want from the API.
// See www.degreedays.net/api/json#request for more on this.
function generateRequestData(location, hddBaseTemp, cddBaseTemp, tempUnit,
numberDays) {
// breakdown in this example is for the latest daily figures covering the
// specified number of days.
const breakdown = {
type: 'DailyBreakdown',
period: {
type: 'LatestValuesPeriod',
numberOfValues: numberDays
}
};
// Here we specify 2 DataSpec items: one for HDD and one for CDD. You can
// specify up to 120 DataSpec items in one request (e.g. to fetch data in
// lots of base temperatures). With an API Standard+ account you can have a
// DataSpec for hourly temperature data too.
// We give each DataSpec a unique name (dailyHDD and dailyCDD) so we can get
// the corresponding DataSet items from the response.
const locationDataRequest = {
type: 'LocationDataRequest',
location: location,
dataSpecs: {
dailyHDD: {
type: 'DatedDataSpec',
calculation: {
type: 'HeatingDegreeDaysCalculation',
baseTemperature: {
unit: tempUnit,
value: hddBaseTemp
}
},
breakdown: breakdown
},
dailyCDD: {
type: 'DatedDataSpec',
calculation: {
type: 'CoolingDegreeDaysCalculation',
baseTemperature: {
unit: tempUnit,
value: cddBaseTemp
}
},
breakdown: breakdown
}
}
};
return locationDataRequest;
}
// ************* STEP 2: Send the request to the API ***************************
// Next we sign the JSON request and package everything together into an HTTP
// request which we send to the Degree Days.net API. This follows the spec at
// www.degreedays.net/api/json#send
// The API requires the JSON request and the signature to be base64url encoded.
function base64urlEncode(unencoded) {
if (typeof(unencoded) === 'string') {
unencoded = CryptoJS.enc.Utf8.parse(unencoded);
}
return CryptoJS.enc.Base64.stringify(unencoded)
.replace(/=/g, '').replace(/\+/g, '-').replace(/\//g, '_');
}
// For further information on calling web APIs from Custom Functions, see
// https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-runtime
// and
// https://docs.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-web-reqs
function fetchDegreeDaysFromServer(locationDataRequest) {
return new Promise((resolve, reject) => {
// The test API access keys are described at www.degreedays.net/api/test
// They will let you access data for the Cape Cod area only.
// To fetch data for locations worldwide, sign up for a proper API
// account at www.degreedays.net/api/ and copy your API access keys here.
const accountKey = 'test-test-test';
const securityKey = 'test-test-test-test-test-test-test-test-test-test-test-test-test';
const endpoint = 'https://apiv1.degreedays.net/json';
const fullRequest = {
securityInfo: {
endpoint: endpoint,
accountKey: accountKey,
timestamp: new Date().toISOString(),
random: CryptoJS.enc.Hex.stringify(
CryptoJS.lib.WordArray.random(12))
},
request: locationDataRequest
};
const fullRequestJson = JSON.stringify(fullRequest);
// We sign the JSON request and package everything together into an HTTP
// request which we send to the Degree Days.net API.
const signatureBytes = CryptoJS.HmacSHA256(fullRequestJson, securityKey);
// Send the HTTP request to the API servers using XMLHttpRequest.
const params = 'request_encoding=base64url' +
'&signature_method=HmacSHA256' +
'&signature_encoding=base64url' +
'&encoded_request=' + base64urlEncode(fullRequestJson) +
'&encoded_signature=' + base64urlEncode(signatureBytes);
const xhr = new XMLHttpRequest();
xhr.open('POST', endpoint, true);
xhr.setRequestHeader('Content-Type',
'application/x-www-form-urlencoded');
xhr.onload = function() {
if (xhr.status === 200) {
var parsedResponse;
try {
parsedResponse = JSON.parse(xhr.responseText);
} catch (error) {
reject(error);
}
resolve(parsedResponse);
} else {
reject(new Error('Got unexpected HTTP response code ' +
xhr.status + ': ' + xhr.statusText));
}
};
xhr.onerror = function() {
reject(new Error("Couldn't connect to the API. It could be that " +
"your network is down, or something is blocking the " +
"connection. Your browser's developer-tools console may have " +
"more details."));
};
xhr.send(params);
});
}
// ************* STEP 3: Process the response from the API *********************
// The JSON response is explained at www.degreedays.net/api/json#response
// Since the request contained dataSpecs for HDD and CDD, the response will
// contain two corresponding dataSets, with matching ids (dailyHDD and dailyCDD).
// These can be combined into a multi-dimensional array which will display in
// Excel as three columns: Date, HDD, CDD, and a row for each date.
function parseData(response) {
// Retrieve the dataSets (matching the dataSpec id)
const hddDailyData = response.dataSets.dailyHDD;
const cddDailyData = response.dataSets.dailyCDD;
// Check if the dataSets contain an error (this would be a failure specific to
// the request, eg not enough data). Since we have requested the same location
// and period for each data set, we expect any error messages to be the same
// for both heating and cooling.
if (hddDailyData.type === 'Failure') {
return errorResult(hddDailyData.code, hddDailyData.message);
} else {
const resultArray = [];
// add a header row for display
const headerRow = ['Date', 'HDD', 'CDD'];
resultArray.push(headerRow);
// combine the results of HDD and CDD into an array of results
for (var i = 0, n = hddDailyData.values.length; i < n; i++) {
const hdd = hddDailyData.values[i];
const cdd = cddDailyData.values[i];
const row = [hdd.d, hdd.v, cdd.v];
resultArray.push(row);
}
return resultArray;
}
}
The sample functions.js code above is just an example of how you can make custom Excel functions to call the Degree Days.net JSON API and put the returned data into a spreadsheet. The JSON API docs have more information about the various ways you can specify the data you want from the API in the JSON request and the data you can expect back in the response. With these docs and the sample code above you will hopefully be able to make your own custom Excel functions to fetch exactly the data you need.
Build and run your project locally to test your custom functions
Yeoman generator will have created your DegreeDaysExample project with everything it needs to be launched locally in a development environment. You just have to open the command prompt, navigate to your DegreeDaysExample folder, and then:
Build the project with:
npm run build
Then run it in Excel with the following command:
npm run start
The first time you run the above, Node will probably ask you whether you want to install a certificate representing "Developer CA for Microsoft Office Add-ins". You need to click "Yes" to this, otherwise the add-in won't load. If you're reading this and you've already clicked "No" (and the add-in hasn't loaded), close Excel, stop node with npm stop, and then run npm run start again, and click "Yes" to the certificate, quickly, before Excel opens.
Excel should open automatically with the add-in loaded so your custom functions will be available for you to use from the spreadsheet. Try entering a formula into any of the cells in the blank worksheet that should have opened, for example:
=DEGREEDAYS.LATESTDAILYFORSTATIONID("KHYA", 15.5, 19, "C", 7)
Note that KHYA is a station in Cape Cod, which will work with the test API account that the sample code above uses. The test account is limited to locations in Cape Cod, which is fine for testing, but unlikely to be ideal for your long-term use. To fetch data for locations worldwide, you'll need to get a full API account (if you haven't got one already) and replace the test API access keys in the sample code with your own API access keys.
The Microsoft documentation has more information on testing and debugging Office add-ins in general and debugging Excel custom functions specifically.
Deploy your project to make your custom functions fully available to yourself and others
Building and running your project locally as described above is really only for testing. When you deploy your add-in properly, its functions will become available for use within all your spreadsheets, and those of anyone else you want to share it with.
Under the covers, an Office Add-in is essentially a static website. When you build your project, it will put all the website files into a dist directory within your DegreeDaysExample project.
As above, open your command line, navigate to your DegreeDaysExample project folder, and run:
npm run build
That will build all the necessary website files into a dist folder within your DegreeDaysExample project folder.
Next you need to host the website (the content of that dist folder) somewhere. You can host it pretty much anywhere (like IIS or Apache), it doesn't have to be hosted on a Node server. Though if possible it's best to host it at an HTTPS URL, to avoid security warnings when using the add-in.
Once you have hosted the website, you need to modify the manifest.xml file (mentioned further above) so that the URLs in that manifest.xml file all point to your hosted website (instead of pointing to localhost as they will by default). In manifest.xml you can also specify the Provider Name, Display Name, and Description for your add-in.
Deployment is then just a question of getting your manifest.xml file to the people who are going to use the add-in. It's not quite as simple as emailing it to them, but there are a number of options. The Microsoft docs on deploying and publishing an Office add-in list various ways to deploy an add-in (such as via SharePoint or the Office 365 Admin Center), but they are basically all just different ways to distribute the manifest.xml file.
Here we'll show you how to deploy it via a shared network drive, as that is an easy option that is likely to be available to most people:
- Put
manifest.xmlon a shared network drive. If you're just doing this for yourself you can easily create a local one by right-clicking a folder on your computer, selecting "Properties", going to the "Sharing" tab, noting down the "Network Path", and clicking "Share". - Within Excel, the user will need to add the shared network drive by going to "File" -> "Options" -> "Trust Center" -> "Trust Center Settings" -> "Trusted Add-in Catalogs", and adding the network path of the shared drive with the "Show in Menu" option selected.
- The add-in should now be available in the "Shared Folder" section of "My Add-Ins" on the "Insert" menu ribbon within Excel.
Problems?
Hopefully the example code above, the JSON API docs, and the JSON API test tool will be enough to get you going, but please feel free to email us if you'd like more help.
Higher level integration details
It is also worth reading the higher-level integration guide for tips on the various approaches to integrating with the API. We have helped a lot of businesses integrate their software with our API so we are very familiar with the patterns that work well for common use cases. The main considerations may be a little different for an Excel Add-in (and especially for custom functions), but a lot of what is covered in the integration guide will probably still be applicable and useful for your project.