Problems and Incident counts with Google Apps Script - integration template & guide

21 Comments

  • Brett Bowser
    Zendesk Community Manager

    This is fantastic! Thanks for taking the time to create this and share with everyone :)

    0
  • Heather Rommel
    Community Moderator
    Zendesk Luminary
    The Product Manager Whisperer - 2021

    I agree, this is fantastic! 

    0
  • Justin Fenech

    Thanks! Happy to help.

    0
  • Cyrup

    This is amazing, thanks!

    0
  • Gareth Elsby

    Hey Justin,

     

    Great tool! I've taken a copy of this and I've been having a play around with it. I managed to create one to pull down organisations and support addresses. They work pretty good!

    function onOpen(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menu = [
    {name: "Configure", functionName: "zendeskConfigure"},
    {name: "Get Agents", functionName: "getAgents"},
    {name: "Get Problems", functionName: "getProblems"},
    {name: "Get Organisations", functionName: "getOrganisations"},
    {name: "Get Support Addresses", functionName: "getSupportAddresses"},
    {name: "Get Triggers", functionName: "getTriggers"}
    ];
    ss.addMenu("Zendesk", menu);
    }

    // Get all organisations
    function getOrganisations() {
    var resultId = 'organizations';
    var sheet = 'Organizations';
    var params = 'organizations.json';
    var fields = [ "id", "name", "domain_names","organization_fields" ];
    var result = getClient().request(params);

    spreadsheetDBSave(result, resultId, sheet, fields);
    }

    // Get all Support addresses
    function getSupportAddresses() {
    var resultId = 'recipient_addresses';
    var sheet = 'Support Addresses';
    var params = 'recipient_addresses.json';
    var fields = [ "id", "name", "email", "default", "forwarding_status" ];
    var result = getClient().request(params);

    spreadsheetDBSave(result, resultId, sheet, fields);
    }

     

    Could you think of a way to handle pagination on Organisations? I could only pull down the first 100 :(

     

    Thanks!

    0
  • Justin Fenech

    Hi Gareth,

    Apologies for the delayed reply. Pagination is something I really should have worked in, but I no longer have access to a zendesk account, and I'm waiting for my Zendesk developer account to be activated. If someone from Zendesk is reading this, hint hint.

    In the interim something like the following should work if you add it to the Libs/Client.gs file.
    This has not been tested and I am going off memory of how the api data was structured. 
    I have also tried to keep this compatible with the spreadsheetDBSave helper function by passing in the resultId and structuring the results data accordingly. 

    Client_.prototype.requestAll = function (uri, resultId) {
    var userProperties = this.authPropertyStore_;

    if (this.authService_) {
    if (this.authService_.hasAccess()) {
    var url = 'https://'
    .concat(userProperties.getProperty('subdomain'), '.zendesk.com/api/v2/', uri);
    var results = {};
    results[resultId] = [];

    while(url) {
    var response = UrlFetchApp.fetch(url, {
    headers: {
    Authorization: 'Bearer ' + this.authService_.getAccessToken()
    }
    });
    var result = JSON.parse(response.getContentText());
    results[resultId].push(result[resultId]);
    url = result.next_page;
    }

    return results;
    } else {
    this.zendeskAuth();
    }
    }
    }

    You should be able to call this new function like so:

    var result = getClient().requestAll(params, resultId);

    Hope this helps, let me know how you go.

    Justin

     

    0
  • Brett Bowser
    Zendesk Community Manager

    Thanks for sharing this Justin!

    I did want to follow-up with you as well regarding your developer account.

    I checked internally and as far as I can tell your account should be up and running.

    Can you confirm on your end?

    0
  • Justin Fenech

    Hi Brett - always happy to help out when I have the time!

    I just logged in and you are correct; there is no longer a trial message and the account appears to be working correctly. Thank you for taking the time to look into this for me.

    Justin

    0
  • Jules Winckelmann

    Hello everyone.

    First of all, thank you so much for sharing this, this can be a great workaround.

    I have three questions though:

    - When trying to get the problem data, I end up with 100 rows only.
    Can you guys let me know how to squeeze this limitation?

    - When trying to Authenticate, nothing happens after clicking the Auth button.
    A colleague from the IT tried himself and it worked straight away, but I can't figure why. Do I need to be the main Zendesk administrator to do that, or am I just doing something wrong?

    - Additionally, I want to add a custom ticket field to be able to sort the results by products. Do I just need to add the field ID number in the var fields line?

    var fields = [ "id", "subject", "status", "incident_count", "created_at", "updated_at", "FIELD ID" ];

    Thanks in advance for your help !!!

    Edouard

    0
  • Gareth Elsby

    Justin,


    Sorry, I gave that a try but couldn't get it to work for me. Did you ever get a chance to have another look at this after your developer account was approved?

    0
  • Justin Fenech

    @Edouard - You should be able to get more than 100 rows if you use the requestAll code that I provided in an earlier reply.

    Regarding Authentication, make sure that the browser isn't blocking anything. I had some trouble using brave browser with the shields on. 

    Custom fields can be added as you describe. Just make sure you use the correct syntax as per the Zendesk developer documentation. 

    @Gareth - I completely forgot, I'm sorry. All caught up in my new job - I will try to find some time this week to get it sorted. 

    0
  • Jules Winckelmann

    Hello guys.

    • Regarding Authentication, make sure that the browser isn't blocking anything. I had some trouble using brave browser with the shields on.

    Indeed the authenticate window couldn't pop out, I had to copy the button link and open it in a new tab.

    • @Edouard - You should be able to get more than 100 rows if you use the requestAll code that I provided in an earlier reply.

    I think there is a problem when parsing the results. I end up with the following (see pic below)

    I asked one of my colleague to look into it, but he couldn't figure what's wrong.

    • Custom fields can be added as you describe. Just make sure you use the correct syntax as per the Zendesk developer documentation. 

    For each of my problem tickets, I need to add a custom field, here the product.
    I tried to add this custom fields in the var fields, but without success.

    I first tried with the ticket filed name:

    And with the ticket filed ID as well:

    But I always end with "undefined" values for each of my problem tickets...
    Anyone know how shall I proceed?

    Thanks again for your help.

    Edouard

    0
  • Justin Fenech

    @Edouard - I'm not sure why you're getting undefined, does this happen when you use the default functions?

    For custom fields I believe the format is something like "custom_field_123456789"

     

    @All I have updated the Zendesk Template so that requesting problems now grabs all of them rather than just the first 100.

    0
  • Jules Winckelmann

    I have updated the Zendesk Template so that requesting problems now grabs all of them rather than just the first 100.

    Yay this works Justin ! Thank you so much !

    I'm not sure why you're getting undefined, does this happen when you use the default functions?
    For custom fields I believe the format is something like "custom_field_123456789"

    Well, I'm afraid it's not working either, I've got the same "undefined" values for each row, even though I tried with "custom_field_name" or with "custom_field_id"
    I checked the API developer documentation and I could find any related info...

    Another question @Justin Does your app allows to extract data to build reportings?
    Or is it just about monitoring in Zendesk interface?

    Thanks again for your precious help 

    Edouard

    0
  • Justin Fenech

    @Edouard - I have updated the sheet so that it will handle the custom fields. The format "custom_field_123456789" should now work.

    0
  • Jules Winckelmann

    Justin, is there any address where I can send love right now?
    You are amazing, thank you so much.

    0
  • Justin Fenech

    An email address?

    I have released an application on the Zendesk market place called Problem Tracker. If you would like to show your support you could sign up for that temporarily. I would definitely love some feedback on the app.

    0
  • Kevin Fahy

    Hello,

    Is there any plan to add a column for this in zendesk views, or a simpler workaround than a google apps script integration?

    Don't get me wrong, many many thanks to Justin, who has spent a lot of time and was nice enough to share, but I simply want to make sure I am not missing something here. All we want to do is look in our view and see which problems actually have incidents attached, and if possible how many incidents. We have a small volume of tickets, and and we separate our views into Tier 1, 2, & 3 so that by the time tickets get to T3 there are so few that implementing this solution/workaround is less likely than just clicking on each ticket individually to see if and how many incidents are attached

     

    0
  • Devan La Spisa
    Zendesk Community Manager

    Hello Kevin,

    At this time, there is anything we can announce that pertains to your request and our current development roadmap. However, I would recommend sharing your feedback on our Support Feedback forum so our developers can consider your suggestion for potential future updates.

    Support Product Feedback

    Best regards. 

    0
  • Justin Fenech

    Hey Kevin - have developed a similar app that works from the sidebar here: https://www.zendesk.com/apps/support/problem-tracker/

    Would love some feedback.

    Justin

    0
  • Nicole Saunders
    Zendesk Community Manager

    Thanks for sharing your solution, Justin! 

    0

Please sign in to leave a comment.

Powered by Zendesk