Writing Wolfram APIs

The CloudConnector for Excel uses the Wolfram API Excel function to call Wolfram APIs that live in a Wolfram Cloud. This section describes how to write and deploy APIs.

Environments for Deployment

Wolfram APIs can be deployed from a Cloud notebook or from the Desktop Environment.

Cloud Notebook

To deploy from a Cloud notebook, you can use a web browser to sign in to a Wolfram Cloud. Then open a Cloud notebook, and you should be able to deploy APIs to the Wolfram Cloud you are signed into.

Desktop Environment

To deploy from the Wolfram Desktop Environment, you can use CloudConnect to sign in. If the target cloud is a Enterprise Private Cloud, you must first set the Cloud Base:

CloudConnect will sign in so that you can deploy Wolfram APIs:

Deploying Wolfram APIs

Wolfram APIs are functions written in the Wolfram Language and deployed to a Wolfram Cloud. You can develop and deploy Wolfram APIs in a notebook, on the desktop or in the cloud. An example is shown here:

This APIFunction has one parameter, which is named . You can test the API from inside a notebook by passing in the necessary parameters:

Use the CloudDeploy function to deploy an APIFunction to the Wolfram Cloud. The deployed cloud object is known as a Wolfram API:

For a Wolfram API to work correctly with the CloudConnector for Excel, you must add the option AllowedCloudExtraParameters and set to All to your APIFunction. If this is not set, the plugin will be unable to use it and an error will result.

The Permissions option is set to "Public". This allows users to call your API without signing in. Other setting for this option can be made; these are described in the section on permissions.

Deployed Wolfram APIs can be tested with a web request, within the Wolfram Language. URLRead is the simplest function to perform such a request:

The API that was created requires the parameter "x", which was not included in the request, and so an HTTP status code of 400 was thrown. The content of the response of the response can be read:

To create a request that is successful, the parameter "x" must be added and must be of Interpreter type "Integer". To construct a request with a parameter, you will need to add a query to the request. This requires use of the HTTPRequest function:

If you get the desired result at this point, it is highly likely that your API will run successfully within Excel. If not, please double-check your Permissions and AllowedCloudExtraParameters.

More information on writing and deploying Wolfram APIs is available from the reference pages for APIFunction and CloudDeploy.

More information on how to call Wolfram APIs can be found in the Calling Wolfram APIs section.

Interpreting Excel Data

Parameter data sent from Excel to a Wolfram API is always validated through an Interpreter, as specified in the APIFunction. This ensures functions are called with the correct data. It also gives a significant degree of security and avoids malicious attacks.

There is an error if the data does not fit the Interpreter type specification. For example, if the Wolfram API specifies a parameter should be a single string and a matrix of numbers is sent, there will be an error.

A single cell sent to the Wolfram Cloud can be interpreted as a single item, a vector or a matrix. A row or column of cells can be interpreted as a vector or a matrix. A grid of cells can only be interpreted as a matrix. The details of the interpretation are governed by the Interpreter type.

A full list of all Interpreter types can be found using the function $InterpreterTypes, but not all of these will be compatible with Excel. The core type specifications for APIFunction that are compatible with WolframAPI in Excel are given in the following table.

Scalar

A WolframAPI can take the contents of a single cell as a string, number or date.

The following API function squares a number with the name "num":

 APIFunction[
    {"num" -> "Number"},
    #num2&,
    AllowedCloudExtraParametersAll
]

One useful Interpreter type is "ExcelDate". This will convert a date from Excel into a Wolfram Language DateObject. An API function that uses "ExcelDate" is shown here. It takes a date from Excel and returns the day of the week that date has:

 APIFunction[
    {"date" -> "ExcelDate"}, 
    #date["Day"]&, 
    AllowedCloudExtraParametersAll
]

An API function that has a scalar Interpreter type, such as "ExcelDate", will not accept input that comes from several cells.

Vectors

