Highcharts is a flexible, feature-rich JavaScript library that allows developers to create interactive charts for the web. By integrating Highcharts with ServiceNow, you can transform static data into engaging visualizations, providing your users with a more intuitive, interactive experience. In this article, I’ll walk you through the process of integrating Highcharts with ServiceNow, showcasing use cases, and tips to get the most out of this powerful combination.

Why Integrate Highcharts with ServiceNow?

ServiceNow provides basic reporting and visualization features, but for more advanced charting needs—such as interactive dashboards or detailed drill-down capabilities—Highcharts offers several advantages:

• Customization: Highcharts offers a wide range of chart types (line, bar, pie, scatter, etc.), and the ability to customize almost every aspect of the chart.
• Interactivity: With Highcharts, users can interact with charts by zooming, clicking data points, and receiving real-time feedback, making data exploration more engaging.
• Real-time Data: You can easily configure charts to update in real-time, providing up-to-the-minute insights.
• Extensibility: Highcharts offers numerous plugins and extensions, allowing you to extend functionality as needed.

By combining ServiceNow’s robust data management with Highcharts’ visualization capabilities, organizations can create more effective reports and dashboards that drive better decision-making.

Licensing:

For this article, which has educational purposes, I will download version 11.4.3 of the library and use it in my PDI. For commercial use, it is recommended that you purchase licenses directly through their website. Another option is to use the version available within the platform, which in my research (<instance-name>/scripts/glide-highcharts.js) is 9.3.2.

Using the paid version:

In my article “Importing Excel to Servinow” I showed how to import a library into SN. Just follow the same steps to import the Highcharts library.

Using the version available on the platform:

Just create a JS Include in the Portal Theme added to the library already available on the platform:

Basic usage example:

For this first example I will create a simple widget with hard-coded data just to show how to use the library. To make things easier I will use a basic example that can be found in the documentation. This example was made using the version of the library that already comes with the platform.

<div class="page-header">
  <h1>ServiceNow Guru - Basic Highchart Example</h1>
</div>

<div id="container" style="width:100%; height:400px;"></div>

var chartConfirObj = {
  chart: {
    type: 'bar',
  },
  title: {
    text: 'Fruit Consumption',
  },
  xAxis: {
    categories: ['Apples', 'Bananas', 'Oranges'],
  },
  yAxis: {
    title: {
      text: 'Fruit eaten',
    },
  },
  series: [
    {
      name: 'Jane',
      data: [1, 0, 4],
    },
    {
      name: 'John',
      data: [5, 7, 3],
    },
  ],
};

var chart = Highcharts.chart('container', chartConfirObj);

In this example, we see that to build the chart we need to pass an object containing the chart settings we want. The library is huge and I could spend hours here talking about each item in this object and also about each customization possibility, but the library documentation is incredibly complete and can answer all questions:

• API Docs
• API Reference

Now let’s give our graph a bit more reality and bring real data to it.

//variables that will be used to customize the graph
data.font_family = options.font_family || 'SourceSansPro,Helvetica,Arial,sans-serif';
data.fill_color = options.fill_color || '#fff';

data.title = options.title || 'Chart Title';
data.title_color = options.title_color || '#fff';
data.title_size = options.title_size || '14px';
data.title_weight = options.title_weight || 'normal';

data.label_color = options.label_color || '#fff';
data.label_size = options.label_size || '12px';
data.label_weight = options.label_weight || 'normal';
data.label_connector_color = options.label_connector_color || '#fff';

data.slice_label_size = options.slice_label_size || '14px';
data.slice_label_outline = options.slice_label_outline || 'transparent';
data.slice_label_opacity = options.slice_label_opacity || 1;
data.slice_label_weight = options.bar_label_weight || 'bold';

data.table = options.table || 'change_request';
data.agg_field = options.agg_field || 'risk';

data.graphData = [];

//get total requests
var reqCount = new global.GlideQuery(data.table).count();

// get the total requests by risk
var agg = new GlideAggregate(data.table);
agg.addAggregate('COUNT', data.agg_field);
agg.orderBy(data.agg_field);
agg.query();
while (agg.next()) {

    var count = parseInt(agg.getAggregate('COUNT', data.agg_field));
    var percentTemp = 100 * count / reqCount;
    var percent = Math.round(percentTemp * 100) / 100;

    var objReq = {
        name: agg.getDisplayValue(data.agg_field),
        y: count,
        percent: percent.toFixed(2)
    };

    // if the risk is Very High the pie should be sliced and red
    if (agg.getValue(data.agg_field) == 1) {
        objReq.sliced = true;
        objReq.selected = true;
        objReq.color = 'red';
    }

    data.graphData.push(objReq);

}

var chartConfirObj = {
  chart: {
    type: "pie",
    backgroundColor: c.data.fill_color,
  },
  title: {
    text: c.data.title,
    style: {
      fontFamily: c.data.font_family,
      color: c.data.title_color,
      fontSize: c.data.title_size,
      fontWeight: c.data.title_weight,
    },
  },
  tooltip: {
    formatter: function () {
      return (
        "<b>" +
        this.point.name +
        " </b>: " +
        this.y +
        " </b> (" +
        this.point.percent +
        "%)"
      );
    },
  },
  plotOptions: {
    series: {
      allowPointSelect: true,
      cursor: "pointer",
      dataLabels: [
        {
          enabled: true,
          distance: 20,
          connectorColor: c.data.label_connector_color,
          formatter: function () {
            return this.point.name + " </br>" + this.point.percent + "%";
          },
          style: {
            fontFamily: c.data.font_family,
            color: c.data.label_color,
            fontSize: c.data.label_size,
            fontWeight: c.data.label_weight,
            textOutline: c.data.bar_label_outline,
          },
        },
        {
          enabled: true,
          distance: -30,
          format: "{y}",
          style: {
            textOutline: c.data.slice_label_outline,
            fontSize: c.data.slice_label_size,
            opacity: c.data.slice_label_opacity,
            fontWeight: c.data.slice_label_weight,
            textAling: "center",
          },
        },
      ],
    },
  },
  series: [
    {
      name: "Percentage",
      colorByPoint: true,
      data: c.data.graphData,
      dataLabels: {
        style: {
          color: c.data.bar_label_color,
          fontSize: c.data.bar_label_size,
          textOutline: c.data.bar_label_outline,
          fontWeight: c.data.bar_label_weight,
        },
      },
    },
  ],
};

