“Do not go where the path may lead, go instead where there is no path and leave a trail.”
— Ralph Waldo Emerson
When we hacked the REST Message API to fetch a remote file, all we did with the file was to attach it to an existing Incident. But there are times when what you are really after is the contents of the file, not the file itself. When that file happens to be an Excel spreadsheet, the ServiceNow platform comes shipped with a handy tool called the GlideExcelParser that will allow you to dig into that file and pull out the data. By combining this capability with our earlier work on fetching remote files, you can reach out to a URL, pull in a spreadsheet from another site, and then parse the spreadsheet to extract the data that you need.
One example of where this might be useful would be in importing data records where one or more fields on the record is a URL of an associated spreadsheet containing relevant information such a list of items to be ordered or a list of people for which you would like to place an order. Another example might be a mail-in ticket where the details are not included in the body of the email or attached to the email as a file, but accessible via a URL link found in the message body. Basically, this applies to any scenario where you have the URL of a file, and just want the data contained in the file and not the actual file.
To demo this capability, we just need to find a spreadsheet hosted on the Internet that we can use for an example. Something like this:
This document has three sheets, but for our purposes we can just focus on one of the areas of one of the sheets, just to show how things work. Here is the second sheet, labeled Input:
For our little demo, we will focus on the grey block on rows 20 through 31 so that we can pull out some data and then use it for some other purpose such as creating an Incident. Once we pull out the data, we will no longer need the actual file, so we can delete the attachment that we created to tidy things up when we are done. Since this is just a demo, we can do the whole thing in a Fix Script that we can discard later once our little proof of concept has been completed.
The first thing that we will need to do is to go get our file. For that, we can leverage our earlier Script Include, but we will need to attach it to something while we work with it. For the purposes of this demonstration, let’s just attach it to the Fix Script itself.
var url = 'https://snhackery.com/update_sets/servicenow-platform-support-calculator-template.xlsx';
var table = 'sys_script_fix';
var sys_id = '0bbdf83e2f21d0104425fcecf699b660';
var fileFetcher = new FileFetchUtils();
var attachment = fileFetcher.fetchFileFromUrlAndAttach(table, sys_id, url);
That was simple enough. Now let’s parse the file.
var stream = new GlideSysAttachment().getContentStream(attachment);
var parser = new sn_impex.GlideExcelParser();
parser.setSheetName('Input');
var row = [];
if (parser.parse(stream)) {
while (parser.next()) {
row.push(parser.getRow());
}
} else {
gs.info("Error: Unable to parse uploaded file");
}
OK, this one is a little more complicated. First, we create a content stream from the file attachment. Then we create a new parser. Then we tell the parser that we want it to parse the second sheet, the one labeled Input. Then we create an empty array to store the rows, and loop through the parsed stream adding rows to our array one at a time until we run out of rows. Now we have an array of row that that we can use for whatever purpose that we want.
At this point, we can delete the attachment, since we have no further use for it, but let’s save that for a little later and first see what we can do with our row data. To start with, let’s just make sure that we have some data, and if we do, let’s call a function in which we can encapsulate all of the code to utilize the data.
if (row.length) {
gs.info('Incident created: ' + createIncident(row));
} else {
gs.info("Error: No data rows in parsed uploaded file");
}
Now it’s time to deal with the data. The parsed rows are values keyed by the column heading. Where there is no column heading, then the key is the Excel column letter. In our little example, we are interested in the data in the second and third columns, and in the first row, there is a value in the second column (Data Input Form), but not in the third. This means that second column values will be obtained with thisRow[‘Data Input Form’] and third column values will be obtained with thisRow[‘C’] (since C is Excel’s name for the third column).
So to rebuild that little grey block of data, we will need the second column value for row #19, the second and third column values for rows 21 through 26, and then the second and third column values for row 28. Now, that’s assuming that no one adds or removes a line or a column from the spreadsheet when filling out the template, which could be dealt with using a lot more defensive coding strategy, but that’s a little beyond the scope of this little exercise here. For our purposes, we are just going to assume that things are going to be right where they are supposed to be. So here is our function.
function createIncident(row) {
var full_description = 'The following data was extracted from the fetched spreadsheet:\n\n';
full_description += row[19]['Data Input Form'] + '\n\n';
for (var i=21; i<27; i++) {
full_description += '\t' + row[i]['Data Input Form'] + ':\t' + row[i]['C'] + '\n';
}
full_description += '\n' + row[28]['Data Input Form'] + '\t' + row[28]['C'];
var incidentGR = new GlideRecord('incident');
incidentGR.caller_id = gs.getUserID();
incidentGR.short_description = 'Excel parsing example';
incidentGR.description = full_description;
incidentGR.assignment_group.setDisplayValue('Service Desk');
incidentGR.insert();
return incidentGR.getDisplayValue('number');
}
Now that the Incident has been created, we just need to delete the attachment record to clean things up. That’s just a few more lines of code.
var attachmentGR = new GlideRecord('sys_attachment');
attachmentGR.get(attachment);
attachmentGR.deleteRecord();
That’s it. All that is left is to save the script and then click on that little Run Fix Script button to see what happens.
*** Script: Incident created: INC0010019
Let’s go check out our new Incident and see how things came out.
Well, it looks pretty close, but we missed a line of data. The System Administrators line didn’t make it over. Looks like our start index should have been 20 and not 21. Oh well, you get the idea. I would go back and fix it, but this is just a little demo to show how things could work together, and I think we have accomplished that. If you want to play around with this, you will be using your own spreadsheet anyway, so you’ll be doing something completely different. But if you want to fix your own copy of this one, please go right ahead!
