Zendesk Excel Export with PHP and cURL

composer.json

{
  "require": {
    "phpoffice/phpspreadsheet": "^1.9",
    "ext-curl": "*",
    "ext-json": "*"
  }
}

zendesk_excel_export.php

<?php

/**
* Please note: This is a snippet! You have to adapt it to yourself!
*
* Special features: Loading of custom fields dynamically with caching!
*
* Note: You need the ID of the custom fields to get their value!
* There is also a difference of getting CustomFields with Dropfown values and with textarea values.
*
* getStaticCustomFieldValue is for getting custom fields like text/textarea/date etc
* getCustomFieldValue gets the set value of the dropdown and gets its display name
*/

use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

require 'vendor/autoload.php';


$baseUrl = 'https://*****.zendesk.com';
$apiToken = "your_token";
$apiMail = "your_mail@host.tld/token"; // YOU HAVE TO LEAVE '/token' to tell Zendesk API you login with token as password

/**
 * Cache Variable for custom fields - do not edit!
 */
$customFieldCache = [];

function getStaticCustomFieldValue($field, $ticket)
{
    foreach ($ticket['custom_fields'] as $custom_field) {
        if ($custom_field['id'] == $field) {
            return $custom_field['value'];
        }
    }
    return null;
}

function getCustomFieldValue($field, $ticket)
{
    global $apiToken, $apiMail, $customFieldCache, $baseUrl;

    $ticketFieldValue = getStaticCustomFieldValue($field, $ticket);
    if (empty($ticketFieldValue)) {
        return null;
    }

    if (isset($customFieldCache[$field])) {
        return isset($customFieldCache[$field][$ticketFieldValue]) ? $customFieldCache[$field][$ticketFieldValue] : null;
    }

//echo "Have to fetch data".PHP_EOL;
    $ch = curl_init($baseUrl."/api/v2/ticket_fields/" . $field . ".json");

    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_USERPWD, $apiMail . ':' . $apiToken);

    $result = json_decode(curl_exec($ch), true);
    $info = curl_getinfo($ch);
    if ($info['http_code'] != '200') {
        echo "<h4>Error occured</h4><pre>";
        print_r($result);
        print_r($info);
        die();
    }

    if (isset($result['ticket_field']['custom_field_options'])) {
        foreach ($result['ticket_field']['custom_field_options'] as $custom_field_option) {
            $customFieldCache[$field][$custom_field_option['value']] = $custom_field_option['name'];
        }
    }

    if (isset($customFieldCache[$field][$ticketFieldValue])) {
        return $customFieldCache[$field][$ticketFieldValue];
    }

    return 'No data!';

}


$allTickets = [];
do {
    $page = !isset($data) ? $baseUrl . '/api/v2/incremental/tickets.json?start_time=0' : $data['next_page'];
//            echo "Getting Tickets, page: " . $page . PHP_EOL;
    $ch = curl_init($page);

    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_USERPWD, $apiMail . ':' . $apiToken);

    $result = curl_exec($ch);
    $info = curl_getinfo($ch);
    if ($info['http_code'] != '200') {
        echo "<h4>Error occured</h4><pre>";
        print_r($result);
        print_r($info);
        die();
    } else {

        $data = json_decode($result, true);
        if (!$data) {
            die(json_last_error_msg());
        }
//                echo PHP_EOL.' -> '.count($data['tickets']).PHP_EOL;
//                echo PHP_EOL.' EOS-> '.$data['end_of_stream'].PHP_EOL;
        $allTickets = array_merge($allTickets, $data['tickets']);
//                die(print_r($allTickets));
    }

} while (empty($data['end_of_stream']));

$id = array_column($allTickets, 'id');

array_multisort($id, SORT_ASC, $allTickets);


$s = new Spreadsheet();
$s->getProperties()->setCreator('Zendesk Exporter')->setTitle('Zendesk Excel Export');
$sheet = $s->getActiveSheet();


$row = 2;
$debugCount = 0;
foreach ($allTickets as $ticket) {
    $debugCount++;

    if ($ticket['status'] == 'deleted') {
        continue;
    }

    $sheet->setCellValueByColumnAndRow(1, $row, $ticket['id']);
    $sheet->getCellByColumnAndRow(1, $row)->getHyperlink()->setUrl($baseUrl.'/agent/tickets/' . $ticket['id']);
    $sheet->getStyleByColumnAndRow(1, $row)->getFont()->getColor()->setARGB(Color::COLOR_BLUE);
    $sheet->setCellValueByColumnAndRow(2, $row, $ticket['status']);
    $sheet->setCellValueByColumnAndRow(3, $row, getCustomFieldValue('360004073933', $ticket));
    $sheet->setCellValueExplicitByColumnAndRow(4, $row, getStaticCustomFieldValue('360004131674', $ticket), DataType::TYPE_STRING);
    $sheet->setCellValueExplicitByColumnAndRow(5, $row, getStaticCustomFieldValue('360004132134', $ticket), DataType::TYPE_STRING);
    $sheet->setCellValueByColumnAndRow(6, $row, date('d.m.Y', strtotime($ticket['created_at'])));
    $sheet->setCellValueByColumnAndRow(7, $row, empty(getStaticCustomFieldValue('360004176353', $ticket)) ? '' : date('d.m.Y', strtotime(getStaticCustomFieldValue('360004176353', $ticket))));
    $sheet->setCellValueExplicitByColumnAndRow(8, $row, getCustomFieldValue('360004227514', $ticket), DataType::TYPE_STRING);
    $sheet->setCellValueExplicitByColumnAndRow(9, $row, getCustomFieldValue('360004075293', $ticket), DataType::TYPE_STRING);
    $row++;
    if ($debugCount == 30) {
//                break;
    }
}

$writer = new Xlsx($s);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment; filename="Zendesk Export ' . date('d.m.Y H:i') . '.xlsx"');
$writer->save('php://output');