Recent searches


No recent searches

New request list experience - Export .csv Button

Pinned


Posted Oct 02, 2024

I really like the new Request list experience and we recently rolled it out. However, there were a few key features that were lacking including an export .csv button. I thought I would share the script to export .csv in case it benefits anyone else here. The code below can simply be added to the bottom of your requests_page.hbs

 

Requirements:

 

Once the requirements are met, you can simply drop this code into the end of your requests_page.hbs. Note, you may need to do some tweaking to get it to work, but it was designed to be generic.

 

</script>

<!-- Script to export the tickets, currently displayed on the screen, to .CSV including the selected columns -->
<script>
// Utility function to safely get and parse JSON from local storage
function getLocalStorageItem(key, defaultValue = []) {
    try {
        return JSON.parse(localStorage.getItem(key) || JSON.stringify(defaultValue));
    } catch (error) {
        console.error(`Error parsing localStorage item ${key}:`, error);
        return defaultValue;
    }
}

// Function to convert search parameters from the URL
function convertSearchParams() {
    const params = new URLSearchParams(window.location.search.slice(1));
    const queryArray = [];
    const ccId = params.get('selected_tab_name') === 'ccd-requests' ? 'cc_id=true' : '';
    let organizationId = '', page = '', perPage = '50'; // Default perPage to 50

    if (!window.location.search || params.get('selected_tab_name') === 'my-requests') {
        queryArray.push(encodeURIComponent('requester:me'));
    }

    params.forEach((value, key) => {
        if (key === 'organization_id') organizationId = `organization_id=${value}`;
        else if (key === 'page') page = `page=${value}`;
        else if (key === 'per_page') perPage = `per_page=${value}`;
        else if (key.startsWith('filter_custom_field_')) {
            const customFieldKey = key.replace('filter_', '');
            decodeURIComponent(value).split(' ').forEach(val => queryArray.push(`${customFieldKey}${val.trim()}`));
        } else if (['filter_custom_status_id', 'filter_updated_at', 'filter_created_at'].includes(key)) {
            const unfilteredKey = key.replace('filter_', '');
            decodeURIComponent(value).split(' ').forEach(val => queryArray.push(`${unfilteredKey}${val.trim()}`));
        } else if (!['selected_tab_name', 'query', 'sort_by', 'sort_order'].includes(key)) {
            queryArray.push(`${key}=${value}`);
        }
    });

    const query = `*+${queryArray.join('+')}`;
    const finalQuery = `${organizationId}&${page}&per_page=${perPage}&query=${query}`.replace(/^&+|&+$/g, '');
    return ccId ? `${ccId}&${finalQuery}` : finalQuery;
}

// Function to get the column configuration from local storage
function getColumnConfiguration() {
    return getLocalStorageItem('GUIDE_REQUESTS_APP_COLUMN_CONFIG', [])[1] || [];
}

// Fetch ticket fields and build a mapping of field IDs to 'title_in_portal' and 'custom_field_options'
async function fetchTicketFields() {
    const apiBase = '/api/v2/ticket_fields.json?locale=en-us&page[size]=100';
    let ticketFieldsMapping = {};
    let nextPage = apiBase;

    while (nextPage) {
        try {
            const response = await fetch(nextPage);
            const data = await response.json();
            data.ticket_fields?.forEach(field => {
                ticketFieldsMapping[field.id] = {
                    title_in_portal: field.title_in_portal,
                    custom_field_options: field.custom_field_options || []
                };
            });
            nextPage = data.next_page;
        } catch (error) {
            console.error('Error fetching ticket fields:', error);
            break;
        }
    }
    return ticketFieldsMapping;
}

// Utility function to get the custom field display value
function getCustomFieldDisplayValue(fieldValue, customOptions) {
    return customOptions.find(option => option.value === fieldValue)?.raw_name ?? fieldValue;
}

// Convert the flattened data to CSV format and trigger download
function exportToCSV(data, ticketFieldsMapping) {
    const userColumns = getColumnConfiguration();

    const { headers, headerToKeyMap } = userColumns.reduce(
        (acc, column) => {
            const fieldConfig = ticketFieldsMapping[column];
            let headerName = fieldConfig?.title_in_portal || 
                             { 'created_at': 'Created Date', 'updated_at': 'Updated Date' }[column] || 
                             (['subject', 'id', 'status', 'priority', 'type'].includes(column) ? column.charAt(0).toUpperCase() + column.slice(1) : column);
            
            acc.headerToKeyMap[headerName] = column;
            acc.headers.push(headerName);
            return acc;
        },
        { headers: [], headerToKeyMap: {} }
    );

    const csvRows = [headers.join(',')];

    data.forEach(request => {
        const row = headers.map(header => {
            const key = headerToKeyMap[header];
            let value = request[key];

            if (!value && request.custom_fields) {
                request.custom_fields.forEach(customField => {
                    if (customField.id === key || ticketFieldsMapping[customField.id]?.title_in_portal === header) {
                        value = getCustomFieldDisplayValue(customField.value, ticketFieldsMapping[customField.id]?.custom_field_options);
                    }
                });
            }

            if (Array.isArray(value)) {
                value = value.map(item => (typeof item === 'object' ? JSON.stringify(item) : item)).join(' | ');
            } else if (typeof value === 'object' && value !== null) {
                value = JSON.stringify(value);
            } else if (value === null || value === undefined) {
                value = '';
            }

            return `"${String(value).replace(/"/g, '""')}"`;  // Escape double quotes
        });
        csvRows.push(row.join(','));
    });

    const csvString = csvRows.join('\n');
    const blob = new Blob([csvString], { type: 'text/csv' });
    const fileName = `ExportedTickets-${new Date().toISOString()}.csv`;

    const link = document.createElement('a');
    link.href = URL.createObjectURL(blob);
    link.download = fileName;
    link.click();
}

// Fetch data from the search API and replace custom fields with display names
async function fetchDataFromAPI() {
    const apiBase = '/api/v2/requests/search.json';
    const queryString = convertSearchParams();
    const fullURL = `${apiBase}?${queryString}`;

    let allData = [];
    let nextPage = fullURL;

    const ticketFieldsMapping = await fetchTicketFields();

    while (nextPage) {
        try {
            const response = await fetch(nextPage);
            const data = await response.json();

            data.requests?.forEach(request => {
                const flattenedRequest = { ...request };
                request.custom_fields.forEach(customField => {
                    const fieldName = ticketFieldsMapping[customField.id]?.title_in_portal || `Unknown Field ${customField.id}`;
                    flattenedRequest[fieldName] = getCustomFieldDisplayValue(customField.value, ticketFieldsMapping[customField.id]?.custom_field_options);
                });
                allData.push(flattenedRequest);
            });
            nextPage = data.next_page;
        } catch (error) {
            console.error('Error fetching data:', error);
            break;
        }
    }

    exportToCSV(allData, ticketFieldsMapping);
    $(button).prop('disabled', false).text('Export Data');
}

// Append the Export Data button
const button = $('<button>', {
    text: 'Export Data',
    class: 'button',
    click: function() {
        $(this).prop('disabled', true).text('Exporting...');
        fetchDataFromAPI();
    }
});

$('#main-content').append(button);
</script>

0

1

1 comment

image avatar

Brett Bowser

Zendesk Community Manager

This is awesome. Thanks for sharing with everyone Matthew Toenjes !

0


Please sign in to leave a comment.

Didn't find what you're looking for?

New post