var chart = Highcharts.chart("containerPie", chartConfirObj);

<div id="containerPie"></div>

[
    {
        "name": "table",
        "section": "other",
        "default_value": "change_request",
        "label": "Table",
        "type": "string"
    },
    {
        "name": "agg_field",
        "section": "other",
        "default_value": "risk",
        "label": "Field used to aggregate",
        "type": "string"
    },
    {
        "name": "font_family",
        "section": "other",
        "default_value": "SourceSansPro,Helvetica,Arial,sans-serif",
        "label": "Font family",
        "type": "string"
    },
    {
        "name": "fill_color",
        "section": "other",
        "default_value": "#fff",
        "label": "Fill color",
        "type": "string"
    },
    {
        "name": "title",
        "section": "other",
        "default_value": "Chart title",
        "label": "Title",
        "type": "string"
    },
    {
        "name": "title_color",
        "section": "other",
        "default_value": "#fff",
        "label": "Title color",
        "type": "string"
    },
    {
        "name": "title_size",
        "section": "other",
        "default_value": "14px",
        "label": "Title Size",
        "type": "string"
    },
    {
        "name": "title_weight",
        "section": "other",
        "default_value": "normal",
        "label": "Title weight",
        "type": "string"
    },
    {
        "name": "label_color",
        "section": "other",
        "default_value": "#fff",
        "label": "Label Color",
        "type": "string"
    },
    {
        "name": "label_size",
        "section": "other",
        "default_value": "11px",
        "label": "Label size",
        "type": "string"
    },
    {
        "name": "label_weight",
        "section": "other",
        "default_value": "normal",
        "label": "Label weight",
        "type": "string"
    },
    {
        "name": "label_connector_color",
        "section": "other",
        "default_value": "#fff",
        "label": "Label connector color",
        "type": "string"
    }
]

Now, with everything we have shown in this article, we have the possibility of building complex dashboards like the one we saw at the beginning of this article.

Conclusion

Integrating Highcharts with ServiceNow allows you to take your data visualization to the next level, enabling more interactive, detailed, and dynamic reporting. With its wide range of customization options and ability to handle real-time data, Highcharts is an ideal tool for organizations looking to enhance their ServiceNow dashboards and reports. Whether you’re visualizing incident trends, service performance, or asset utilization, this integration can provide critical insights to drive better decision-making.

By following the steps in this article, you’ll be well on your way to creating powerful, interactive charts in ServiceNow, enabling users to explore and understand complex data sets with ease.

The post Leveraging ServiceNow with Highcharts: Transforming Data into Insight appeared first on ServiceNow Guru.

In this comprehensive guide, we will take you through the fundamentals of GlideQuery, from basic concepts and syntax to advanced techniques and best practices. By the end of this article, you will have a solid grasp of how to leverage GlideQuery to enhance your data handling capabilities in ServiceNow, making your development process more efficient and your applications more responsive.

Let’s dive in and explore the power of GlideQuery, unlocking new potentials in your ServiceNow development journey.

Principles of GlideQuery

Peter Bell, a software engineer at ServiceNow, initially developed these API to use as an external tool for his team. Gradually, the API became an integral part of the platform, specifically integrated into the Paris release.

This API is versatile, compatible with both global and scoped applications. In the latter case, developers need to prefix their API calls with “global” to ensure proper functionality:

var user = new GlideQuery('sys_user')

var user = new global.GlideQuery('sys_user')

The API is entirely written in JavaScript and operates using GlideRecord in a second layer. Instead of replacing GlideRecord, its purpose is to enhance the development experience by minimizing errors and simplifying usage for developers.

I. Fail Fast

Detect errors as quickly as possible, before they become bugs.

Through a new type of error, NiceError, which facilitates the diagnosis of query errors, it is possible to know exactly what is wrong in your code and quickly fix it. Examples:

Field name validation

The API checks if the field names used in the query are valid and returns an error if any of them are not. In some cases, this is extremely important as it can prevent major issues. In the example below, using GlideRecord it would delete ALL records from the user table because the field name is written incorrectly. Note that the line that would delete the records has been commented out for safety and we have added a while loop just to display the number of records that would be deleted.

var gr = new GlideRecord('sys_user');
gr.addQuery('actived', '!=', true);
gr.query();
//gr.deleteMultiple();  commented for safety 
gs.info(gr.getRowCount());
while (gr.next()) {
    gs.info(gr.getDisplayValue('name'));
}

Using GlideQuery the API returns an error and nothing is executed:

var myGQ = new GlideQuery('sys_user')
    .where('activated', '!=', true)
    .del();

Returns:

