Load Excel files to ServiceNow using Portal

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:

 

exemple table

 

 

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:

 

UI Script

 

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.’

 

JS Include

 

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:

<form ng-submit="c.submitFile()" name="form" id="my-form">
  <fieldset class="form-row">
    <div class="form-group col-md-3">
      <!-- Field Period -->
      <label class="control-label" for="monthYear">Month/Year</label>
    </div>
    <div class="form-group col-md-3">
      <!-- Field Department -->
      <label for="department">Department</label>
    </div>
    <div class="form-group col-md-3">
      <!-- Input file -->
      <label for="department">Attach File</label>
    </div>
    <div class="form-group col-md-3">
      <!-- Buttom submit -->
      <button
        type="submit"
        id="submit"
        class="btn submit-btn btn-block btn-primary"
      >
        Submit
      </button>
    </div>
  </fieldset>
</form>

This is the code preview:

 

 

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

Widget
Widget Component: HTML Template
Script:

<form ng-submit="c.submitFile()" name="form" id="my-form">

  <fieldset class="form-row">

    <div class="form-group col-md-3">
      
      <!-- Field Period -->
      <label class="control-label" for="monthYear">Month/Year</label>
      <input id="monthYear" name="monthYear" placeholder="MM/YYYY" ng-model="c.model.monthYear.displayValue"
             class="form-control" readonly>

    </div>
    
    <div class="form-group col-md-3">
      
      <!-- Field Department -->
      <label for="department">Department</label>
      <sn-record-picker name="department"
                        field="c.model.department"
                        table="c.recordPicker.table"
                        default-query="c.recordPicker.query"
                        display-field="c.recordPicker.display.field"
                        display-fields="c.recordPicker.display.fields"
                        value-field="c.recordPicker.value"
                        search-fields="c.recordPicker.search"
                        page-size="c.recordPicker.size"
                        >
      </sn-record-picker>
      
    </div>
    
    <div class="form-group col-md-3">
      
      <!-- Input file -->
      <label for="attachment" class="btn btn-block btn-primary" ng-hide="c.haveAttachment">
        <span class="glyphicon glyphicon-paperclip"></span> Attach File
      </label>
      
      <div ng-show="c.haveAttachment" class="file-attached">
        {{c.fileName}} <a id="clear" href="" ng-click="c.removeAttachment()">
        <span class="glyphicon glyphicon-remove"></span></a>
      </div>
      
      <input type="file" name="attachment" id="attachment" accept=".xls,.xlsx" 
             onchange="angular.element(this).scope().readAttachment(angular.element(this)[0].files[0])"  
             >
    </div>
    
    <div class="form-group col-md-3">
      
      <!-- Buttom submit -->
      <button type="submit" id="submit" class="btn submit-btn btn-block btn-primary">Submit</button>
      
    </div>
    
  </fieldset>
  
</form>

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.

Date Posted:

May 28, 2024

Share This:

4 Comments

  1. Ankush May 29, 2024 at 2:20 pm - Reply

    Why can’t we create a data source and via recordproducer let user upload file using OOB attachment option ?
    Unless I am missing something

    • Thiago Pereira May 31, 2024 at 12:11 am - Reply

      Hi Ankush!
      There are several ways to do it but we should consider the following:
      Loading large files and attachments: One place where we commonly run into performance troubles is loading large files and attachments. In general, these may take a littlelonger to load. Além do fato de sobrecarregar a sys_attachment.
      Reduced Server Load: Distributing some processing to the client side can reduce the load on the server, especially for applications with a large number of concurrent users.
      Validation Feedback: Real-time validation and feedback to users can be implemented more effectively on the client side, providing a better user experience.

      In the example in the article, the Excel file is very simple. But imagine if you need to upload a file with dozens of columns and a lot of validations to be done on the data?

  2. Ashish May 29, 2024 at 4:49 pm - Reply

    why not use the OOB excel parser and avoid all this?

    • Thiago Pereira May 31, 2024 at 12:18 am - Reply

      As I said in the previous answer there are several ways to do it and reduced server load It’s something we should always keep in mind. As GlideExcelParser runs on the server, all necessary validation would be done there, which I don’t see as a good practice.

Leave A Comment

Categories

Tags

Loading

Related Posts

Fresh Content
Direct to Your Inbox

Just add your email and hit subscribe to stay informed.