Michael Zglesze
Giga Expert

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