- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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