javascriptjsonapigoogle-apps-scriptzendesk

Zendesk Update Users API From Google Sheets

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 API.

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: Logger Output

This is an image of the data in my sheet My Sheet Data

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.

Solution

  • From your endpoint in your script, I thought that you might have wanted to use "Batch update". Ref If my understanding is correct, the following sample curl in the official document can be used. Ref

    curl https://{subdomain}.zendesk.com/api/v2/users/update_many.json \
  -d '{"users": [{"id": 10071, "name": "New Name", "organization_id": 1}, {"external_id": "123", "verified": true}]}' \
  -H "Content-Type: application/json" -X PUT \
  -v -u {email_address}:{password}

    If this sample curl command is converted to Google Apps Script using your script, how about the following modification?

    Modified script:

    function updateManyUsers2() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var [headers, ...rows] = sheet.getDataRange().getDisplayValues();
  var users = rows.map(r => {
    var temp = {};
    headers.forEach((h, j) => {
      if (r[j] != "") temp[h] = r[j];
    });
    return temp;
  });
  var url = 'https://itsupportdesk1611575857.zendesk.com/api/v2/users/update_many.json';
  var user = 'myemailaddresshere/token';
  var pwd = 'mytoken';
  var options = {
    'method': 'PUT',
    'headers': {
      'Authorization': "Basic " + Utilities.base64Encode(user + ':' + pwd)
    },
    'payload': JSON.stringify({ users }),
    'contentType': 'application/json',
    'muteHttpExceptions': true
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

    Note:

    Reference: