Export Smart Assessment Responses to Excel/PDF

ujjwalababa · 3 weeks ago

Hello All,

I would like to know if there is currently any capability to export Smart Assessment responses to Excel or PDF format.

Our end users have expressed a need to download and retain assessment responses for reporting, review, and audit purposes. Having an export option would significantly improve the user experience and help users manage assessment records more efficiently.

Has anyone implemented a similar solution or are there any plans to support this functionality?

Thank you in advance for your insights.

Thank you

prerna_sh · 3 weeks ago

Hi @ujjwalababa ,

You can create a Business Rule on the Smart Assessment Instance (sn_smart_asmt_instance) table. Configure it as an After Update with a condition such as State changes to Complete, meaning the Business Rule will execute when the Smart Assessment has been completed.

You can also add an additional condition specific to your assessment if required. Otherwise, you can proceed with just the State changes to Complete condition.

and use the Created Script Include in the BR, you'll get the Excel on your assessment.

// 1. Get External Assessment from M2M

var extM2M = new GlideRecord('sn_vdr_risk_asmt_m2m_tpra_sae_template');
extM2M.addQuery('sae_questionnaire_instance', smartAssessmentInstance);
extM2M.query();

if (!extM2M.next()) {
    return;
}
var externalAssessmentSysId = extM2M.getValue('vendor_risk_assessment');


// 2. Get Question Instances

var questionGR = new GlideRecord('sn_smart_asmt_question_instance');
questionGR.addQuery('assessment_instance', smartAssessmentInstance);
questionGR.orderBy('order');
questionGR.query();


// 3. Start Excel XML

var excel = '';
excel += '<?xml version="1.0"?>';
excel += '<?mso-application progid="Excel.Sheet"?>';
excel += '<Workbook ' +  'xmlns="urn:schemas-microsoft-com:office:spreadsheet" ' + 'xmlns:o="urn:schemas-microsoft-com:office:office" ' + 'xmlns:x="urn:schemas-microsoft-com:office:excel" ' + 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">';
excel += '<Worksheet ss:Name="Assessment Responses">';
excel += '<Table>';


// 4. Header
excel += '<Row>';
excel += '<Cell><Data ss:Type="String">Question</Data></Cell>';
excel += '<Cell><Data ss:Type="String">Response</Data></Cell>';
excel += '<Cell><Data ss:Type="String">Justification</Data></Cell>';
excel += '</Row>';


// 5. Process Questions

while (questionGR.next()) {
    var questionRecord = questionGR.getElement('assessment_question').getRefRecord();
    if (!questionRecord.isValidRecord()) {
        continue;
    }

    // Get Question
    var question = questionGR.getDisplayValue('assessment_question');
    // Get Selected Response
    var response = questionGR.getDisplayValue('selected_response_options');
    }

    // Add Question + Response 
    excel += '<Row>';
    excel += '<Cell><Data ss:Type="String">' + escapeXML(question) + '</Data></Cell>';
    excel += '<Cell><Data ss:Type="String">' + escapeXML(response) + '</Data></Cell>';
    excel += '</Row>';
}


// 6. Close Excel XML
excel += '</Table>';
excel += '</Worksheet>';
excel += '</Workbook>';


// 7. Get External Assessment
var assessmentGR = new GlideRecord('sn_vdr_risk_asmt_assessment');
assessmentGR.addQuery('sys_id', externalAssessmentSysId);
assessmentGR.query();
if (!assessmentGR.next()) {
    return;
}


// 8. Create Attachment

var attachment = new GlideSysAttachment();

var fileName = 'Assessment responses(' + extM2M.getDisplayValue('sae_questionnaire_template') + ')-' +assessmentGR.getValue('number') + '.xls';
var attachmentSysId = attachment.write( assessmentGR, fileName, 'application/vnd.ms-excel', excel);
return attachmentSysId;


// 9. XML Escape Function

function escapeXML(value) {
    if (value === null || value === undefined) {
        return '';
    }

    value = String(value);
    value = value.replace(/&/g, '&amp;');
    value = value.replace(/</g, '&lt;');
    value = value.replace(/>/g, '&gt;');
    value = value.replace(/"/g, '&quot;');
    value = value.replace(/'/g, '&apos;');

    return value;
}

Also, you can put conditions for the responses you got as per your requirement.

-------------------------------------------------------------------------------------------------------------------------------------------
If my response solves your query, please mark it as helpful by selecting accept as Solution and Helpful. Let me know if anything else is required.
Thanks,
Prerna

View solution in original post

ujjwalababa · 2 weeks ago

Hello @prerna_sh ,

Thank you for sharing the code and the detailed explanation...This was much needed 🙂 However, I am encountering an error when opening the downloaded Excel file. Do you have any idea what might be causing this issue? Could you please suggest how I can resolve it and prevent the error from occurring?
Thank you

Ujjwala

prerna_sh · 2 weeks ago

Hi @ujjwalababa
I have also encountered the same warning when opening the generated file, but I have Kept it as it is for now.
This warning appears because the script generates the Excel content in XML format but saves the file with an .xls extension. Excel detects the format mismatch and shows this warning.

The data is still generated correctly. To avoid the warning, you need to generate the file in a native Excel format instead of XML. To do so You can make the changes in the script itself.

-------------------------------------------------------------------------------------------------------------------------------------------
If my response solves your query, please mark it as helpful by selecting accept as Solution and Helpful. Let me know if anything else is required.
Thanks,
Prerna