NiceError: [2024-07-22T12:58:23.681Z]: Unknown field 'activated' in table 'sys_user'. Known fields:
[
  "country",
  "calendar_integration",
  etc.

Choice field validation

The API checks whether the option chosen for the field exists in the list of available options when it is of type choice. In the example below, using GlideRecord, the script would return nothing:

var gr = new GlideRecord('incident');
gr.addQuery('approval', 'donotexist'); //invalid value for the choice field 'approval'
gr.query();
while (gr.next()) {
    gs.info(gr.getDisplayValue());
}

Using GlideQuery, the API returns an error and nothing is executed:

var tasks = new GlideQuery('incident')
    .where('approval', 'donotexist')
    .select('number')
    .forEach(gs.log);

Returns:

NiceError: [2024-07-22T12:57:33.975Z]: Invalid choice 'donotexist' for field 'approval' (table 'incident'). Allowed values:
[
  "not requested",
  "requested",
  "approved",
  "rejected"
]

Value type validation

The API checks whether the type of value chosen for the field is correct.

var tasks = new GlideQuery('incident')
    .where('state', '8') // invalid value for the choice field 'state'
    .select('number')
    .forEach(function (g) {
        gs.info(g.number);
    });

Returns:

NiceError: [2024-07-22T12:56:51.428Z]: Unable to match value '8' with field 'state' in table 'incident'. Expecting type 'integer'

II. Be JavaScript (native JavaScript)

JavaScript objects bringing more familiarity to the way queries are made and reduce the learning curve. With GlideRecord we often have problems with the type of value returned:

var gr = new GlideRecord('sys_user');
gr.addQuery('first_name', 'Abel');
gr.query();

if (gr.next()) {
    gs.info(gr.first_name);
    gs.info(gr.firt_name === 'Abel');
    gs.info(typeof(gr.first_name));
}

Returns:

*** Script: Abel
*** Script: false
*** Script: object

As we can see Abel is different from Abel!
The reason for this confusion is that GlideRecord returns a Java object.

Using GlideQuery we don’t have this problem:

var user = new GlideQuery('sys_user')
    .where('first_name', 'Abel')
    .selectOne('first_name')
    .get(); // this method can throw error if no record is found

gs.info(user.first_name);
gs.info(user.first_name === 'Abel');
gs.info(typeof (user.first_name));

Returns:

*** Script: Abel
*** Script: true
*** Script: string

III. Be Expressive

Do more with less code! Simplify writing your code!

Performance

Using GlideQuery can increase processing time by around 4%, mainly due to the conversion of the Java object to JavaScript. However, keep in mind that we will often do this conversion manually after a GlideRecord.

Stream x Optional

The API works together with 2 other APIs: Stream and Optional. If the query returns a single record, the API returns an Optional object. If it returns several records, the API returns an object of type Stream, which is similar to an array. These objects can be manipulated according to the methods of each API.

Practical Examples

Before we start with the examples, it is necessary to make 2 points clear

• The primary key of the table (in our case normally the sys_id) is always returned even if the request is not made in Select.
• Unlike GlideRecord, GlideQuery does not return all record fields. We need to inform the name of the fields we want to get:
  .selectOne([‘field_1’, ‘field_2’, ‘field_n’])

I. selectOne

It is very common that we only need 1 record and in these cases we use selectOne(). This method returns an object of type Optional and we need a terminal method to process it:

a) get

// Searches for the user and if it does not exist returns an error
var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne('first_name')
    .get(); // this method can throw error if no record is found

gs.info(JSON.stringify(user, null, 4));

If success:

Script: {
    "first_name": "Fred",
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If fail:

NiceError: [2024-07-21T22:28:48.274Z]: get() called on empty Optional: Unable to find a record with the following query:
GlideQuery<sys_user> [
  {
    "type": "where",
    "field": "last_name",
    "operator": "=",
    "value": "Luddyx",
    "whereClause": true
  }
]

b) orElse

Optional method used to handle queries that do not return any value.

var user = new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .orElse({  //Method in the Optional class to return a default value.
        first_name: 'Nobody'
    });

gs.info(JSON.stringify(user, null, 4));

If success:

Script: {
    "first_name": "Fred",
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If fail:

Script: {
    "first_name": "Nobody"
}

c) ifPresent

var userExists = false;
new GlideQuery('sys_user')
    .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .ifPresent(function (user) {
        gs.info(user.first_name + ' - ' + user.sys_id);
        userExists = true;
    });

gs.info(userExists);

If user exists:

*** Script: Fred - 5137153cc611227c000bbd1bd8cd2005
*** Script: true

If not:

*** Script: false

d) isPresent

var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    . selectOne(['first_name'])
    .isPresent();

gs.info(userExists);

If user exists:

*** Script: true

If not:

*** Script: false

e) isEmpty

var userExists = new GlideQuery('sys_user')
   .where('last_name', 'Luddy')
    .selectOne(['first_name'])
    .isEmpty();

gs.info(userExists);

If user exists:

*** Script: false

If not:

*** Script: true

II. get

Returns a single record using sys_id

var user = new GlideQuery('sys_user')
    .get('62826bf03710200044e0bfc8bcbe5df1', ['first_name', 'last_name'])
    .orElse({
        first_name: 'Nobody',
        last_name: 'Nobody'
    });

gs.info(JSON.stringify(user, null, 4));

If user exists:

*** Script: {
    "sys_id": "62826bf03710200044e0bfc8bcbe5df1",
    "first_name": "Abel",
    "last_name": "Tuter"
}

If not:

*** Script: {
    "first_name": "Nobody",
    "last_name": "Nobody"
}

III. getBy

