Export Data from Background Script to Excel

DebjitGhosh31 · ‎01-25-2023

Hi,

I am currently using a script that stores all duplicate CI serial number in an array. Now my requirement is to export the data stored in the array to an excel file.

Is there any way I can dump the data from the script to an excel file.

Regards,

Debjit

Saurabh Gupta · ‎01-25-2023

Hi,
Below is sample code

var grX = new GlideRecord("sys_user");//some where we need to save the file here I am attaching on one of the user record logged in one
var csvData="";//This should always be in the 2D array format
//FieldLabel1,FieldLabel2,FieldLabel3,FieldLabel4=====This Row is For Header of CSV
//Val11,Val12,Val13,Val14==== Row 1
//Val21,Val22,Val23,Val24 ==== Row 2
  grX.addQuery('sys_id',gs.getUserID());
  grX.query();
  if(grX.next())
  {
csvHeader="UserID,Name,Company\n";
csvRows1="ABC1,Mario1,SPM1\n";
csvRows2="ABC2,Mario2,SPM2\n";
csvRows3="ABC3,Mario3,SPM3\n";
csvData=csvHeader+csvRows1+csvRows2+csvRows3;
    var grAttachment = new GlideSysAttachment();
    grAttachment.write(grX, "User Details.csv", 'application/csv', csvData);
  }

Thanks and Regards,

Saurabh Gupta

View solution in original post

AJAY1231 · ‎11-14-2024

Hi , I was able to acheive this by bringing to Excel (.xlsx) instead of csv.

Just below copy paste the code, and u will have excel created in the specified record.

Script:

var rows_data = "";

var columns_of_excel = "<?xml version='1.0' encoding='UTF-8'?>";

columns_of_excel += "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>";

// Define the Styles for bold headers

columns_of_excel += "<Styles>";

columns_of_excel += "<Style ss:ID='BoldHeader'>";

columns_of_excel += "<Font ss:Bold='1'/>"; // Make the font bold

columns_of_excel += "</Style>";

columns_of_excel += "</Styles>";

columns_of_excel += "<Worksheet ss:Name='Incident Data'>";

columns_of_excel += "<Table>";

// Define the headers and apply the bold style

columns_of_excel += "<Row>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Number</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Opened</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Short Description</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Created</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Caller</Data></Cell>";

columns_of_excel += "</Row>";

// Retrieve the incidents for the 'software' category

var gr = new GlideRecord('incident');

gr.addQuery('category', 'software');

gr.query();

while (gr.next()) {

rows_data += "<Row>";

rows_data += "<Cell><Data ss:Type='String'>" + gr.number + "</Data></Cell>";

rows_data += "<Cell><Data ss:Type='String'>" + gr.opened_at + "</Data></Cell>";

rows_data += "<Cell><Data ss:Type='String'>" + gr.short_description + "</Data></Cell>";

rows_data += "<Cell><Data ss:Type='String'>" + gr.sys_created_on + "</Data></Cell>";

rows_data += "<Cell><Data ss:Type='String'>" + gr.caller_id.getDisplayValue() + "</Data></Cell>";

rows_data += "</Row>";

}

// Close the XML structure

columns_of_excel += rows_data;

columns_of_excel += "</Table>";

columns_of_excel += "</Worksheet>";

columns_of_excel += "</Workbook>";

// Combine the columns (headers) and rows data

var columns_and_rows = columns_of_excel; // complete data including headers and rows

// Get the current user record for attaching the file

var user = new GlideRecord('sys_user');

user.addQuery('sys_id', gs.getUserID());

user.query();

user.next();

// Create the attachment

var attachment = new GlideSysAttachment();

attachment.write(user, "Incident software.xls", "application/vnd.ms-excel", columns_and_rows);

gs.print("Excel file exported and attached successfully. Please check.");

Nick D · ‎11-22-2024

Trying out your solution, I got the following error when opening the attachment: "The file extension and format of ... does not match". How were you able to fix that?

ajays · ‎11-22-2024

Share your code here , will check and let know

Jeffrey Siegel · ‎06-05-2025

Were you able to figure out how to fix this error?

dbcandle · ‎05-06-2023

I have a similar use-case. I'm learning ServiceNow from the developer perspective (Studio, client/server scripts, etc.), but still have a long way to go.

Is the script above a server-script (JavaScript)? Is it something that "power users" could do, or does it require lots of sensitive roles/permissions, where a large organization would be reluctant to grant access?

I'm currently learning on ServiceNow's "learn" site: my access at work is very limited at present.