How to export an CSV file from Scripted REST APIs?

[LinhN]

Hi guys, I am writing a script at scripted REST Resource to export queried data into a CSV file.  I will create a UI button on my table, when I click this button, it will call a REST API. But it seems that my script to make a CSV file doesn't work. I know that I was lacking something. Please help me any solution to modify the script

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {
    var departmentCounts = {};

    // Function to process GlideAggregate results and sum counts
    function processResults(ga, keyField) {
        while (ga.next()) {
            var departmentName = ga.getValue(keyField); // Get the department name
            var recordCount = parseInt(ga.getAggregate('COUNT'), 10); // Get the count of records as integer

            // Sum up the counts for the same department name
            if (departmentCounts[departmentName]) {
                departmentCounts[departmentName] += recordCount;
            } else {
                departmentCounts[departmentName] = recordCount;
            }
        }
    }

    // First Query: Using assigned_to.department.name
    var ga1 = new GlideAggregate('alm_consumable');
    ga1.addQuery('model_category.name', 'Salesforce Voucher');
    ga1.addQuery('install_status', '10');
    ga1.addQuery('assigned_to.department.name', 'ENDSWITH', '部');
    ga1.addQuery('assigned_to.department.name', 'DOES NOT CONTAIN', '本部');
    ga1.addQuery('assigned_to.department.name', 'DOES NOT CONTAIN', '事業部');
    ga1.groupBy('assigned_to.department.name');
    ga1.addAggregate('COUNT');
    ga1.query();
    processResults(ga1, 'assigned_to.department.name');

    // Second Query: Using assigned_to.department.parent.name
    var ga2 = new GlideAggregate('alm_consumable');
    ga2.addQuery('model_category.name', 'Salesforce Voucher');
    ga2.addQuery('install_status', '10');
    ga2.addQuery('assigned_to.department.parent.name', 'ENDSWITH', '部');
    ga2.addQuery('assigned_to.department.parent.name', 'DOES NOT CONTAIN', '本部');
    ga2.addQuery('assigned_to.department.parent.name', 'DOES NOT CONTAIN', '事業部');
    ga2.groupBy('assigned_to.department.parent.name');
    ga2.addAggregate('COUNT');
    ga2.query();
    processResults(ga2, 'assigned_to.department.parent.name');

    // Generate CSV content
    var csvContent = 'Department Name,Total Record Count\n'; // CSV Header
    for (var department in departmentCounts) {
        csvContent += department + ',' + departmentCounts[department] + '\n';
    }

    // Set response headers for CSV download
    response.setContentType('text/csv');
    response.setHeader('Content-Disposition', 'attachment; filename="department_counts.csv"');

    // Write CSV content to response
    response.writeOutput(csvContent);

})(request, response);

Do I need to modify anything of the script in UI action and Scripted REST Resource? 

[JenniferRah]

Is this running at all? Because the URL in your UI Action looks wrong. The Name field on the Scripted REST Resource record is not part of the URL. The URL should just be your instance and the "Resource path" variable.

[Ankur Bawiskar]

@LinhN 

why to use Scripted REST API and not write the script inside the UI action itself?

[JenniferRah]

This is a good point. If you are just using it within ServiceNow, there's no need for a Scripted REST API.