Returns a single record (even if there is more than 1 record) using the keys used as parameters.

var user = new GlideQuery('sys_user')
    .getBy({
        first_name: 'Fred',
        last_name: 'Luddy'
    }, ['city', 'active']) // select first_name, last_name, city, active
    .orElse({
        first_name: 'Nobody',
        last_name: 'Nobody',
        city: 'Nowhere',
        active: false
    });

gs.info(JSON.stringify(user, null, 4));

If user exists:

*** Script: {
    "first_name": "Fred",
    "last_name": "Luddy",
    "city": null,
    "active": true,
    "sys_id": "5137153cc611227c000bbd1bd8cd2005"
}

If not:

*** Script: {
    "first_name": "Nobody",
    "last_name": "Nobody",
    "city": "Nowhere",
    "active": false
}

IV. insert

The insert method needs an object as a parameter where each property must be the name of the field we want to fill. The insert returns an Optional with the data of the inserted object and the sys_id. We can also request extra fields for fields that are automatically populated:

var user = new GlideQuery('sys_user')
    .insert({  
        active: false,
        first_name: 'Thiago',
        last_name: 'Pereira',
    },['name'])
    .get()

gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "active": false,
    "first_name": "Thiago",
    "last_name": "Pereira",
    "name": "Thiago Pereira"
}

V. update

The API has a method for when we want to update just one record. To use this method, the field used in the “where” must be the primary key and this way it updates just 1 record.

var user = new GlideQuery('sys_user')
    .where('sys_id', '40f6e42147efc210cadcb60e316d43be') //sys_id of the record created in the insert example
    .update({ email: 'thiago.pereira@example.com', active: true }, ['name'])
    .get();
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "email": "thiago.pereira@example.com",
    "active": true,
    "name": "Thiago Pereira"
}

VI. updateMultiple

var myQuery = new GlideQuery('sys_user')
    .where('active', false)
    .where('last_name', 'LIKE', 'Pereira') 
    .updateMultiple({ active: false });

gs.info(JSON.stringify(myQuery, null, 4));

If success:

*** Script: {
    "rowCount": 1
}

If fail:

*** Script: {
    "rowCount": 0
}

VII. insertOrUpdate

This method receives an object with the key(s) to perform the search. If one of the keys is a primary key (sys_id), it searches for the record and updates the other fields entered in the object. If no primary key is passed or the sys_id is not found, the method will create a new record. In this method we cannot select fields other than those passed in the object.

// Create a new record even though a user already exists with the values
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        first_name: 'Thiago',
        last_name: 'Pereira'
    })
    .orElse(null);
    
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "5681fca1476fc210cadcb60e316d43b6",
    "first_name": "Thiago",
    "last_name": "Pereira"
}

// Update an existing record
var user = new GlideQuery('sys_user')
 .insertOrUpdate({
 sys_id: '40f6e42147efc210cadcb60e316d43be', //sys_id of the record created in the insert example
 first_name: 'Tiago',
 last_name: 'Pereira'})
 .orElse(null);

gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "40f6e42147efc210cadcb60e316d43be",
    "first_name": "Tiago",
    "last_name": "Pereira"
}

// Creates a new record as the sys_id does not exist
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        sys_id: 'xxxxxxxxxxxxxxxxxxxx',
        first_name: 'Thiago',
        last_name: 'Pereira2'})
    .orElse(null);
gs.info(JSON.stringify(user, null, 4));

Returns:

*** Script: {
    "sys_id": "50e338ed47afc210cadcb60e316d4364",
    "first_name": "Thiago",
    "last_name": "Pereira2"
}

VIII. deleteMultiple / del

There is no method to delete just one record. To do this we need to use deleteMultiple together with where() using a primary key. The method does not return any value.

var user = new  GlideQuery('sys_user')
    .where('last_name', "CONTAINS", 'Pereira2')
    .deleteMultiple();

IX. whereNotNull

Note: We will talk about dot walking later.

new GlideQuery('sys_user')
    .whereNotNull('company')
    .whereNotNull('company.city')
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

Returns:

*** Script: Lucius Bagnoli works in Tokyo
*** Script: Melinda Carleton works in London
*** Script: Jewel Agresta works in London
*** Script: Christian Marnell works in Prague
*** Script: Naomi Greenly works in London
*** Script: Jess Assad works in Tokyo
etc.

X. limit

new GlideQuery('sys_user')
    .whereNotNull('company')
    .whereNotNull('company.city')
    .limit(2)
    .select('name', 'company.city')
    .forEach(function (user) {
        gs.info(user.name + ' works in ' + user.company.city)
    });

Returns:

*** Script: Mildred Gallegas works in Rome
*** Script: Elisa Gracely works in Rome

XI. select

We often need queries that return several records and in these cases we use select(). This method returns an object of type Stream and we need a terminal method to process it:

a) forEach

var arrIncidens = [];
var incidents = new GlideQuery('incident')
    .where('state', 2)
    .limit(2)
    .select('number')
    .forEach(function (inc) {
        gs.info(inc.number + ' - ' + inc.sys_id);
        arrIncidens.push(inc.sys_id);
    });

if (arrIncidens.length==0)
    gs.info('Not found.')

If success:

*** Script: INC0000025 - 46f09e75a9fe198100f4ffd8d366d17b
*** Script: INC0000029 - 46f67787a9fe198101e06dfcf3a78e99

If fail:

*** Script: Not found.

b) toArray

Returns an array containing the items from a Stream. The method needs a parameter that is the maximum size of the array, the limit being 100.

