How to generate excel sheet of variable editor and attach it to requested item.

[dj7887]

Hi All,

We have one catalog item and workflow behind it. At one stage of workflow we need to generate Excel sheet of few questions on variabl editor and and attach that excel sheet to current requested item.

Please provide your help on achieving this.

Thanks,

DJ

[Ankur Bawiskar]

Hi,

there is not out of the box way to handle this; you can create a csv file with the column header as the name of the variable, next row of csv as the variable values and attach it to the RITM record

Sample script below to get the variable labels as the 1st row of csv file and the next row as the variable values

you can enhance the script to have your file name

I assume this script is in workflow which runs on sc_req_item table

var ritmSysId = current.sys_id;
var set = new GlideappVariablePoolQuestionSet();
set.setRequestID(ritmSysId);
set.load();
var vs = set.getFlatQuestions();

var valuesArray = [];

var csvHeader = [];

for(var i=0;i<vs.size();i++){

var variableLabel = vs.get(i).getLabel();

csvHeader.push(variableLabel.toString());

var variableValue = vs.get(i).getDisplayValue();

valuesArray.push(variableValue.toString());

}

var csvHeaderRow = csvHeader.toString();
var valueRow =  valuesArray.toString();
var sa = new GlideSysAttachment();

var document = csvHeaderRow + "\n" + valueRow;

sa.write(current, "data1.csv", "test/csv", document);

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[dj7887]

Hi Ankur,

Thanks for your reply. I tried above code and workking fine. However is there any way that we can create excel file instead of csv.

Please help on this.

Thanks,

DJ.

[Ankur Bawiskar]

Hi Dj,

excel would be somewhat difficult because it is not simple file as csv or text file

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[dj7887]

Hi Ankur,

Is there any way that we can run report from workflow. If we run report on requested item variables and export that to excel and attach to RITM. is this something possible.

Please help on this.

Thanks,

DJ

[Ankur Bawiskar · Accepted solution]

Hi Dj,

Figured a way to do this

1) create a scheduled report for Report on Service Catalog Variables; keep it as active false; Run as On demand

Don't set any users in the users field. we don't want email to be triggered to any user; give some unique email Subject

https://docs.servicenow.com/bundle/madrid-performance-analytics-and-reporting/page/use/reporting/con...

2) in the RITM workflow use below script to trigger the scheduled report

var gr = new GlideRecord('sysauto_report');
gr.get('scheduledReportSysId');

// for global scope
SncTriggerSynchronizer.executeNow(gr);

// for custom scope
gs.executeNow(gr);

3) it creates an attachment in sys_attachment table for sys_email table

copy the attachment from that record to your ritm record

Note: one thing to be sure on this is you pick the correct record of sys_email

query sys_email table with that email subject given to scheduled report; orderByDesc() created date and setLimit(1)

sample script

var gr = new GlideRecord('sys_email');

gr.orderByDesc('sys_created_on');

gr.addQuery('subject','Sample Email Subject');

gr.setLimit(1);

gr.query();

if(gr.next()){

GlideSysAttachment.copy('sys_email', gr.sys_id, 'sc_req_item', current.sys_id);

}

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur