In this article we want to show in depth how to build an Include Script that will allow you to generate files in CSV format.
Preparing the Environment
1. Create a table that will serve as the target table (mandatory requirement) for generating the file/attachment:
Table Label: CSV Temp
Table Name: u_csv_temp
Note: You can use another table (core or scoped) according to the business rules, but I suggest using a table (CSV Temp) similar to the one proposed in this article.
2. Insert a field in the target table:
Column label: Name
Column name: name
Max length: 60
Note: You can use more fields as needed. But I suggest adding at least one field (Name) as a requirement of this implementation.
3. Script Include: GenerateCSV
var GenerateCSV = Class.create();
GenerateCSV.prototype = {
initialize: function() {
},
generate: function(csv_data) {
var has_csv_temp;
var deleted_csv_temp;
var attchment_sys_id = '';
var attchment_url_csv = '';
var error_message = 'No error message';
var csv_config = {
table_target: 'u_csv_temp',
sys_id_target: '',
filename: this.create_filename(),
headers: csv_data.incident_labels,
records: csv_data.incidents
};
has_csv_temp = this.has_csv_temp(csv_config);
if (has_csv_temp) {
deleted_csv_temp = this.delete_csv_temp(csv_config);
if (deleted_csv_temp) {
csv_config.sys_id_target = this.add_csv_temp(csv_config);
if (csv_config.sys_id_target && csv_config.sys_id_target !== '') {
attchment_sys_id = this.create_csv(csv_config);
if (attchment_sys_id && attchment_sys_id !== '') {
attchment_url_csv = this.create_url_csv_to_download(attchment_sys_id);
return attchment_url_csv;
} else {
error_message = 'Unable to generate CSV file!';
}
} else {
error_message = 'The CSV file could not be generated, because the record was not added to the table <CSV Temp>!';
}
} else {
error_message = 'The CSV file could not be generated, because it was not possible to delete the previous record in the <CSV Temp> table!';
}
} else {
csv_config.sys_id_target = this.add_csv_temp(csv_config);
if (csv_config.sys_id_target && csv_config.sys_id_target !== '') {
attchment_sys_id = this.create_csv(csv_config);
if (attchment_sys_id && attchment_sys_id !== '') {
attchment_url_csv = this.create_url_csv_to_download(attchment_sys_id);
return attchment_url_csv;
} else {
error_message = 'Could not generate CSV file!';
}
} else {
error_message = 'The CSV file could not be generated, because the record was not added to the table <CSV Temp>!';
}
}
return error_message;
},
create_filename: function() {
var filename = '';
filename = 'csv_' + gs.getUserID();
return filename;
},
has_csv_temp: function(csv_config) {
var has_csv_temp = false;
var glide_record = new GlideRecord(csv_config.table_target);
glide_record.addQuery("name", csv_config.filename);
glide_record.query();
if (glide_record.next()) {
has_csv_temp = true;
}
return has_csv_temp;
},
delete_csv_temp: function(csv_config) {
var deleted = false;
var glide_record = new GlideRecord(csv_config.table_target);
glide_record.addQuery("name", csv_config.filename);
glide_record.query();
if (glide_record.next()) {
deleted = glide_record.deleteRecord();
}
return deleted;
},
add_csv_temp: function(csv_config) {
var csv_temp_sys_id = '';
var glide_record = new GlideRecord(csv_config.table_target);
glide_record.initialize();
glide_record.name = csv_config.filename;
csv_temp_sys_id = glide_record.insert();
return csv_temp_sys_id;
},
create_csv: function(csv_config) {
var attachment_sys_id = '';
var csv_data = '';
var headers = csv_config.headers;
var records = csv_config.records;
var filename = csv_config.filename + '.csv';
for (var j = 0; j < headers.length; j++) {
if ((j + 1) == headers.length)
csv_data = csv_data + '"' + headers[j] + '"';
else
csv_data = csv_data + '"' + headers[j] + '"' + ',';
}
csv_data = csv_data + "\r\n";
for (var i = 0; i < records.length; i++) {
csv_data = csv_data + '"' + records[i].number + '",' +
'"' + records[i].opened_at + '",' +
'"' + records[i].short_description + '",' +
'"' + records[i].caller_name + '",' +
'"' + records[i].priority + '",' +
'"' + records[i].state + '",' +
'"' + records[i].category + '",' +
'"' + records[i].assignment_group + '",' +
'"' + records[i].assigned_to_name + '",' +
'"' + records[i].sys_updated_on + '"';
csv_data = csv_data + "\r\n";
}
attachment_sys_id = this.add_attachment(csv_config, filename, csv_data);
return attachment_sys_id;
},
add_attachment: function(csv_config, filename, csv_data) {
var attachment_sys_id = '';
var content_type = 'application/csv';
var glide_record = new GlideRecord(csv_config.table_target);
glide_record.get(csv_config.sys_id_target);
var glide_sys_attachment = new GlideSysAttachment();
attachment_sys_id = glide_sys_attachment.write(glide_record, filename, content_type, csv_data);
return attachment_sys_id;
},
create_url_csv_to_download: function(attachment_sys_id) {
var instace = 'https://' + gs.getProperty('instance_name') + '.service-now.com/';
var attachment_table = 'sys_attachment.do';
var attachment_param_sys_id = '?sys_id=' + attachment_sys_id;
var attachment_url = instace + attachment_table + attachment_param_sys_id;
return attachment_url;
},
type: 'GenerateCSV'
};
4. Widget: Incident CSV Generation
HTML Template
<div id="report">
<h4 class="page-title">{{ c.title }}</h4> <br />
<button type="button" name="button-generate-csv" class="button-generate-csv" ng-click="c.generate_csv()">Generate CSV</button>
<div style="font-family: Roboto; font-size: 16px;">
<table width="100%" style="border: 2px solid #000;">
<tbody>
<tr>
<td rowspan="3"><img src="logo_sn.png" width="110" height="110" style="margin-top: -1px; margin-bottom: -1px;"/></td>
<td style="font-weight: bold;">Incident CSV Generation</td>
</tr>
<tr>
<td><strong>Issue:</strong> {{ c.issue_date }} {{ c.issue_hour }}</td>
</tr>
<tr>
<td><strong>Page:</strong> <span class="current-page-number"></span> de <span class="current-page-number"></span></td>
</tr>
</tbody>
</table>
</div>
<div style="font-family: Roboto; font-size: 12px; margin-top: 24px;" class="report-body">
<table style="" width="100%">
<thead>
<tr>
<th style="padding: 10px 6px; background-color: #C1C1C1; border-left: 2px solid #C1C1C1; border-right: 2px solid #C1C1C1; text-align: center; font-weight: bold; color: #000; border: 2px solid #000; border-spacing: 0px; border-collapse: collapse;" ng-repeat="label in c.incident_label_fields">{{ label }}</th>
</tr>
</thead>
<tbody>
<tr ng-repeat="incident in c.incidents">
<td style="padding: 10px 6px; text-align: center; font-weight: 600; border: 2px solid #000; border-spacing: 0px; border-collapse: collapse;" ng-repeat="property in incident">{{ property }}</td>
</tr>
</tbody>
</table>
</div>
</div>
CSS
.button-generate-csv {
font-family: Roboto;
margin-bottom: 24px;
}
.page-title {
border-bottom: 1px solid #000;
padding-bottom: 5px;
font-weight: 600;
font-family: Roboto;
}
Client Script
api.controller=function($scope, $window, $location) {
/* widget controller */
var c = this;
c.title = 'Incident Report';
c.incident_label_fields = [];
c.incidents = [];
c.attachment_sys_id = '';
c.issue_date = '';
c.issue_hour = '';
get_date_hour_issue();
c.incident_label_fields = c.data.incident_label_fields;
c.incidents = organize_incidents(c.data.incidents);
/*
* Set date and time when the report is issued.
*/
function get_date_hour_issue() {
c.issue_date = moment().format("DD/MM/YYYY");
c.issue_hour = moment().format("HH:mm:ss");
}
function organize_incidents(incidents) {
var incidents_organized = incidents;
incidents_organized.map(function(incident) {
return {
number: incident.number,
opened_at: incident.opened_at,
short_description: incident.short_description,
caller_name: incident.caller_name,
priority: incident.priority,
state: incident.state,
category: incident.category,
assignment_group: incident.assignment_group,
assigned_to_name: incident.assigned_to_name,
sys_updated_on: incident.sys_updated_on
}
});
return incidents_organized;
}
/*
* Generate CSV.
*/
c.generate_csv = function() {
var attachment_url = '';
var error_message = '';
var obj = {
action: 'generate_csv',
};
c.server.get(obj).then(function(response) {
c.data.action = undefined;
if (response.data.attachment_url && response.data.attachment_url !== '') {
attachment_url = response.data.attachment_url;
$window.open(attachment_url, '_self');
} else {
error_message = response.data.error_message;
alert(error_message);
}
});
}
};
Server Script
(function() {
/* populate the 'data' object */
/* e.g., data.table = $sp.getValue('table'); */
var table_name = 'incident';
data.attachment_url = '';
data.error_message = '';
data.attachment_sys_id = '';
data.incident_label_fields = [];
data.incidents = [];
data.incident_label_fields = get_incident_label_fields();
data.incidents = get_incidents();
if (input && input.action == 'generate_csv') {
var csv_data = {
incident_labels: get_incident_label_fields(),
incidents: get_incidents()
};
generate_csv(csv_data);
}
function generate_csv(csv_data) {
var response_generate_csv = '';
var is_url_response_generate_csv = '';
var generate_csv = new GenerateCSV();
response_generate_csv = generate_csv.generate(csv_data);
is_url_response_generate_csv = response_generate_csv.startsWith('http');
if (response_generate_csv && is_url_response_generate_csv)
data.attachment_url = response_generate_csv;
else
data.error_message = response_generate_csv;
}
function get_incident_label_fields() {
var incident_label_names = [];
var glide_record_incidents = new GlideRecord(table_name);
glide_record_incidents.setLimit(1);
glide_record_incidents.query();
if (glide_record_incidents.next()) {
incident_label_names.push(glide_record_incidents.number.getLabel());
incident_label_names.push(glide_record_incidents.opened_at.getLabel());
incident_label_names.push(glide_record_incidents.short_description.getLabel());
incident_label_names.push(glide_record_incidents.caller_id.getLabel());
incident_label_names.push(glide_record_incidents.priority.getLabel());
incident_label_names.push(glide_record_incidents.state.getLabel());
incident_label_names.push(glide_record_incidents.category.getLabel());
incident_label_names.push(glide_record_incidents.assignment_group.getLabel());
incident_label_names.push(glide_record_incidents.assigned_to.getLabel());
incident_label_names.push(glide_record_incidents.sys_updated_on.getLabel());
}
return incident_label_names;
}
function get_incidents() {
var incidents = [];
var glide_record_incidents = new GlideRecord(table_name);
glide_record_incidents.orderByDesc('sys_updated_on');
glide_record_incidents.query();
while (glide_record_incidents.next()) {
var incident = {
number: glide_record_incidents.number.toString(),
opened_at: glide_record_incidents.opened_at.toString(),
short_description: glide_record_incidents.short_description.toString(),
caller_name: glide_record_incidents.caller_id.first_name.toString() + ' ' + glide_record_incidents.caller_id.last_name.toString(),
priority: glide_record_incidents.priority.getDisplayValue().toString(),
state: glide_record_incidents.state.getDisplayValue().toString(),
category: glide_record_incidents.category.getDisplayValue().toString(),
assignment_group: glide_record_incidents.assignment_group.name.toString(),
assigned_to_name: glide_record_incidents.assigned_to.first_name.toString() + ' ' + glide_record_incidents.assigned_to.last_name.toString(),
sys_updated_on: glide_record_incidents.sys_updated_on.toString()
};
incidents.push(incident);
incident = {};
}
return incidents;
}
})();
Note 1: The Script Include implemented that when generating an attachment (CSV file), it is verified if there is already a record in the table 'CSV Temp' that has this attachment, related to the user logged in the application. If so, this record in the 'CSV Temp' table is deleted and, consequently, the related attachment in the 'sys_attachment' table. Then a new record is created in the 'CSV Temp' table related to the logged in user, and the attachment (CSV file) that is related to this record in the 'CSV Temp' table is generated. The Script Include finally provides a link to automatically download the attachment (CSV file).
I hope I have helped you!
Hi @Leandro Lopes,
It worked like a charm.
1) Widget with the list of records:
2) Exported csv file:
Thank you.