var users = new global.GlideQuery('sys_user')
    .limit(20)
    .select('first_name', 'last_name')
    .toArray(2); // max number of items to return in the array

gs.info(JSON.stringify(users, null, 4));

Returns:

*** Script: [
    {
        "first_name": "survey",
        "last_name": "user",
        "sys_id": "005d500b536073005e0addeeff7b12f4"
    },
    {
        "first_name": "Lucius",
        "last_name": "Bagnoli",
        "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
    }
]

c) map

Used to transform each record in a Stream.

new GlideQuery('sys_user')
    .limit(3)
    .whereNotNull('first_name')
    .select('first_name')
    .map(function(user) {
        return user.first_name.toUpperCase();
    })
    .forEach(function(name) {
        gs.info(name);
    });

Returns:

*** Script: SURVEY
*** Script: LUCIUS
*** Script: JIMMIE

d) filter

Note: The filter in a Stream always occurs after the query has been executed. Therefore, whenever possible, we should make all possible filters before the select to avoid loss of performance.

var hasBadPassword = function(user) {
    return !user.user_password ||
        user.user_password.length < 10 ||
        user.user_password === user.last_password ||
        !/\d/.test(user.user_password) // no numbers
        ||
        !/[a-z]/.test(user.user_password) // no lowercase letters
        ||
        !/[A-Z]/.test(user.user_password); // no uppercase letters
};

new GlideQuery('sys_user')
    .where('sys_id', 'STARTSWITH', '3')
    .select('name', 'email', 'user_password', 'last_password')
    .filter(hasBadPassword)
    .forEach(function(user) {
        gs.info(user.name + ' - ' + user.sys_id)
    });

Returns:

*** Script: Patty Bernasconi - 3682abf03710200044e0bfc8bcbe5d17
*** Script: Veronica Achorn - 39826bf03710200044e0bfc8bcbe5d1f
*** Script: Jessie Barkle - 3a82abf03710200044e0bfc8bcbe5d10

e) limit

Both the GlideQuery and Stream APIs have the limit() method. In GlideQuery it is executed before the execution of the query, which is more performant. For this reason, whenever possible, we should use the limit before executing the select.

new GlideQuery('task')
    .orderBy('priority')
    .limit(3) // Good: calling GlideQuery's limit method
    .select('assigned_to', 'priority', 'description')
    //.limit(3) // Bad: calling Stream's limit method
    .forEach(function(myTask) {
        gs.info(myTask.priority + ' - ' + myTask.assigned_to)
    });

Returns:

*** Script: 1 - 5137153cc611227c000bbd1bd8cd2007
*** Script: 1 - 681b365ec0a80164000fb0b05854a0cd
*** Script: 1 - 5137153cc611227c000bbd1bd8cd2007

f) find

Returns the first item found in the Stream according to the given condition. Important notes:

• Returns an Optional, which may be empty if no item is found.
• The first item in the Stream is returned if no condition is entered.

var hasBadPassword = function(user) {
    return !user.user_password ||
        user.user_password.length < 10 ||
        user.user_password === user.last_password ||
        !/\d/.test(user.user_password) || // no numbers
        !/[a-z]/.test(user.user_password) || // no lowercase letters
        !/[A-Z]/.test(user.user_password); // no uppercase letters
};
var disableUser = function(user) {
    var myGQ = new GlideQuery('sys_user')
        .where('sys_id', user.sys_id)
        .update({
            active: false
        }, ['user_name'])
        .get();
        gs.info(JSON.stringify(myGQ, null, 4));
};
var myUsers = new GlideQuery('sys_user')
    .select('name', 'email', 'user_password', 'last_password')
    .find(hasBadPassword)
    .ifPresent(disableUser);

Returns:

*** Script: {
    "sys_id": "0e826bf03710200044e0bfc8bcbe5d45",
    "active": false,
    "user_name": "ross.spurger"
}

g) reduce

var longestFirstName = new global.GlideQuery('sys_user')
   .whereNotNull('first_name')
   .select('first_name')
   .reduce(function (acc, cur) {
       return (cur.first_name.length > acc.length) ? cur.first_name : acc;
       }, '');

gs.info(JSON.stringify(longestFirstName));

Returns:

*** Script: "Sitemap Scheduler User"

h) Every

Executes a function on each item in the Stream. If it returns true for all items in the Stream, the method returns true, otherwise it returns false.

var numToCompare = 10;
//var numToCompare = 1000;

var hasOnlyShortDescriptions = new global.GlideQuery('task')
    .whereNotNull('description')
    .select('description')
    .every(function(t) {
        return t.description.length < numToCompare;
    });

gs.info(hasOnlyShortDescriptions);

If numToCompare  is 10, returns:

*** Script: false

If numToCompare  is 1000, returns:

*** Script: true

i) some

Executes a function on each item in the Stream. If it returns true for at least one item in the Stream, the method returns true, otherwise it returns false.

var numToCompare = 10;
//var numToCompare = 1000;

var hasLongDescriptions = new global.GlideQuery('task')
   .whereNotNull('description')
   .select('description')
   .some(function (t) { 
      return t.description.length > numToCompare; 
   });

gs.info(hasLongDescriptions);

If numToCompare  is 10, returns:

*** Script: true

If numToCompare  is 1000, returns:

*** Script: false

j) flatMap

FlatMap is very similar to map but with 2 differences:

1.  The function passed to flatMap must return a Stream.
2. The flatMap manipulates (unwraps/flattens) the returned Stream so that the “parent” method can return the result.

