Zendesk Update Users API From Google Sheets | Community
Skip to main content

Zendesk Update Users API From Google Sheets

  • April 4, 2022
  • 1 reply
  • 0 views

Fredm

I'm going to start by saying it's immensely frustrating half knowing how to do something but never quite being able to finish; this is another one of those projects for me.

Scenario: Using a Google Sheet and Apps Script I am attempting to update several User records in Zendesk using their https://developer.zendesk.com/api-reference/ticketing/users/users/#update-many-users data-science

I think i probably have most if it right (i stand to be corrected of course) with the following script however I just cannot get it to update any records. I suspect it might be to do with how the array is presented (an area I sadly don't know enough about).

function updateManyUsers(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
 var [headers, ...rows] = sheet.getDataRange().getValues();
 var data = {}
 var items = []
 rows.forEach(function(r) {
   var obj={}
   r.forEach(function (c, j) {
     obj[headers[j]] = c
   })
    var data = {}//moved
   data['users'] = obj // moved this inside your loop
   items.push(data) // pushed the object into the items array
 })
 
 Logger.log("Log JSON Stringify Items: " + JSON.stringify(items))
 
 items.forEach(function(i) {   // added this to loop over objects in items
 var url = 'https://itsupportdesk1611575857.zendesk.com/api/v2/users/update_many.json'; //https://developer.zendesk.com/api-reference/ticketing/users/users/#update-user
 var user = 'myemailaddresshere/token';
 var pwd = 'mytoken';
 var options = {
     'method' : 'PUT',
     'headers': {
       'Authorization': "Basic " + Utilities.base64Encode(user + ':' + pwd)
     },
     'payload' : JSON.stringify(i),
     'contentType': 'application/json',
     'muteHttpExceptions': true
 };
 UrlFetchApp.fetch(url, options);
 Logger.log(i)
 var response = UrlFetchApp.fetch(url, options);
   Logger.log(response);
   
})

} 

I've gone through as much as I can following the documentation, I know i had the end points incorrect and the method(?) too (set to Post instead of Push). I have gone through varying error messages that I have tried to act upon and this is my current one: 

This is an image of the data in my sheet 

Suplimental: In order to get better at this i would like to put myself on a learning path but am unsure what the path is; most of my automation work and scripting is done using Google Apps script so would people recommend a JavaScript course? I alter between that and Python not knowing what would suit me best to get a better understanding of this kind of issue.

Many thanks in advance.

1 reply

Jack23
  • April 4, 2022

Without knowing much about the structure of "items" in your code snippet, which i'm assuming is an array of user objects that are rows inside your sheet it's hard to say what the problem might be.

Would you be able to post an example of an item in the items.forEach loop? It could be a problem with data structure and that would be where i'd start debugging first :) 

As for learning more, App Script is built on JavaScript so I think you'll find picking up JavaScript quite easy, I'd recommend maybe watching some videos on YouTube about making some basic node apps (maybe search for 'consume api in node') and then maybe try to self teach by interacting with the Zendesk API using node, maybe make a few apps that automatically create tickets, update ticket fields etc to get the hang of the basics and then move on to more complex things when you feel comfortable.