As platform administrators we know that there are many ways to import an Excel file to ServiceNow. But what if you need to provide your client a way to load data without accessing one of these tools?

When we talk about importing data to ServiceNow, the SheetJS library can be extremely useful in helping us with this process (manipulating spreadsheets in the browser). Developers can create custom integrations to transfer data between ServiceNow and other applications. For example, a developer might build a custom integration that allows data to be imported from an external Excel spreadsheet into ServiceNow.

SheetJS: https://git.sheetjs.com/sheetjs/sheetjs

Business Use Case

In this article, we will use as an example an outsourced company that needs to import the hours worked by its employees to the platform so that these hours can be processed and paid. All records must be associated with a department. All necessary validations will be described in a future article. At this point we will only talk about importing the data.

The person in charge of the company will have to access the portal and upload an Excel file with the employees hours following the following model:

The Solution

The first thing we need is to import the standalone version of SheetJS into ServiceNow. Open the link to the xlsx.full.min.js file and copy all the code. After that, we will create a UI Script to import this file into the platform:

The next step is to associate this script with the portal. To do this, open the theme used, go to the ‘JS Includes’ related list and click on ‘New’. In the ‘UI Script’ field, select the script created in the previous step and click ‘Submit.’

Now, let’s start developing the widget that will import the data. We will start by creating the form where the file will be uploaded. Using Bootstrap’s grid system and some CSS changes (which we won’t cover in this article) we have the following result:

Widget

Widget Component: HTML Template

Script:

This is the code preview:

Now let’s insert the fields. The final HTML should look like this:

Widget

Widget Component: HTML Template

Script:

Regarding HTML, I will leave two points for the next articles:

• The field Department will be created using the snRecordPicker directive:

Now we have the form with all its components:

Now that we have our HTML built, let’s move on to the Client Script and create the functions that will be used. This will be the most important part of the article because it is here that we will read the file. Once the file has been selected using the button on the form, we need a script to read, process, and insert the data into the table (for this article we will not perform any type of validation on the data).

Widget

Widget Component: Client Script

Script:

api.controller=function($scope, spUtil) {

var c = this;

c.model = {};

c.hoursToSubmit = [];

/* 
    read the file
    the function get the attached file as a blob
    https://developer.mozilla.org/en-US/docs/Web/API/Blob
*/
$scope.readAttachment = function(blob) {

    //check if the file is XLS or XLSX
    var isXLSX = blob.name.endsWith('.xlsx') || blob.name.endsWith('.xls');

    // If not an Excel file the function fails
    if (!isXLSX) {
        spUtil.addErrorMessage("The file must be .xlxs or .xls");
        //c.removeAttachment();
        return;
    }

    c.fileName = blob.name;

    /* star the reader */
    var myReader = new FileReader();

    /* function that will be executed when the reader is called */
    myReader.onload = function(e) {

        var data = e.target.result;

        /* get the workbook */
        var workbook = XLSX.read(data, {
            type: "binary"
        });
        var o = {};

        /* get the first sheet name */
        var name = workbook.SheetNames[0];

        /* obtain the JSON object of the sheet and stringify */
        var work_hours = XLSX.utils.sheet_to_json(workbook.Sheets[name], {header: "A"});

        /* remove the first line (columns titles) using Lodash*/
        work_hours = _.drop(work_hours, 1);

        if (work_hours.length == 0) {

            spUtil.addErrorMessage("Nothing to import!");
            //c.removeAttachment();
            return;

        } else {

            /* For this article we will not do any type of validation */
            c.hoursToSubmit = work_hours;
            return;

        }

    }

    /* stars the reader */
    myReader.readAsBinaryString(blob);
}

c.submitFile = function() {

    $scope.server.get({
        action: "insert-hours",
        monthYear: c.model.monthYear,
        department: c.model.department,
        hoursToSubmit: c.hoursToSubmit
    }).then(function(resp) {

        spUtil.addInfoMessage("Sucess! Good job!! :)");
        c.hoursToSubmit = [];
        c.model = {};
        //c.removeAttachment();

    });

}

};

Widget

Widget Component: Server Script

Script:

(function() {

if(input && input.action == 'insert-hours') {

    var grWH = new GlideRecord('x_529701_snguru_worked_hours');

    for (var i = 0; i < input.hoursToSubmit.length ; i++) {

        grWH.initialize();          
        grWH.setValue('id', input.hoursToSubmit[i]['A'].toString());
        grWH.setValue('name', input.hoursToSubmit[i]['B']);
        grWH.setValue('u_departament', input.department.value);         
        grWH.setValue('period', input.monthYear.displayValue);
        grWH.setValue('u_type', input.hoursToSubmit[i]['C']);
        grWH.setValue('hours', parseInt(input.hoursToSubmit[i]['D']));
        grWH.insert();

    }

}

})();

Now our widget is ready to make the magic happen! Fill in the fields, select the file, and click ‘Submit’.

If everything goes well, the data will be written to the table:

Conclusion

By leveraging the SheetJS library and custom ServiceNow widgets, we have created a streamlined solution for importing employee work hours from Excel files directly into ServiceNow. This approach eliminates the need for users to access external tools, simplifying the process and enhancing user experience. Such integrations not only save time, but also reduce errors associated with manual data entry, ensuring that the data processing is efficient and accurate.