var records = new global.GlideQuery('sys_user')
    .where('last_login', '>', '2015-12-31')
    .select('first_name', 'last_name')
    .flatMap(function(u) {
        return new global.GlideQuery('task')
            .where('closed_by', u.sys_id)
            .where('short_description', 'Prepare for shipment')
            .select('closed_at', 'short_description')
            .map(function(t) {
                return {
                    first_name: u.first_name,
                    last_name: u.last_name,
                    short_description: t.short_description,
                    closed_at: t.closed_at
                };
            });
    })
    .toArray(50);

gs.info(JSON.stringify(records, null, 4));

Returns:

*** Script: [
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:43:48"
    },
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:40:22"
    },
    {
        "first_name": "Thiago",
        "last_name": "Pereira",
        "short_description": "Prepare for shipment",
        "closed_at": "2021-10-04 13:42:14"
    }
]

XII. parse

Similar to GlideRecord’s addEcodedQuery but does not accept all operators. Currently only the following operators are supported:

var myTask = new GlideQuery.parse('task', 'active=true^short_descriptionLIKEthiago^ORDERBYpriority')
    .limit(10)
    .select('short_description', 'priority')
    .toArray(10); // 10 is the max number of items to return in the array

gs.info(JSON.stringify(myTask, null, 4));

Returns:

*** Script: [
    {
        "short_description": "thiago teste update 2",
        "priority": 1,
        "sys_id": "8b2c540b47ce0610cadcb60e316d4377"
    }
]

XIII. orderBy / orderByDesc

var users = new global.GlideQuery('sys_user')
    .limit(3)
    .whereNotNull('first_name')
    .orderBy('first_name')
    //.orderByDesc('first_name')
    .select('first_name', 'last_name')
    .toArray(3); 

gs.info(JSON.stringify(users, null, 4));

Returns:

*** Script: [
    {
        "first_name": "Abel",
        "last_name": "Tuter",
        "sys_id": "62826bf03710200044e0bfc8bcbe5df1"
    },
    {
        "first_name": "Abraham",
        "last_name": "Lincoln",
        "sys_id": "a8f98bb0eb32010045e1a5115206fe3a"
    },
    {
        "first_name": "Adela",
        "last_name": "Cervantsz",
        "sys_id": "0a826bf03710200044e0bfc8bcbe5d7a"
    }
]

XIV. withAcls

It works in the same way as GlideRecordSecure, forcing the use of ACLs in queries.

var users = new GlideQuery('sys_user')
    .withAcls()
    .limit(4)
    .orderByDesc('first_name')
    .select('first_name')
    .toArray(4);

gs.info(JSON.stringify(users, null, 4));

XV. disableAutoSysFields

new GlideQuery('task')
    .disableAutoSysFields()
    .insert({ description: 'example', priority: 1 });

XVI. forceUpdate

Forces an update to the registry. Useful, for example, when we want some BR to be executed.

new GlideQuery('task')
    .forceUpdate()
    .where('sys_id', 'd71b3b41c0a8016700a8ef040791e72a')
    .update()

XVII. disableWorkflow

new GlideQuery('sys_user')
    .disableWorkflow() // ignore business rules
    .where('email', 'bob@example.com')
    .updateMultiple({ active: false });

XVIII. Dot Walking

var tokyoEmployee = new GlideQuery('sys_user')
    .where('company.city', 'Tokyo')
    .selectOne('name', 'department.id')
    .get();
gs.info(JSON.stringify(tokyoEmployee, null, 4));

Returns:

*** Script: {
    "name": "Lucius Bagnoli",
    "department": {
        "id": "0023"
    },
    "sys_id": "02826bf03710200044e0bfc8bcbe5d3f"
}

XIX. Field Flags

Some fields support metadata. The most common case is the “display value”. We use the “$” symbol after the field name and specify the desired metadata. Metadata supported so far:

1. $DISPLAY = getDisplayValue
2. $CURRENCY_CODE = getCurrencyCode
3. $CURRENCY_DISPLAY = getCurrencyDisplayValue
4. $CURRENCY_STRING = getCurrencyString

new GlideQuery('sys_user')
    .whereNotNull('company')
    .limit(3)
    .select('company', 'company$DISPLAY')
    .forEach(function(user) {
        gs.info(user.company$DISPLAY + ' - ' + user.company);
    });

Returns:

*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2
*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2
*** Script: ACME Italy - 187d13f03710200044e0bfc8bcbe5df2

XX. aggregate

Used when we want to make aggregations. GlideQuery also has methods for this and they are often easier to use than GlideAggregate.

a) count

Using ‘normal’ GlideAggregate

var usersGa = new GlideAggregate('sys_user');
usersGa.addAggregate('COUNT');
usersGa.query();
usersGa.next();
gs.info(typeof (usersGa.getAggregate('COUNT')));
var userCount = parseInt(usersGa.getAggregate('COUNT'));
gs.info(typeof (userCount));
gs.info(userCount);

Returns:

*** Script: string
*** Script: number
*** Script: 629

Using GlideQuery

var userCount = new GlideQuery('sys_user').count();
gs.info(typeof(userCount));
gs.info(userCount);

Returns:

*** Script: number
*** Script: 629

Note that in addition to using fewer lines of code, GlideQuey returns a number while GlideAggregate returns a string.

b) avg

var faults = new GlideQuery('cmdb_ci')
    .avg('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 7.533

c) max

var faults = new GlideQuery('cmdb_ci')    
    .max('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 172

d) min

var faults = new GlideQuery('cmdb_ci')    
    .min('sys_mod_count')
    .orElse(0);
gs.info(faults);

Returns:

*** Script: 0

