Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save sgerrand/c12799c0a351e39f5c4ead935bf0724e to your computer and use it in GitHub Desktop.
Save sgerrand/c12799c0a351e39f5c4ead935bf0724e to your computer and use it in GitHub Desktop.
This is a method for pulling Zendesk data from a view via the API into a Google Docs spreadsheet
/* This function runs when the spreadsheet is opened and populates a menu option
labelled Zendesk that contains two options (7 days and 30 days) which are tied
to the listed functions
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Pull Satisfaction Data (last 7 days, rolling)",
functionName : "getSatisfactionDataLast7"
},
{
name : "Pull Satisfaction Data (last 30 days, rolling)",
functionName : "getSatisfactionDataLast30"
}];
sheet.addMenu("Zendesk", entries);
};
// This function GETs data from the Zendesk API using the parameters specified
function getAPIdata(apiAction, apiURL, apiAuth) {
// Grab our view data from the API via GET using the basic auth header from above
var response = UrlFetchApp.fetch(apiURL + apiAction,
{
method: "get",
headers: {"Authorization": apiAuth}
});
// Get our view data
var apiData = response.getContentText();
// Convert that view data to a JSON object
var apiObject = Utilities.jsonParse(apiData);
return apiObject;
}
// This passes the ID number for a view containing tickets with bad satisfaction comments over the last 7 days to our function that grabs the satisfaction data and processes it
function getSatisfactionDataLast7() {
getSatisfactionData("31296638");
}
// This passes the ID number for a view containing tickets with bad satisfaction comments over the last 30 days to our function that grabs the satisfaction data and processes it
function getSatisfactionDataLast30() {
getSatisfactionData("31483628");
}
function getSatisfactionData(viewID) {
/* ========================================================================
We need to manipulate our spreadsheets to create a new copy of the
spreadsheet where we'll write our data
======================================================================== */
// Grab the active spreadsheet so we can write some data to it later
var satisfaction_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Select the "Template" sheet and make it active
var template_sheet = satisfaction_spreadsheet.getSheetByName("Template");
satisfaction_spreadsheet.setActiveSheet(template_sheet);
// Make a copy of the active sheet (which is our "Template" sheet) and make it active so we can write data to it later
var data_sheet = satisfaction_spreadsheet.duplicateActiveSheet();
satisfaction_spreadsheet.setActiveSheet(data_sheet);
// Rename that sheet to the start date previously captured from the user
data_sheet.setName("New Data - " + Session.getActiveUser().getEmail());
// Move that spreadsheet to before the "Template" sheet
satisfaction_spreadsheet.moveActiveSheet(template_sheet.getIndex());
/* ========================================================================
Now we're ready to start grabbing data from the API using the
function we previously created.
======================================================================== */
// Setup basic authentication so we can get data via API. We'll pass this data to our API function
var unamepass = "YOUR_EMAIL@YOUR_DOMAIN.COM/token:YOUR_API_TOKEN";
var digest = Utilities.base64Encode(unamepass);
var digestfull = "Basic "+digest;
var zendeskURL = "https://YOURSUBDOMAIN.zendesk.com";
// Grab our data via the API
var viewObject = getAPIdata("/api/v2/views/" + viewID + "/execute.json", zendeskURL, digestfull);
// Isolate the users object so we can map assignee ID to assignee name
var viewUsersObject = viewObject.users;
// Iterate through each record in the users object and build a lookup object so we can map assignee ID to name
var userLookup = {};
for (var j in viewUsersObject) {
userLookup[viewUsersObject[j].id] = viewUsersObject[j].name;
}
// Isolate the rows object
var viewTicketObject = viewObject.rows;
// Let's create an array which will contain all the data we're going to dump into the spreadsheet
var dataTable = new Array();
// Iterate through each record in the API object and output select data to the logs
for (var i in viewTicketObject) {
// Get each of our properties for this ticket
var ticketID = viewTicketObject[i].ticket.id;
var ticketRequesterUpdate = viewTicketObject[i].requester_updated_at.replace("T", " ").replace("Z", " (UTC)");
var ticketAssigneeID = viewTicketObject[i].assignee_id;
var ticketAssigneeName = userLookup[ticketAssigneeID];
// Get the ticket data for each record via another API request
var realTicketObject = getAPIdata("/api/v2/tickets/" + ticketID + ".json", zendeskURL, digestfull);
// Grab the satisfaction comment for this ticket
var ticketSatisfactionComment = realTicketObject.ticket.satisfaction_rating.comment;
var ticketSubject = realTicketObject.ticket.subject;
// Construct the current row of data for this ticket as an array and then push to our parent array
var currentRow = [ticketRequesterUpdate, '=hyperlink("https://support.zendesk.com/tickets/' + ticketID + '", ' + ticketID + ")", ticketAssigneeName, ticketSubject, ticketSatisfactionComment];
dataTable.push(currentRow);
}
/* ========================================================================
With our data pulled and structured as an array, we begin pushing to
the spreadsheet.
======================================================================== */
// Let's count the number of records in the table of data so we know how many rows to add
var number_of_records = dataTable.length;
// Now we append new rows to the data sheet for each record
Logger.log(number_of_records);
data_sheet.insertRowsAfter(2, number_of_records - 1);
// The code below will set the values for range A1:D2 to the values in an array.
data_sheet.getRange(2, 1, number_of_records, 5).setValues(dataTable);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment