One of the most common uses of ServiceNow is importing and storing data. While import sets and transform maps have their own methods of validating the data before the data is imported into the system, it is also possible to validate the contents of an Excel file before allowing a user to attach it to the record.
By using the GlideExcelParser API, you can easily validate cell values of any .xlsx file imported into ServiceNow to ensure submitted attachments are the correct file or template.
The Process
For this example, I am going to create a record producer on the Incident table that aims to take in a spreadsheet that will be validated before the Incident is created. If the correct template is not attached, I will abort the creation of the record. This will prevent users from submitting incorrect files and could also be used as a way to force users to use a supplied template.
Here is the example spreadsheet I will be using. I will validate that upon submission the text in cell C3 is “Test text C3”.
Get Attachment ID
First, in order to use the GlideExcelParser API, you need to pass in the GlideScriptableInputStream of the attachment, which can be attained with the GlideSysAttachment API. Below, I am using a simple GlideRecord query to obtain the attachment’s sys_id.
Parse Attachment and Validate Cell Data
Now that I have obtained the sysID of the attachment on the record, I can pass that into a parser function. In this function, I access the GlideScriptableInputStream using getContentStream() in the GlideSysAttachment API. Once that is gathered, I can parse the file by using the GlideExcelParser API and its associated methods. Notes: According to the ServiceNow documentation, the namespace sn_impex must be used when creating a GlideExcelParser object. Also, this parser only applies for files of the .xlsx file type. In order to prevent users from submitting other files, a check must be added that validates the extension on the submitted attachment.
Further documentation
For more information about the GlideExcelParser API, including other methods that are available, check out the ServiceNow documentation site here.