e) sum

var totalFaults = new GlideQuery('cmdb_ci')
    .sum('sys_mod_count')
    .orElse(0);
gs.info(totalFaults);

Returns:

*** Script: 20972

f) groupBy

var taskCount = new GlideQuery('incident')
    .aggregate('count')
    .groupBy('state')
    .select()
    .forEach(function (g) {
        gs.info(JSON.stringify(g, null, 4));
    });

Returns:

*** Script: {
    "group": {
        "state": 1
    },
    "count": 13
}
*** Script: {
    "group": {
        "state": 2
    },
    "count": 19
}
etc.

g) aggregate

var taskCount = new GlideQuery('task')
    .groupBy('contact_type')
    .aggregate('avg', 'reassignment_count')
    .select()
    .forEach(function (g) {
        gs.info(JSON.stringify(g, null, 4));
    });

Returns:

*** Script: {
    "group": {
        "contact_type": ""
    },
    "avg": {
        "reassignment_count": 0.0033
    }
}
*** Script: {
    "group": {
        "contact_type": "email"
    },
    "avg": {
        "reassignment_count": 1
    }
}
etc.

h) having

new GlideQuery('core_company')
    .aggregate('sum', 'market_cap')
    .groupBy('country')
    .having('sum', 'market_cap', '>', 0)
    .select()
    .forEach(function (g) {
        gs.info('Total market cap of ' + g.group.country + ': ' + g.sum.market_cap);
    });

Returns:

*** Script: Total market cap of : 48930000000
*** Script: Total market cap of USA: 5230000000

The post GlideQuery Cheat Sheet appeared first on ServiceNow Guru.

This article demonstrates how to streamline this process by creating a UI Action that allows you to move multiple customer updates between update sets with a single click.

Problem Statement
When you make configurations in your instance, there are times when you need to move these updates to a different update set. The conventional method requires opening each customer update record individually and changing the update set field to the desired update set. This method becomes impractical when dealing with numerous updates.

Solution
To overcome this challenge, you can create a UI Action that facilitates the movement of multiple customer updates to another update set in one go. This approach not only saves time but also reduces the risk of manual errors.

To begin, navigate to System Definition > UI Actions and create a new UI Action:

function showConfirmDialog() {

	var entries = g_list.getChecked();
	if (!entries || entries.length == 0)
		return;

	// this function runs when the modal close
	var callback = function () {

		// To send parameters to the server script we should use GlideAjax
		var ga = new GlideAjax('MoveUpdatesToCurrentUS');
		ga.addParam('sysparm_name', 'moveEntries');
		ga.addParam('sysparm_entry_ids', entries);

		// GlideAjax callback to refresh the related list
		var afterMove = function () {
			GlideList2.get(g_form.getTableName() + '.sys_update_xml.update_set').setFilterAndRefresh('');
		};

		ga.getXMLAnswer(afterMove.bind(this));
		return true;
	};

	var dialogClass = window.GlideModal ? GlideModal : GlideDialogWindow;
	var dialog = new dialogClass('glide_confirm_standard');
	dialog.setTitle(new GwtMessage().getMessage('Confirmation'));
	dialog.setPreference('warning', true);
	dialog.setPreference('title', new GwtMessage().getMessage('move_update_set_entries'));
	dialog.setPreference('onPromptComplete', callback.bind(this));
	dialog.render();
	
}

Two points I would like to highlight in this script.
– As we need to pass the selected records as a parameter to the server script, we need to use GlideAjax.
– For the callback function I needed to update only the related list. Initially I could only update the entire record but a quick search here on the portal led me to an article explaining exactly what I needed.

For the modal message to be displayed correctly, we need to create a record in the sys_ui_message table with the text we want.  Navigate to System UI > Messages and create a new message:

Now we need to create the Script Include that will perform all the necessary validations and actions. Navigate to System Definition> Script Includes and create a new script:

var MoveUpdatesToCurrentUS = Class.create();

