How to generate excel sheet using variables in service catalog and attach them to change request

[KM10]

Hi All,

We have one catalog item and workflow designer behind it. At one stage of workflow we need to generate Excel sheet of  questions on variable editor and few variables are from multi row variable set and attach that excel sheet to normal change request item.

Please provide your help on achieving this.

Thanks,

KM

[Bhavya11]

Hi @KM10 

hope this will help you

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

Create an attachment with submitted variables as its content 

Please mark as Correct Answer and Helpful, if applicable.
Thanks!

BK

[Ubada Barmawar]

Hi @KM10 

you can write BR on "sc_req_item" or in custom action script. the script below (sample BR) generates an excel file and attaches the same to submitted RITM record. you can use copy attachment action in flow designer to copy it to change request in next step (or you can achieve it all in a single custom action script using "copy()" method)

----------------------------------------------------------------------------------------------------------

var questions = '';
var answers = '';
var variable;

// Retrieve all variables from the pool and sort them
var sortedVariables = sortVariables(current.variables);

// Iterate through sorted variables, excluding empty and certain types
for (var i in sortedVariables) {
variable = current.variables[sortedVariables[i].index];
if (variable != '' && variable != 'false' &&
variable.getGlideObject().getQuestion().type != 11 &&
variable.getGlideObject().getQuestion().type != 19 &&
variable.getGlideObject().getQuestion().type != 20) {
questions += variable.getGlideObject().getQuestion().getLabel() + "\t";
answers += variable.getDisplayValue() + "\t";
}
}

// Combine questions and answers into tab-delimited format
var finalOutput = questions + "\n" + answers;

// Function to sort variables based on their order
function sortVariables(variableArray) {
var sortedVariables = [];
for (var i in variableArray) {
var object = {
index: i,
order: variableArray[i].getGlideObject().getQuestion().order
};
sortedVariables.push(object);
}
sortedVariables.sort(function compare(a, b) {
return a.order - b.order;
});
return sortedVariables;
}

// Attach the Excel file to the current record (note: .xls files are actually tab-delimited text files in this context)
var attachment = new GlideSysAttachment();
attachment.write(current, current.number + '.xls', 'application/vnd.ms-excel', finalOutput);

---------------------------------------------------------------------------------------------------------------

please make modifications if you have to use this in flow designer, you can also use flow designer action inputs if required.

if this solves your query, please appreciate the effort by marking this as accepted solution and helpful.

regards,

Ubada Barmawar