sbkinney · November 9, 2012 01:47 · Nov 9, 2012 · Nov 9, 2012
diff --git a/Google Docs import via Zendesk API → import_sat_via_Zendesk_API.js b/Google Docs import via Zendesk API → import_sat_via_Zendesk_API.js
diff --git a/Google Docs import via Zendesk API b/Google Docs import via Zendesk API
@@ -0,0 +1,136 @@
+/* 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);
+}