Scripting with Excel Spreadsheets

Michael Zglesze
Giga Expert

‎07-01-2020 10:31 AM

Hello Community!

I have been asked to automate the following process, but I'm unsure if ServiceNow has the capability to work with Excel files in a script (like in a Scheduled JOb) as is required:

  1. Open a given Excel Macro-Enabled Workbook in which data from ServiceNow will be populated.
  2. Populate the data in the proper rows and columns.
  3. Email the Excel Macro-Enabled Workbook to a particular email address.

I checked the ServiceNow Developer's Site under Reference --> APIs --> Server Global, and I didn't see any Classes or APIs for Excel listed.

Has anyone accomplished anything like this, or have any information that could help me develop this?

Thank you!

Michael Zgleszewski

Labels:
0 Helpfuls
  • 5,392 Views
3 REPLIES 3

Aoife Lucas
Giga Expert

‎07-02-2020 10:49 AM

Sure, it can do this very easily.  I do it from an Inbound Action (email attachment). 

Do an manual import of the Excel first to let ServiceNow create the temp import table for you (don't really have to but it saves time).

Create transform map to translate the temp table into your production table.

Here's my script that uses the above items to import and run the transform:

(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {
     
      var importSetTableName = "{your temp import table name}";
      var transformMapIDs = "{UUIDs of transform maps}";   //Use a comma to specify multiple transform maps
      var applicatonScope = "Global";
     
      // Create the datasource record
      current.name = "File import from: " + email.from;   //Feel free to rename this as appropriate
      current.import_set_table_name = importSetTableName;
      current.file_retrieval_method = "Attachment";
      current.type = "File";
      //current.format = "Excel"; // For Excel Files
      current.format = "CSV"; // For CSV Files
      current.header_row = 1;
      current.sheet_number = 1;
      current.sys_package.setDisplayValue(applicatonScope);
      current.sys_scope.setDisplayValue(applicatonScope);
      var dataSourceID = current.insert();

      /*
        * Schedule Load of Attachment
        *
        * This inbound email action will generate an import data source, however the attachment isn't copied to the data source until
        * after the insert of the record.   Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
        */
      new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);
     
})(current, event, email, logger, classifier);

Create a script include called EmailFileImportUtils:

var EmailFileImportUtils = Class.create();
EmailFileImportUtils.prototype = {
	initialize: function() {
	},
	
	scheduleImport: function(dataSourceID, transformMapIDs) {
		/*
 		* Create scheduled job to process import
 		*
 		* The inbound email action will generate an import data source, however the attachment isn't copied to the data source until
 		* after the insert of the record.   The code below will create a scheduled job to process the import 30 seconds later
 		* so that attachment has time to be copied to the data source from the email.
 		*/
		
		var schRec = new GlideRecord("sys_trigger");
		schRec.name = "Load Data Source: " + dataSourceID;
		schRec.trigger_type = 0;   // Run Once
		schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapIDs + "')";
		
		var nextAction = new GlideDateTime();
		nextAction.addSeconds(30);   // 30 seconds should be enough time however this can be changed.
		schRec.next_action = nextAction;
		schRec.insert();
	},
	
	loadImportSet: function(dataSourceID, transformMapIDs) {
		// Get Datasource Record
		var dataSource = new GlideRecord("sys_data_source");
		dataSource.get(dataSourceID);
		
		// If CSV and header isn't on row 1, recreate attachment with empty rows removed
		if (dataSource.getValue("format") == "CSV" && dataSource.getValue("header_row") > 1) {
			var attachmentRec = new GlideRecord("sys_attachment");
			attachmentRec.addQuery("table_sys_id", dataSource.getValue("sys_id"));
			attachmentRec.query();
			if (attachmentRec.next()) {
				var oldAttachmentID = attachmentRec.getValue("sys_id");
				var inputStream = new GlideSysAttachment().getContentStream(oldAttachmentID);
				var textReader = new GlideTextReader(inputStream);
				var ln = " ";
				var newLine = "";
				
				var lineCounter = 0;
				var headerRow = parseInt(dataSource.getValue("header_row"));
				while((ln = textReader.readLine()) != null) {
					lineCounter += 1;
					if (lineCounter < headerRow) {
						continue;
					}
					
					if (ln.length > 1) {
						newLine += ln + "\n";
					}
				}
				new GlideSysAttachment().write(dataSource, 'Changed ' + attachmentRec.getValue('file_name'), "text/csv", newLine);
			}
		}		
		
		// Process data source file
		var loader = new GlideImportSetLoader();
		var importSetRec = loader.getImportSetGr(dataSource);
		var ranload = loader.loadImportSetTable(importSetRec, dataSource);
		importSetRec.state = "loaded";
		importSetRec.update();
		
		// Transform import set
		var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapIDs);
		transformWorker.setBackground(true);
		transformWorker.start();
	},
	
	type: 'EmailFileImportUtils'
	
};

Works like a champ.  Thought I had a comment on that script include to give credit to the original author.  I cannot recall who it was but would like to add that into my script include to get proper attribution.  This script is a life saver.

Tapadh leat,

Aoife

Michael Zglesze
Giga Expert

‎07-07-2020 08:22 AM

Thank you, Aoife, for your help!  I never thought of creating a temporary table for the spreadsheet!  I may go that route, but I still have other questions.

Here's a little more background:  This is for a new audit process that all of our company's Business Applications are responsible for submitting once per month.  They need a list of all of our users with particular properties, which is easy (dump the sys_user table), but they need it in this suped-up, macro-enabled Excel Workbook, with multiple tabs, and our information in particular cells throughout.

I was hoping that there was some sort of magical JavaScript Class that I could use where I could import the template as an object from a MID Server, write the necessary data in particular cells in the object, then create a record in the sys_email table with the Excel Workbook as an attachment.  Something like the sudo-code below:

//	Code here to pull in Excel File from MID Server as attachment on ECC Queue record
//	...

//	Import the Excel Macro-Enabled Workbook with the magic JavaClass I'm looking for
var objExcel = new ImportExcel(excelFile);

//	Set the Worksheet to write to
objExcel.setWorksheet("Sheet1");

//	Query for a list of the users from the sys_user table
var grUser = new GlideRecord('sys_user');
grUser.query();

//	Write the User Name field to $B2, the Active field to $C2, and the Locked Out field to $D2, incrementing the row for each user record
while(grUser.next()) {
	objExcel.setValue('$B' + (grUser.getLocation() + 3), grUser.user_name);
	objExcel.setValue('$C' + (grUser.getLocation() + 3), grUser.active);
	objExcel.setValue('$D' + (grUser.getLocation() + 3), grUser.locked_out);
}

//	Create a record in the sys_email table to send an email with the new Excel file
var grEmail = new GlideRecord('sys_email');  
grEmail.initialize();
grEmail.recipients = 'infosec@company.com';  
grEmail.subject = 'User Account Report for ServiceNow';
grEmail.body = 'Hello InfoSec,\n\nPlease see the attached Excel Workbook for this month\'s User Account Report.\n\n\nThank you!';
grEmail.mailbox = 'outbox';
grEmail.type = 'send-ready';
grEmail.insert();

//	Create an GlideSysAttachment object to attach the Excel file with
var gsAttachment = new GlideSysAttachment();

//	Attach Excel file to the sys_email record
gsAttachment.write(grEmail, 'UserAccountReport.xlsm', 'application/vnd.ms-excel.sheet.macroEnabled.12', objExcel);

 

Is there a magical JavaScript Class that I can use to do something like this?

 

Thanks again, everyone!

~Michael Zgleszewski

 

0 Helpfuls

Jyothy S
Giga Contributor
In response to Michael Zglesze

‎12-18-2022 03:04 PM

Hi Michael, 

Did you get a solution for this?

Thanks

JS

 

0 Helpfuls