Custom Application Tutorial

What is a Custom Application?

Custom application feature allows user to create unique user interfaces (UI) by using the flexibility of HTML and CSS combined with power of JavaScript programming language.

How to create Custom UI applications

Custom applications requires 2 mandatory files which are XLSX and HTML, and 1 optional ZIP archive file which includes additional contents like JavaScript, CSS, image, etc. files. Although you can embed all script and style code into HTML file; this approach is not recommended due to increase complexity of file. So we suggest to use separate files for script and style; also follow this suggestion through this tutorial.

Summary of files:

Excel (XLSX)
HTML
ZIP Package
1. Script (JS)
2. Style (CSS)
3. Image (PNG, JPEG, BMP, etc.)
4. Etc.

Preparing Files

Excel (xlsx) file

Only process for your Excel is defining names for your inputs and outputs if have not given yet. For example let’s say we have basic sum calculation file with 2 inputs and 1 output.

We can’t use simply C2, C4 and C6 cell references for respectively Input 1, Input 2 and Output cells. Instead we should name them by either using Defined Names section in Excel’s Ribbon > FORMULAS tab:

or typing name into Reference Box at left side of formula bar:

After naming C2, C4 and C6 cells as respectively input1, input2 and output. We can go through next step.

Note: We assumed that Excel file is fully compatible with PSC. Please refer Pagos’ Wiki pages to see current restrictions and known issues.[1]

Content Files

Content files are external files to keep our main html file light and other non-text file types which cannot be embed into an html file.

There are two important items for preparing content files:

All files should be in a parent folder named Content
Afore-mentioned Content folder should be zipped (archived in a ZIP file format)

Here is a screenshot how content folder can be looked like. While parent folder name should be Content, inner folder and file names are totally up to you. We chose to put different file types into different folders.

Most important content file is ssweb_api.js which includes code you need to connect your Excel file through API. You can download file from this link.[2]

Note: While ssweb_api.js preparing jQuery[3] library was used to ease the coding. Please do not forget to add jQuery script references into your html file.

You can freely modified this file after downloading it. Though; most of cases there are only 2 variables you need to check before continue:

APIServiceURL
OAuthServiceURL

This variables should be set according to your server path and opened ports for SpreadsheetWEB API. Default ports are 2150 for APIServiceURL and 1961 for OAuthServiceURL.

Tip: You can use window.location.origin syntax which returns protocol and hostname which represents your server path. For example;

var APIServiceURL = window.location.origin + ":2150/JSONService";

After setting service variables; please pay attention to value variable and GetResult function. GetResult is the main function which provides the communication between application and Excel file. It sends inputs to engine where Excel file is processed, get outputs from the engine and set them into value variable.

Let’s look at the parameters and return type of GetResult function:

Name	Type	Description
ClientIdentifier	String	API Key obtained for SpreadsheetWEB Control Panel. Used if the application is private.
ClientSecret	String	API Password obtained for SpreadsheetWEB Control Panel. Used if the application is private.
ApplicationKey	String	Application Key of the calculation engine.
inputList	Array	Inputs sent to calculation engine for calculations.
outputList	Array	Outputs to be received after calculations are done.
save	Bool	Whether to save the result to database or not.
username	String	Username who saves the result.
callback	Function	A callback function to run after getting outputs

Return:

Name

Type

Content

value

Object

OutputRanges:[{

Name: String

Value: Array (String)

}

]

Message: String

Entity: Object

Please refer wiki for detailed information.[4]

Html File

Next step is creating a web page and writing codes for using API. Assuming we have 2 inputs named input1 and input2 as well as a span element named output to write output into it:

<input type="text" id="input1" value="15">

<input type="text" id="input2" value="20">

<span id="output"></span>

Let’s start to write script.

First step is defining and setting parameters for GetResult:

var ClientIdentifier = "";

var ClientSecret = "";

var ApplicationKey = $("#hdApplicationKey").val();

var save = false;

var username = $("#hdUsername").val();

As you can see we get ApplicationKey and username values from html elements we have not mentioned yet. These elements are automatically generated by engine and put into html code as hidden inputs. By this approach user does not need to set this parameters for each application.

Tip: There is another hidden element to get application ID which is

var ApplicationId = $("#hdApplicationID").val();

Next step is inputs and outputs.

Outputs or outputList (parameter name) is an array of string which should contain output names set in Excel file. As you can remember; we assign 3 names to cells which are input1, input2 and output. Put any of these names in array to get as output. We put output name naturally; but if you want to get a value from input cell, you can put its name into array as well.

var outputs = [

"output"

];

Multi output example:

var outputs = [

"output",

“output2”,

“output3”

];

Inputs array contains Excel names as well as outputs array additional to input values. Input names and input values should be separated by semicolon(;).

var inputs = [

"input1;" + $("#input1").val(),

"input2;" + $("#input2").val()

];

What if we named a range instead of single cell like below?

input11	input21	input31
input12	input22	input32
input13	input23	input33

We can use this syntax:

var inputs = [

"input1;" +

$("#input11").val() + ":" + $("#input12").val() + ":" + $("#input13").val() + ":" +

$("#input21").val() + ":" + $("#input22").val() + ":" + $("#input23").val() + ":" +

$("#input31").val() + ":" + $("#input32").val() + ":" + $("#input33").val()

];

Input values are separated from each other by a colon (:) character.

Last step is the defining a callback function to process output value. As mentioned; return value is set into global variable object. We simply set return value to our span element named output:

$("#output").html(value.OutputRanges[0].Value[0][0])

Final code is like this:

<script>

var ClientIdentifier = "";

var ClientSecret = "";

var username = $("#hdUsername").val();

var ApplicationKey = $("#hdApplicationKey").val();

var ApplicationId = $("#hdApplicationID").val();

var outputs = [

"output"

];

// Send inputs and get results, results are stored in value variable

function Calculate () {

var inputs = [

"input1;" + $("#input1").val(),

"input2;" + $("#input2").val()

];

var x = GetResult(

ClientIdentifier,

ClientSecret,

ApplicationKey,

inputs,

outputs,

save,

username,

function () {

$("#output").html(value.OutputRanges[0].Value[0][0])

}

);

}

$(document).ready(function () {

$("input").on("change", function () {

//What happens when an input is changed

Calculate();

});

});

</script>

Uploading

Last step to create our Custom Application is uploading files into SpreadsheetWeb Server.

This process is very similar to add regular application. Login your account as usual and click Create a new Application button in the Applications tab.

Select Custom Web Page option an click Next button.

There are 3 file attachment inputs for afore-mentioned 3 files of our application.

Excel Spreadsheet File
HTML File
ZIP File which includes Content folder

Name your application, click Choose File button to select your files and lastly do not forget to select a group for your application. Then click Save.