Getting Started
Step 1. Preparing the Workbook
The Web Service API needs Inputs and Outputs of your calculation engine; to be defined as Named Ranges in Excel. Therefore, the first step is to define these in the Excel workbook. This can be done in the Defined Names section of the Formulas tab on the Excel ribbon shown below. 
We are using I_EffectiveDate, which refers to IO!B2 and InputList, which refers to IO!B3:B5 for inputs. We are using Plan1_Single, Plan1_Couple, Plan1_Parent, Plan1_Family for outputs, which refer to cells IO!B10:B13.
You can download this test file from RatingEngine.xlsx.
Step 2. Creating a Web Service
Once your workbook is ready to publish, login to SpreadsheetWEB Control Panel and click Create New Application. Select Web Service from the list of application types.
Create New Application button.
Choose Web Service Application.
Application keys are assigned to your web service and will be shown in the Edit Application screen and on the completion screen through the wizard. You will use this key to connect to your application. If you would like to make this application publicly available, Access should be selected as “Unrestricted”, as shown below.
Application key visible on the edit application screen.
Unrestricted access found under Basic Application Options under Edit Application screen.
The sample code uses "f3597d20cb174c1181d432819ecde698" as the application key.
Step 3. Security
If access is “Restricted”, then it will be a private application, requiring API Key and API Password for authorization. These parameters are set individually for each site. If already configured at the site-level, you can obtain these keys under the Edit Application screen for the target application. If not set up under the sites section and you do not have access to the System Administration section of the Control Panel, then please contact your System Admin.
For restricted web services, the site-level client identifier and secret are visible under the edit applications screen. Learn more at Whitelisting.
You will also need the API Service URL and Authentication Service URL for the corresponding server. Please contact your system administrator for this information.
Our sample code uses "BA37D227-5CB4-4FEA-A633-A24F15A11A7D" as the Client Identifier and "test" as the Client Secret.
Step 4. Add API Library to Project
We offer API Connector libraries that authenticate through an OAuth authentication provider to retrieve a session key and send API request to our API service in order to retrieve a response. These libraries are available in C#, Java, JavaScript, and PHP. You can download these libraries from the API Libraries section.
You will need to add one of these libraries to your project as a first step, depending on your project development platform.
Sample Code
Connecting a Public Application
.NET
var client = new Pagos.SpreadsheetWeb.SpreadsheetWebAPI(string.Empty, string.Empty, false)
{
APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;,
OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;
};
// Create your new request data transfer object.
var request = new CalculationRequest
{
ApplicationKey = "f3597d20cb174c1181d432819ecde698",
Save = false,
Inputs = new Dictionary<string, List<string>;>;
{
{"I_EffectiveDate", new List<string>; {"01-20-2015"}},
{"I_Zip", new List<string>; {"90000"}},
{"I_SIC", new List<string>; {"3550"}},
{"I_Eligible", new List<string>; {"50"}}
},
Outputs = new List<string>;
{
"Plan1_Single",
"Plan1_Couple",
"Plan1_Parent",
"Plan1_Family"
}
};
// Call the server to run your calculation request.
var result = client.GetResult(request);
// If successful, using the Newtonsoft JSON library to deserialize the results.
if (result.IsSuccessful())
{
var outputRanges =
Newtonsoft.Json.JsonConvert.DeserializeObject<OutputResults>;(
Newtonsoft.Json.JsonConvert.DeserializeObject<string>;(result.Results));
}
JAVA
SpreadsheetWebAPI api = new SpreadsheetWebAPI(false);
api.APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;;
api.OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;;
Map<String, ArrayList<String>;>; inputList = new HashMap<String, ArrayList<String>;>;();
// Mapping a single input (to a single cell in the spreadsheet).
ArrayList<String>; effectiveDate = new ArrayList<String>;();
effectiveDate.add("1/1/2012");
inputList.put("I_EffectiveDate", effectiveDate);
// Mapping to a ranged input (a named range that constitutes multiple cells in the spreadsheet).
ArrayList<String>; rangeInput = new ArrayList<String>;();
rangeInput.add("90000");
rangeInput.add("3550");
rangeInput.add("65");
inputList.put("InputList", rangeInput);
// Requested outputs.
ArrayList<String>; outputList = new ArrayList<String>;();
outputList.add("Plan1_Single");
outputList.add("Plan1_Couple");
outputList.add("Plan1_Parent");
outputList.add("Plan1_Family");
SpreadsheetWebResults results = api.GetResult("f3597d20cb174c1181d432819ecde698", // Application Key
"", // Requested record ID
"ssweb", // User Name
false, // Whether or not to save the record
"", // Session ID
inputList, // Map of inputs
outputList); // List of requested outputs
System.out.println("Is Successful:" + results.isSuccessful().toString());
System.out.println("Status Code:" + results.getHttpStatusCode().toString());
System.out.println("JSON Results:" + results.getResults());
PHP
$api = new SswebAPI(false);
$api->;OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;;
$api->;APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;;
$ApplicationKey = 'f3597d20cb174c1181d432819ecde698';
$RecordId = null;
$UserName = "ssweb";
$SessionId = '';
$Save = false;
$Inputs = array("I_EffectiveDate"=>;array("01-20-2015"),"I_Zip"=>;array("90000"), "I_SIC" =>; array("3550"), "I_Eligible" =>;array("50"));
$Outputs = array("Plan1_Single", "Plan1_Couple", "Plan1_Parent", "Plan1_Family");
echo $result = $api->;GetResult($ApplicationKey, $RecordId, $UserName, $SessionId, $Save, $Inputs, $Outputs);
JavaScript
/* Set up the API endpoints. */
APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;;
OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;;
/* Set up the application key. */
var applicationKey = "f3597d20cb174c1181d432819ecde698";
/* Set up the requested inputs. */
var inputs = {
I_Name: ["ssweb"],
I_EffectiveDate: ["01/01/2012"],
I_Zip: ["10001"],
I_SIC: ["0123"],
I_Eligible: ["3"]
};
/* Set up the requested outputs. */
var outputs = [
"Plan1_Single",
"Plan1_Couple",
"Plan1_Parent",
"Plan1_Family"
];
/* Fire the request. Use the callback to handle the response. */
GetResult(
applicationKey, "ssweb", false, inputs,
outputs, -1, null, function (response) {
/* Deserialize the response. */
var deserializedResponse = JSON.parse(response);
/* For each output range, write the name of the requested
* range, along with the value array. */
$.each(deserializedResponse.OutputRanges, function(index, value){
var valueContents = "";
$.each(value.Value, function(i, v){ valueContents += v + ", "; });
console.log(value.Name + " = " + valueContents)
})
});
Connecting a Private Application
.NET
var client = new Pagos.SpreadsheetWeb.SpreadsheetWebAPI( "BA37D227-5CB4-4FEA-A633-A24F15A11A7D", "test", false) { APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;, OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>; }; // Create your new request data transfer object. var request = new CalculationRequest { ApplicationKey = "f3597d20cb174c1181d432819ecde698", Save = false, Inputs = new Dictionary<string, List<string>;>; { {"I_EffectiveDate", new List<string>; {"01-20-2015"}}, {"I_Zip", new List<string>; {"90000"}}, {"I_SIC", new List<string>; {"3550"}}, {"I_Eligible", new List<string>; {"50"}} }, Outputs = new List<string>; { "Plan1_Single", "Plan1_Couple", "Plan1_Parent", "Plan1_Family" } }; // Call the server to run your calculation request. var result = client.GetResult(request); // If successful, using the Newtonsoft JSON library to deserialize the results. if (result.IsSuccessful()) { var outputRanges = Newtonsoft.Json.JsonConvert.DeserializeObject<OutputResults>;( Newtonsoft.Json.JsonConvert.DeserializeObject<string>;(result.Results)); }
JAVA
SpreadsheetWebAPI api = new SpreadsheetWebAPI("BA37D227-5CB4-4FEA-A633-A24F15A11A7D", "test", false); api.APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;; api.OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;; Map<String, ArrayList<String>;>; inputList = new HashMap<String, ArrayList<String>;>;(); // Mapping a single input (to a single cell in the spreadsheet). ArrayList<String>; effectiveDate = new ArrayList<String>;(); effectiveDate.add("1/1/2012"); inputList.put("I_EffectiveDate", effectiveDate); // Mapping to a ranged input (a named range that constitutes multiple cells in the spreadsheet). ArrayList<String>; rangeInput = new ArrayList<String>;(); rangeInput.add("90000"); rangeInput.add("3550"); rangeInput.add("65"); inputList.put("InputList", rangeInput); // Requested outputs. ArrayList<String>; outputList = new ArrayList<String>;(); outputList.add("Plan1_Single"); outputList.add("Plan1_Couple"); outputList.add("Plan1_Parent"); outputList.add("Plan1_Family"); SpreadsheetWebResults results = api.GetResult("f3597d20cb174c1181d432819ecde698", // Application Key "", // Requested record ID "ssweb", // User Name false, // Whether or not to save the record "", // Session ID inputList, // Map of inputs outputList); // List of requested outputs System.out.println("Is Successful:" + results.isSuccessful().toString()); System.out.println("Status Code:" + results.getHttpStatusCode().toString()); System.out.println("JSON Results:" + results.getResults());
PHP
$api = new SswebAPI("BA37D227-5CB4-4FEA-A633-A24F15A11A7D", "test", true);
$api->;OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;;
$api->;APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;;
$ApplicationKey = 'f3597d20cb174c1181d432819ecde698';
$RecordId = null;
$UserName = "ssweb";
$SessionId = '';
$Save = false;
$Inputs = array("I_EffectiveDate"=>;array("01-20-2015"),"I_Zip"=>;array("90000"), "I_SIC" =>; array("3550"), "I_Eligible" =>;array("50"));
$Outputs = array("Plan1_Single", "Plan1_Couple", "Plan1_Parent", "Plan1_Family");
echo $result = $api->;GetResult($ApplicationKey, $RecordId, $UserName, $SessionId, $Save, $Inputs, $Outputs);
JavaScript
/* Set up the API endpoints. */
APIServiceURL = "<a href="http://dev7.pagos.com/Api/JSONService%22">;http://dev7.pagos.com/Api/JSONService"</a>;;
OAuthServiceURL = "<a href="http://dev7.pagos.com/Auth/OAuthIssuer%22">;http://dev7.pagos.com/Auth/OAuthIssuer"</a>;;
/* Set up the security keys. */
ClientIdentifier = "BA37D227-5CB4-4FEA-A633-A24F15A11A7D";
ClientSecret = "test";
/* Set up the application key. */
var applicationKey = "f3597d20cb174c1181d432819ecde698";
/* Set up the requested inputs. */
var inputs = {
I_Name: ["ssweb"],
I_EffectiveDate: ["01/01/2012"],
I_Zip: ["10001"],
I_SIC: ["0123"],
I_Eligible: ["3"]
};
/* Set up the requested outputs. */
var outputs = [
"Plan1_Single",
"Plan1_Couple",
"Plan1_Parent",
"Plan1_Family"
];
/* Fire the request. Use the callback to handle the response. */
GetResult(
applicationKey, "ssweb", false, inputs,
outputs, -1, null, function (response) {
/* Deserialize the response. */
var deserializedResponse = JSON.parse(response);
/* For each output range, write the name of the requested
* range, along with the value array. */
$.each(deserializedResponse.OutputRanges, function(index, value){
var valueContents = "";
$.each(value.Value, function(i, v){ valueContents += v + ", "; });
console.log(value.Name + " = " + valueContents)
})
});