How to Create Source File for Mashape APIs

Excel Ignite is an Excel add-on that allows users to retrieve data from online APIs, as User Defined Functions (UDF). To make your API accessible through Excel Ignite, all you need to do is to create an XML-based Source file, which defines

  • Available functions, API end points and their inputs
  • How the Excel Ignite would connects to the corresponding APIs, with the inputs provided
  • Detailed mapping between API response and available outputs for the Excel function

The APIs can be publically accessible or local to a specific environment. The following is instruction of how to create a source file.

Prerequisite

  1. Download the Excel Ignite here.
  2. Unzip the installation package and install it.
  3. Once installed, open Excel and enter =Ignite_Browse() in any cell.
  4. The =Ignite_Browse() function will open Windows Explorer in the folder where sources files are kept.
    This is also where your source file should be added.

Mashape Example

Below is an example of how users can use the "mashape-enclout" source file with Excel Ignite to retrieve data from multiple APIs, and how you can create the "mashape-enclout" source file.

Excel Example

In the example below, users want to retrieve IP address (A record in DNS), root domain and meta-description of facebook.com. It can be achieved by typing in =Ignite("mashape-enclout", function, output, input1, input2, input3 ...) in Excel, where "Ignite" is the Excel Ignite add-on, "mashape-enclout" is the source file name, and function represents the API functions defined within the mashape-enclout source filer, then followed with the output name and inputs.

You can define multiple functions within a single source file. Each function can have multiple outputs (extracted from the API response), therefore, users need to specify which output they wants to retrieve using the output parameter. Only one output is returned to Excel. Excel Ignite can accept more than one input parameters, when calling the ignite function, the inputs are listed at last, using optional parameter feature from Excel.

Mashape enclout

In the above spreadsheets, column A are inputs, column B are the outputs using Excel Ignite.

  1. B1 cell retrieves "A" record for "facebook.com" using the "dns" function defined in the mashape-enclout.xml source file.
    =Ignite("mashape-enclout", "dns", "A", A1)
  2. B2 cell retrieves the "root-domain" for "http://www.facebook.com/path/index.php", using the "urlparse" function defined in the same source file.
    =Ignite("mashape-enclout", "urlparse", "root-domain", A2)
  3. B3 cell retrieves the meta description from Facebook website,
    =Ignite("mashape-enclout", "content", "meta_description", A3)

Mashape EnClout DNS API Example

Below is an example to create the source file that only contains the DNS function, using Mashape EnClout DNS API. This Mashape API takes following three parameters
  1. auth_token: this parameter is embedded in the URL with pre-configured value.
  2. url: this parameter is an input to the API. In the above screenshot, this input comes from A1 cell.
  3. X-Mashape-Authorization: this parameter is a pre-configured HTTP header parameter for Mashape authentication.
curl --include --request GET 'https://enclout-dns.p.mashape.com/show.json?auth_token=JPbbCaVFYdnpm2xY9aq1&url=facebook.com' --header "X-Mashape-Authorization: 5ZYZkKff90mshmc5NQy5EEGx1FNXp10EWnSjsnnY4zP2JofH6u"
Response
{
  "dns_entries": [
      { "RData": "\"v=spf1 redirect=_spf.facebook.com\"", "Type": "TXT" },
      { "RData": "173.252.100.16", "Type": "A" },
      { "RData": "a.ns.facebook.com. dns.facebook.com. 2013010300 7200 1800 604800 120", "Type": "SOA" },
      { "RData": "a.ns.facebook.com.", "Type": "NS" },
      { "RData": "b.ns.facebook.com.", "Type": "NS" },
      { "RData": "10 smtpin.mx.facebook.com.", "Type": "MX" }
      ]
}
In order to create a source that can respond to the above API, we will create an XML file describes the API, its inputs and available outputs, such as
<?xml version="1.0" encoding="utf-8" ?>
<ignite version="1.0" source="mashape-enclout">
 <func name="dns" endpoint="https://enclout-dns.p.mashape.com/show.json?" method="GET" format="JSON" jsonRootForXML="dns" >
    <description> This function returns content meta information related to a domain </description>
    <inputs>
        <input name="url" type ="input1" />
        <input name="auth_token" type="config" value="JPbbCaVFYdnpm2xY9aq1"/>
        <input name="X-Mashape-Authorization" type="config" value="5ZYZkKff90mshmc5NQy5EEGx1FNXp10EWnSjsnnY4zP2JofH6u" method="HTTP_HEADER"/>
    </inputs>
    <outputs>
        <output name="SOA" path="//dns/dns_entries[Type='SOA']/RData"/>
        <output name="A" path="//dns/dns_entries[Type='A']/RData" />
        <output name="AAAA" path="//dns/dns_entries[Type='AAAA']/RData"/>
        <output name="NS" path="//dns/dns_entries[Type='NS']/RData"/>
        <output name="MX" path="//dns/dns_entries[Type='MX']/RData"/>
    </outputs>
 </func>
</ignite>

The API response is in the JSON format. Excel Ignite supports APIs with JSON or XML response. In the source file, you need to define the mapping to extract the output from the returning object. For XML, we use XPATH for node navigation. For JSON, we use Newtonsoft.Json.Linq library for JSON parsing. You also have the option to use jsonRootForXML="dns" to convert from JSON to XML, and then use XPATH for node navigation.

In the above example, we specify three types of inputs. Parameters with config type are passed to the API as GET parameters. Parameters with http_header type are passed to the API as http headers. Parameters with "input" prefix are data provided by Excel users. The integer after the prefix determines the order in the Excel function call.

Deployment

Once the source file is created, in order to use it, Excel users just need to drop the file to the Ignite source file folder to deploy it. =Ignite_Browse() function will open Windows Explorer in the folder where sources files are kept.

Complete Mashape files

Mashape-Enclout Source file
Mashape-enclout Excel file