WolframAPI can take input from a row or column. The Interpreter type for the APIFunction should use RepeatingElement. The following example takes a list of numbers and returns the mean:

 APIFunction[
    {"vector" -> RepeatingElement["Number"]}, 
    Mean[#vector]&, 
    AllowedCloudExtraParametersAll
]

RepeatingElement allows for a row or column of cells from Excel to be used as Input, with each value of Interpreter type "Number".

This API function could be called with a single cell, a row or a column where each element can be interpreted as a number. If a grid of cells is given or any of the cells are not numbers, an error will be thrown.

Matrices

WolframAPI can take input from a block of cells. The type specification for the APIFunction should use RectangularRepeatingElement or SquareRepeatingElement. The following example takes a matrix of numbers and returns a MatrixPlot of those values:

 APIFunction[
    {"matrix" -> RectangularRepeatingElement["Number"]}, 
    MatrixPlot[#matrix]&, 
    AllowedCloudExtraParametersAll
]

This API function can be called with a single cell, a row, a column or a block of cells; each element can be interpreted as a number. If any cell given cannot be interpreted as a number, then an error will be thrown.

Compound Elements

WolframAPI can take compound values from several cells. The type specification for the APIFunction should use RepeatingElement combined with CompoundElement. The following example takes a list of dates and numbers and generates totals of the numbers grouped by date:

 APIFunction[
    {"data" -> RepeatingElement[CompoundElement[{"ExcelDate", "Number"}]]}, 
    Map[Total[#[[All,2]]]&,GroupBy[#data,#[[1]]["Year"]&]]&, 
    AllowedCloudExtraParametersAll
]

This API function should be called from two columns in Excel; the first column should hold a list of dates, the second a list of numbers. If any cell in the first column cannot be interpreted as a number, then an error will be thrown. Likewise, if any cell in the second column cannot be interpreted as a date, then an error will be thrown.

Formatting of Results

Results of Wolfram APIs will automatically format a number of Wolfram Language expressions into Excel-compatible types. These are outlined in the following table.

Formatting of a Wolfram API result in Excel.

If the result of the API is not one of these types, the CloudConnector will be unable to format the data and will return an Excel error (#N/A).

Scalars

If the result of an operation is a single value, CloudConnector for Excel will insert the contents into the cell that called the Wolfram API. This works if the result is a string, a number or a date. The following APIFunction returns a random real number between 1 and 10:

APIFunction[{}, 
    RandomReal[10]&, 
    AllowedCloudExtraParametersAll
]

The result might appear as in the following.

List

If the result of an operation is a list, CloudConnector for Excel will automatically expand the Excel formula into a formula array, creating a column of cells. The following APIFunction returns a list of random numbers between 0 and 1. The number of values is designated by the parameter, "num":

APIFunction[
    {"num""Number"}, 
    RandomReal[{0, 1}, #num]&, 
    AllowedCloudExtraParametersAll
]

The result might appear as below.

When a computation runs, old results from a previous run of the computation are deleted and new results are added. If this would result in a cell being overwritten because it already contains data from other Excel operations, an error will be thrown and the data will not be overwritten. If this happens, you will need to move the overlapping data and reevaluate the WolframAPI function.

List of Lists

If the result of an operation is a list of lists, CloudConnector for Excel will expand the Excel formula into a formula array, creating a matrix of cells. The following APIFunction returns a list of lists of random numbers between 0 and 1. The number of rows is designated by the parameter, "nrow", and the number of columns is designated by the parameter, "ncol":

 APIFunction[
    {"nrow"  "Integer", "ncol"  "Integer"}, 
    RandomReal[{0, 1},{#nrow, #ncol}]&, 
    AllowedCloudExtraParameters  All
]

The result in Excel follows the Wolfram standard, so that each inner list creates a row in Excel. The result might appear as follows.

When a computation runs, old results from a previous run of the computation are deleted and new results are added. If this would result in a cell being overwritten because it already contains data from other Excel operations, an error will be thrown and the data will not be overwritten. If this happens, you will need to move the overlapping data and reevaluate the WolframAPI function.

Association

If the result of an operation is an Association, CloudConnector for Excel will expand the Excel formula into a formula array, creating a matrix of cells.

The following APIFunction takes any number of countries and returns an Association of "Population" and "Area" for each of those countries. The number of countries is designated by the parameter "countries".

 APIFunction[
    {"countries"RepeatingElement["Country"]},
    (<|(#["Name"]<|"Population"->#["Population"],"Area"-> #["Area"]|>)& /@ #countries|>)&,
    AllowedCloudExtraParametersAll
]

When a computation runs, old results from a previous run of the computation are deleted and new results are added. If this would result in a cell being overwritten because it already contains data from other Excel operations, an error will be thrown and the data will not be overwritten. If this happens, you will need to move the overlapping data and reevaluate the WolframAPI function.

Graphics and Images

If the result of an operation is a graphic or image, CloudConnector for Excel will paste a graphic at the WolframAPI location. You can construct a Wolfram API that returns a Graphics or Image expression, and the CloudConnector for Excel plugin will convert the result into an Excel-compatible format. The following APIFunction returns a bar chart that takes a vector of values from Excel:

 APIFunction[
    {"values"RepeatingElement["Number"]}, 
    BarChart[#values]&, 
    AllowedCloudExtraParametersAll
]

3D graphics will also be rasterized and pushed as images to Excel.

Note that if you move the Excel formula that contains graphics into another cell, the link between the two will be broken, and the subsequent evaluation will result in new graphics being created.

Lists Containing Graphics

If the result of an operation is a List that contains graphics or images, CloudConnector for Excel will expand the Excel formula into a formula array and create a matrix of cells. Additionally, any graphics will be pasted at the relative cells. If the result of an API is a list of graphics or images, these are inserted into the spreadsheet in the expected fashion. The following APIFunction returns an image, integer and date, all of which can be formatted appropriately in Excel:

 APIFunction[{}, 
    {1, Now, RandomImage[]}&, 
    AllowedCloudExtraParameters  All
]

Note that if you move the Excel formula that contains graphics into another cell, the link between the two will be broken, and the subsequent evaluation will result in new graphics being created.

Unsupported Result

If the result of an operation is not supported by the existing formatting rules, CloudConnector for Excel will return "#N/A" in the target cell. You use the Rasterize function to get a visualization of the result. The following APIFunction returns a formula that is visualized in Excel:

 APIFunction[{}, 
    Rasterize[Sqrt[x+Sqrt[x]],ImageResolution200]&, 
    AllowedCloudExtraParametersAll
]

You can increase the quality of the resulting image by using the ImageResolution option in Rasterize.

Cloud Objects

A cloud object is a key concept in the Wolfram Cloud. It is used to hold data, functions, notebook documents, scheduled tasks and automated reports. Every cloud object has an owner, a UUID, a name and a set of permissions that control its usage. Typically, the owner is the user who created the cloud object.

When you apply CloudDeploy to an APIFunction, a CloudObject is created. This process is described in more detail in the Writing Wolfram APIs section.

Naming Cloud Objects

When deploying a cloud object to the Wolfram Cloud, it is beneficial to name it so that it is easier to find and for Excel users to access.

CloudDeploy with a single argument will deploy an anonymous CloudObject and can only be identified by its UUID. The second, optional argument of CloudDeploy is the URI, allowing you to specify a location for your cloud object to live. This value can be a CloudObject or a string.

For example, the string "FolderName/CloudObjectName" will deploy your cloud object into the folder "FolderName" with the name, "CloudObjectName":

You can wrap the string with CloudObject to be more explicit:

Accessing Cloud Objects from Wolfram APIs

A Wolfram API can use any cloud object to which the owner has access.

A Wolfram API cannot allow access to cloud objects that are private to the caller and are not accessible to the owner of the API.

Permissions

The Wolfram Cloud provides rich programmatic control over access to cloud objects, including Wolfram APIs. This section shows how to use Permissions to fine-control access to your Wolfram APIs.

Checking Permissions of an API

To check the Permissions of a cloud object, use the Options function:

Private Permissions

A CloudObject can have a permissions setting of "Private". Wolfram APIs with "Private" permissions will only be accessible to the owner. If no permissions setting is given, the default for any deployed cloud object is "Private":

CloudDeploy[
    APIFunction[{} , 1&, AllowedCloudExtraParametersAll], 
    Permissions  "Private"
]

Public Permissions

A CloudObject can have a permissions setting of "Public". Wolfram APIs with "Public" permissions can be called without a user needing to be signed in. An example of a public CloudObject is seen following:

CloudDeploy[
    APIFunction[{} , 1&, AllowedCloudExtraParametersAll], 
    Permissions  "Public"
]

Authenticated

A CloudObject can have a permissions setting of "Authenticated". Wolfram APIs with "Authenticated" permissions can be called by anyone who is signed in. An example of an authenticated CloudObject is seen here:

CloudDeploy[
    APIFunction[{} , 1&, AllowedCloudExtraParametersAll], 
    Permissions  "Authenticated"{"Read"}
]

Use of the "Authenticated" value is particularly useful in conjunction with $RequesterWolframID, as seen in Examples > Developers > Example: Authenticated API.

User Specific

A CloudObject can have a Permissions setting that lists specific users. An example is seen here:

CloudDeploy[
    APIFunction[{} , 1&, AllowedCloudExtraParametersAll],
    Permissions  "test@wolfram.com"{"Read"}
]

This Wolfram API can be called by the user "test@wolfram.com" if they are signed in.

Further Authentication Features

The permissions has a number of other features such as setting up groups. More details are found in the documentation on Permissions.

Cloud Credits

In the Wolfram Public Cloud, calling a Wolfram API will use the cloud credits of the API owner. More information on this can be found in the Cloud Credits Usage section. In a Wolfram Enterprise Private Cloud, there is no accounting system.