Modules

Modules are pieces of javascript that are stored outside of the workbook, allowing them to be re-used. This allows code that is used often to be written once and imported into workbooks.

You can use the modules that are provided by XLConnect, or you can write and import your own.

Using XLConnect modules

XLConnect comes with a set of modules that encapsulate logic to deal with things like http and the Data Lake, and provide logic makes it easier to work with cloud platforms like Xero and Visma. Here's how you can add this to your code:

1. Open the Javascript Studio

2. In the toolbar, drop down the Insert Module button, then select the http.js module.

   

   How to insert the http.js module

Afterwards, your Javascript Studio should look like this:

Javascript Studio after inserting the http.js module

Notice how a new line of code was added in the Code Pane

http = require('http.js')

and a new object named http with 5 functions appeared on the right.

When you use the toolbar to insert a script, it will automatically insert the module definition at the top of the script, execute that line and expand the object that was just created. You can also manually add the line, highlight it, press F8 and expand the new object, we thought we'd save you some clicks as you'll be using this a lot.

1. Now drag the http function from the Engine Pane onto the code, now your code will look like this:

http = require('http.js')
http.get(uri, hds = null, auth = null)

The http funcion has three parameters, weher the last two have defaults.

1. We now need to create a variable uri, as the other two parameters have default values (tip you can copy from here if you hover over it, then paste it into the Code Pane):

http = require('http.js')
uri = 'https://data.meteoserver.nl/api/uurverwachting_gfs.php?locatie=Amsterdam&key=218a949186'
dat = http.get(uri)

1. When we execute this code with F5, it will download the weather forecast for Amsterdam for the next 10 days:

The result of running the code above

1. Now double-click the get function to zoom in.

Elements in the Engine pane can be zoomed in by double-clicking. When you zoom in on a function, it will show you the code. This can be helpful to understand what parameters it expects from you and what the code does.

Zooming in on the get function to see the code

HTTP Module for making HTTP Calls

The http module can be added with:

http = require('http.js')

File Module for working with JSON data

file = require('file.js') 

Using external modules

The javascript world has a rich ecosystem of libraries that you can leverage to manipulate data, here are a few suggestions to get you started:

Lodash

Lodash is a versatile JavaScript utility library that offers a rich set of functional programming helpers for handling arrays, objects, and strings without mutating the original data. It simplifies complex operations, enhancing code readability and maintainability.

To import Lodash into your code (please note the version number)

_ = require('https://raw.githubusercontent.com/lodash/lodash/4.17.10-npm/lodash.js')

AlaSql

AlaSQL is an open-source SQL database for JavaScript that allows for fast in-memory operations and can work with various data sources, including arrays of objects, Excel, and other databases. It offers a rich SQL-syntax for data manipulation, enabling tasks like querying, joining, and exporting data in a manner familiar to SQL developers.

alaSql = require('https://unpkg.com/alasql@4.1.2/dist/alasql.min.js')

While Lodash and Ramda are utility libraries focused on providing functional programming tools for data manipulation in JavaScript, AlaSQL is centered around SQL-like operations, allowing users to interact with data using a syntax closely mirroring traditional SQL. While Lodash and Ramda excel at data transformation using chained operations and functional paradigms, AlaSQL shines in querying, aggregating, and joining data sets in a manner reminiscent of relational databases.

Ramda

Ramda is a functional programming library for JavaScript that emphasizes pure functions, auto-currying, and immutability, making data transformations concise and predictable. It provides a suite of utilities designed for point-free coding, ensuring functions remain unmodified by side-effects.

R = require('https://raw.githubusercontent.com/ramda/ramda/master/dist/ramda.js')

In contrast to Lodash, Ramda places a stronger emphasis on immutability and function composition through auto-currying. While both libraries provide utility functions for data manipulation, Lodash leans more towards general utility, whereas Ramda is firmly rooted in functional programming principles.

These are just a few examples to get you started, the general way of importing external modules is through either raw.githubusercontent.com or unpkg.com, that will allow you to grab the source in a single file for the CommonJS module system XLConnect use today. We are working on supporting ES Modules as well, please let us know if this is important to you.

Developing your own modules