MoveUpdatesToCurrentUS.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    moveEntries: function () {

        var arrMoved = [];
        var objMoved = {};
        var arrNotMoved = [];
        var objNotMoved = {};
        var currUS_ID = '';
        var customUpdateLink = '';
        var customUpdateType = '';
        var baseURL = gs.getProperty('glide.servlet.uri');

        //create a link to the syslog to return the logs created by this SI on last 15 minutes 
        var sysLogQuery = 'sys_created_onONLast 15 minutes@javascript:gs.beginningOfLast15Minutes()@javascript:gs.endOfLast15Minutes()^messageLIKEMoveUpdatesToCurrentUS';
        sysLogQuery = encodeURIComponent(sysLogQuery); //https://www.w3schools.com/jsref/jsref_encodeuricomponent.asp
        var sysLogURL = baseURL + 'syslog_list.do?sysparm_query=' + sysLogQuery;
        
        // get the user current US
        var currUS = gs.getPreference('sys_update_set');
        var grUS = new GlideRecord('sys_update_set');		

        if (grUS.get(currUS)) {
            currUS_ID = grUS.getUniqueValue();
        } else {
            gs.info('[MoveUpdatesToCurrentUS] Error: Unable to get Current US.');
            gs.addErrorMessage('There was an error. Please review the <a href="http://'%20+%20sysLogURL%20+%20'">system logs</a> for more details.');
            return false;
        }

        // get the variable sent by the UI Action
        var entries = this.getParameter('sysparm_entry_ids');

        //get all selected custom updates
        var customUpGr = new GlideRecord('sys_update_xml');
        customUpGr.addEncodedQuery('sys_idIN' + entries);
        customUpGr.query();

        while (customUpGr.next()) {
            
            //check if the user can write in the table
            if (customUpGr.canWrite()) {

                //create a link to the current update and get its type
                customUpdateLink = baseURL + 'sys_update_xml.do?sys_id=' + customUpGr.getValue('sys_id');
                customUpdateType = customUpGr.getValue('type');

                // check if it trying to move do a diferent US
                if (customUpGr.getValue('update_set') == currUS_ID) {

                    objNotMoved = {
                        sys_id: customUpGr.getUniqueValue(),
                        reasonHTML: 'Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> is already in your currently US.',
                        reasonText: 'Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - is already in your currently US.'
                    };

                    arrNotMoved.push(objNotMoved);

                } else {

                    // move the customer update to de current US
                    try {

                        customUpGr.setValue('update_set', currUS);
                        customUpGr.update();
                        
                        objMoved = {
                            sys_id: customUpGr.getUniqueValue(),
                            reasonHTML: 'Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> was moved to your currently US.',
                            reasonText: 'Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - was moved to your currently US.'
                        };

                        arrMoved.push(objMoved);

                    } catch (e) {

                        objNotMoved = {
                            sys_id: customUpGr.getUniqueValue(),
                            reasonHTML: 'Unable to move the Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> due: ' + e,
                            reasonText: 'Unable to move the Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - due: ' + e
                        };
    
                        arrNotMoved.push(objNotMoved);

                    }

                }				

            } else {

                objNotMoved = {
                    sys_id: customUpGr.getUniqueValue(),
                    reasonHTML: 'Unable to move the Custom US <a href="http://'%20+%20customUpdateLink%20+%20'">' + customUpdateType + '</a> due to ACL restrictions.',
                    reasonText: 'Unable to move the Custom US ' + customUpdateType + ' - ' + customUpGr.getValue('sys_id') + ' - due to ACL restrictions.'			
                };

                arrNotMoved.push(objNotMoved);

            }			

        }

        // create 2 messages: one to the syslog and one to show to user
        var sysLogMessage = '[MoveUpdatesToCurrentUS]' + '\r\r';

        if (arrMoved.length > 0) {
            
            var successScreenMessage = 'Move Updates to Current US Action' + '<br><br>';

            sysLogMessage += 'Custom Updates moved:\r';
            successScreenMessage += 'Custom Updates moved:<br>';

            for (var i = 0; i < arrMoved.length; i++) {
                sysLogMessage += arrMoved[i].reasonText + '\r';
                successScreenMessage += arrMoved[i].reasonHTML + '<br>';
            }

            gs.addInfoMessage(successScreenMessage);

        }

        if (arrNotMoved.length > 0) {

            var failureScreenMessage = 'Move Updates to Current US Action' + '<br><br>';

            sysLogMessage += '\r\rCustom Updates NOT moved:\r';
            failureScreenMessage += 'Custom Updates NOT moved:<br>';

            for (var x = 0; x < arrNotMoved.length; x++) {
                sysLogMessage += arrNotMoved[x].reasonText + '\r';
                failureScreenMessage += arrNotMoved[x].reasonHTML + '<br>';
            }

            gs.addErrorMessage(failureScreenMessage);

        }		

        gs.info(sysLogMessage);
        
        gs.addInfoMessage('Customer Update(s) transfer has been completed. Please review the <a href="http://'%20+%20sysLogURL%20+%20'">system logs</a> for more details.');

        return true;

    },

    type: 'MoveUpdatesToCurrentUS'
});

Using

• Open the update set from which you want to move few customer updates
• Go to the Customer Updates Related List
• Select all the updates you want to move
• Click on [Move Updates to Current US] in the actions dropdown menu

• If everything works fine all selected customer updates have been moved to your Current US and you will see a message – simple as that.

• If any problems occur during the transfer, you will be notified.

• Everything will be recorded in syslog

Benefits

• Efficiency: Moves multiple updates simultaneously, significantly reducing the time required.
• Accuracy: Minimizes the risk of errors associated with manual updates.
• Usability: Provides a straightforward and user-friendly method for managing update sets.

By implementing this UI Action, you can enhance your productivity and ensure a smoother workflow when managing update sets in ServiceNow. This method is particularly beneficial in complex development environments where rapid and accurate configuration management is essential.

The post Easily Move Customer Updates Between Update Sets appeared first on ServiceNow Guru.

In the same way we did with the SheetJS library, the first thing we need to do is download the necessary files. On GitHub you will find the link to download the “.js” and “.css” in cdnjs and some online demos.

The library’s documentation is very complete and contains several styling options and methods to use.

After downloading the files, create a dependency on the widget that will use the library:

After creating the dependency, the library will be ready to be used. We just need to start it and configure it according to our needs (in my case I just wanted the MM/YYYY information)

HTML

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

Link Function

$(document).ready(function(){

    setTimeout(function(){
            
        $('#monthYear').datepicker({
            format: "mm/yyyy",
            startDate: "-22m",				
            endDate: "-1m",
            startView: 1,
            minViewMode: 1,
            maxViewMode: 2,
            autoclose: true,
            toggleActive: true,
            language: "pt-BR"
        });

    },300);

});

After initialization and configuration as desired, it will be ready for use:

The post Service Portal and DatePicker Field appeared first on ServiceNow Guru.

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:

<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:

<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:

• The field Month/Year will be created using the Datepicker library: https://github.com/uxsolutions/bootstrap-datepicker

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

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();
            
        });
        
    }

};

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

The post Load Excel files to ServiceNow using Portal appeared first on ServiceNow Guru.