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

17 Comments

  • Brett - Community Manager
    Comment actions Permalink

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

    0
  • Heather Rommel
    Comment actions Permalink

    I agree, this is fantastic! 

    1
  • Justin
    Comment actions Permalink

    Thanks! Happy to help.

    0
  • Cyrup
    Comment actions Permalink

    This is amazing, thanks!

    0
  • Gareth Elsby
    Comment actions Permalink

    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
    Comment actions Permalink

    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 - Community Manager
    Comment actions Permalink

    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
    Comment actions Permalink

    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
  • Edouard Madeuf
    Comment actions Permalink

    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
    Comment actions Permalink

    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
    Comment actions Permalink

    @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
  • Edouard Madeuf
    Comment actions Permalink

    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
    Comment actions Permalink

    @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
  • Edouard Madeuf
    Comment actions Permalink

    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
    Comment actions Permalink

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

    1
  • Edouard Madeuf
    Comment actions Permalink

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

    0
  • Justin
    Comment actions Permalink

    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

Please sign in to leave a comment.

Powered by Zendesk