Hoping someone here can help me with these codes. FYI I have almost zero experience coding in Java so I just asked ChatGPT to code for me.
I have two separate codes that:
- Pulls ticket data (assignee name, ticket ID, created date)
- Pulls ticket metrics (ticket ID, solved date, resolution time)
I need to combine these two so I can get only data that was solved within a spific date.
Code to get ticket date:
function getZendeskTickets() {
var subdomain = 'our-subdomain';
var email = 'our-email-address';
var token = 'our-token';
var url = 'https://' + subdomain + '.zendesk.com/api/v2/tickets.json?per_page=100&page=' + '&sort_by=created_at&sort_order=desc';
var headers = {
'Authorization': 'Basic ' + Utilities.base64Encode(email + ':' + token)
};
var options = {
'headers': headers,
'method': 'get',
'muteHttpExceptions': true
};
var sheetName = "Tickets";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
}
var numRows = sheet.getLastRow();
if (numRows > 1) {
sheet.getRange(2, 1, numRows - 1, 5).clear(); // clear previous data
}
var row = 2;
var nextPageUrl = url;
while (nextPageUrl) {
var response = UrlFetchApp.fetch(nextPageUrl, options);
if (response.getResponseCode() == 200) {
var json = response.getContentText();
var data = JSON.parse(json);
var groupIds = [4929119087759, 6355597919631, 4444649376143, 360002857975, 360002530015, 360002530396, 6439308953103, 360008036995, 5859298250895, 5739337419023];
for (var i = 0; i < data.tickets.length; i++) {
var ticket = data.tickets[i];
if ((ticket.status === "solved" || ticket.status === "closed") && groupIds.includes(ticket.group_id)) {
sheet.getRange(row, 1).setValue(ticket.id);
sheet.getRange(row, 2).setValue(ticket.assignee_id);
sheet.getRange(row, 3).setValue(ticket.created_at);
sheet.getRange(row, 4).setValue(ticket.group_id);
sheet.getRange(row, 5).setValue(ticket.status);
row++;
}
}
nextPageUrl = data.next_page;
} else {
var errorMsg = response.getContentText();
Logger.log(errorMsg);
break;
}
}
}
Code to get ticket metrics:
function getZendeskTicketMetrics() {
var subdomain = 'our-subdomain';
var email = 'our-email-address';
var token = 'our-token';
var url = 'https://' + subdomain + '.zendesk.com/api/v2/ticket_metrics.json?per_page=100&page=';
var options = {
'method': 'get',
'headers': {
'Authorization': 'Basic ' + Utilities.base64Encode(username + ':' + password)
},
'muteHttpExceptions': true
};
var allData = [];
var page = 1;
while (true) {
var response = UrlFetchApp.fetch(url + page, options);
var data = JSON.parse(response.getContentText());
if (data.ticket_metrics.length === 0) {
// No more data to fetch, break out of the loop
break;
}
allData = allData.concat(data.ticket_metrics);
page++;
}
var sheetName = "Metrics";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
}
var numRows = sheet.getLastRow();
if (numRows > 1) {
sheet.getRange(2, 1, numRows - 1, 6).clear(); // clear previous data
}
var row = 2;
sheet.getRange(1, 1, 1, 5).setValues([['Ticket ID', 'Solved at', 'Reopens', 'Replies', 'Full Resolution Time']]);
var values = [];
for (var i = 0; i < allData.length; i++) {
var ticketMetric = allData[i];
values.push([ticketMetric.ticket_id, ticketMetric.solved_at, ticketMetric.reopens, ticketMetric.replies, ticketMetric.full_resolution_time_in_minutes]);
}
sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}
