How to connecting Google app scripts to Jamf

[Habib-Rahman]

Can I have some help, please? I am using the google apps script and I wanted our URL for JAMF we wanted to connect with our username and password so we can auth to JAMF to help us get the data into the Google app script. I wanted to get some data from the app script I can use this data on google data studio, please? Thank you

[MatG]

Habib,
Try this

Copy the below and add to your Apps script.
Change the DOMIAN to your domain and either alter the API call to what you want or run this and it bring in all devices basic info but take a few minutes to run depending on how big your Jamf is.

For the Basic auth, swap ENCODEDSTRINGHERE with your login/pwd encoded which you can do on this site
https://www.labnol.org/urlfetch/ on the Authorization tab select BasicOAuth enter your API username and pwd, the encoded string will show, its genetated instantly so nothing is transmitted,
Obviously give that API account minimum permissions, copy/paste in the string.

Save the script, press Run, first use you will be asked to grant permissions. Let it run, once finished go back to your sheet and you should have Jamf data.

function JamfGetJamfData() {

//Query jamf 
  var url = 'https://DOMAIN.jamfcloud.com/JSSResource/computers/subset/basic';
  var jamfXml = UrlFetchApp.fetch(url, {
		"method": "GET",
		"headers": {
			"Authorization": "Basic ENCODEDSTRINGHERE",
			"Content-Type": "application/xml"
		},
	}).getContentText();

  var document = XmlService.parse(jamfXml)
  var root = document.getRootElement()
  var computers = root.getChildren("computer")
  var list=[]

  computers.forEach(function(item){
    var computer=[]
    item.getChildren().forEach(function(details){
      computer.push(details.getValue())
    })
    list.push(computer)
  })

  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(sheet.getLastRow()+1,1,list.length,list[0].length).setValues(list)

}

[Habib-Rahman]

Hi @MatG 

OMG you are amazing ⭐️ thank you for the help I have connected most of the parts up as you have said however am getting this error when running the code: 

Error

TypeError: Cannot read property 'getRange' of null

JamfGetJamfData

Code.gs:30

[Habib-Rahman]

@MatG error on this line of code: 

  sheet.getRange(sheet.getLastRow()+1,1,list.length,list[0].length).setValues(list) 

[MatG]

interesting as I don't get an error

[Habib-Rahman]

HI @MatG 

Okay so I hade to use this code: sheet.appendRow ([JSON.stringify(computer)])

To make it work.

So for me to get the data some data from smart groups how could i do this would you have a code for me to use for App script to get data from JAMF smart groups?  

[MatG]

you need to first change the API call url to something like
https://DOMAIN.jamfcloud.com/JSSResource/computergroups/id/ID

Change ID to the ID of your smart group so if the ID is 23 then it will be:
https://DOMAIN.jamfcloud.com/JSSResource/computergroups/id/23

you will then need to get the correct parts of the XML returned to add to the sheet.

[Habib-Rahman]

Hi, @MatG am getting 404 errors on the links you have sent over. 

[Habib-Rahman]

ah apology for the above I just read it wrong let me try that now thanks 

[Habib-Rahman]

how do we get the ID of a smart group? 

[MatG]

go to the smart group and its in the URL.

[chelm]

@Habib-Rahman Would you mind posting your final solution?  I got the same error you did.  

[Habib-Rahman]

@chelm This is what I used; however it seems not to work well. The data is connected to JAMF yes however when connecting the Google sheet for the live update to be done automatically having an issue getting that data on sheets.

function NAME() {
  var url = 'JAMF URL';
 var jamfXml = UrlFetchApp.fetch(url, {
    "method": "GET",
		"headers": {
			"Authorization": "",
			"x-api-key": "",
			"cache-control": "",
			"Content-Type": ""
		},
	}).getContentText();
  var ss = SpreadsheetApp.openById('SheetID');
  var sheet = ss.getSheetByName("NAME of Sheet");
  var document = XmlService.parse(jamfXml)
  var root = document.getRootElement()
  // console.log(root)
  var computers = root.getChildren("Name of your smart group")
  var attribues = root.getAttribute("Name of your smart group")
  console.log(attribues)
  var list=[]

  computers.forEach(function(item){
    var computer=[]
    item.getChildren().forEach(function(details){
      computer.push(details.getValue())
    })
    list.push(computer);
 
  })

  var sheet = SpreadsheetApp.openById('Add in your own SheetID');
}

[Habib-Rahman]

HI, @MatG. Sorry to ask again, but some parts of the data work. The JAMF is talking to the Sheet, and it gets the data; however, the issue I am currently having is when you run the search on google ap script, the data is shown below; however, it doesn't capture that data in a google sheet even after connecting it up. 

Would you have any idea what I am doing wrong would you be able to help, please? Thanks 

[chelm]

I'm completely new to App Script and struggling a tiny bit.  It looks to me that everything is ending up in the array element [0] instead of in a new element of the array like one would expect.  

function JamfGetJamfData() {

//Query jamf 
// change the number 6 to the # of your advanced search in the URL
  var url = '<YOUR URL>:8443/JSSResource/advancedcomputersearches/id/6';
  var jamfXml = UrlFetchApp.fetch(url, {
		"method": "GET",
		"headers": {
			"Authorization": "<YOUR AUTH>",
			"Content-Type": "application/xml"
		},
	}).getContentText();
 console.log(jamfXml);

  var document = XmlService.parse(jamfXml);
  var computers = document.getRootElement().getChildren("computers");
  var computersLength = document.getRootElement().getChild("computers").getChild("size").getValue();
  var displayFields = document.getRootElement().getChildren("display_fields");
  var displayFieldSize=document.getRootElement().getChild("display_fields").getChild("size").getValue();
   
  var listComputers=[]
  
  computers.forEach(function(item){
    var computer=[]
    item.getChildren().forEach(function(details){
      computer.push(details.getValue())
    })
    listComputers.push(computer);
  })
  console.log(listComputers[0]);

  var listAttributes=[]

  displayFields.forEach(function(item){
    var displayField=[]
    item.getChildren().forEach(function(details){
      displayField.push(details.getValue())
    })
    listAttributes.push(displayField);
  })
  
  console.log(listAttributes.length);
  
  var sheet = SpreadsheetApp.getActiveSheet()
  sheet.getRange(sheet.getLastRow()+1,1,listComputers.length,listComputers[0].length).setValues(listComputers)
}

The console.log() outputs show that everything is in a single element and they all end up in a single table row.  Anyone know what I am doing wrong?

[chelm]

I had a duh moment as I went to grab a snack, these are multidimensional arrays.  So use this instead:

console.log(listComputers[0][0]);
console.log(listAttributes[0].length);

[Habib-Rahman]

Hi @chelm, I ran what you showed above, and it doesn’t seem to work for me, also where did you define the sheet you want your output to land? Thanks 

[chelm]

I rewrote this because it was annoying me that the JSS ID or ID was not the first column and I always use vlookups on the JSS ID.  Anyway, I am sure there are plenty of people who could do this a lot more gracefully, but this is what I came up with after getting a little help on Stack Overflow. 

function atOpen(){
  JamfGetJamfData("6","test1");
  JamfGetJamfData("6","test2");
};

function JamfGetJamfData(searchid,sheetname) {
//Query jamf 
  var url = '<YOUR URL>:8443/JSSResource/advancedcomputersearches/id/'+searchid;
  console.log(url);
  var jamfXml = UrlFetchApp.fetch(url, {
		"method": "GET",
		"headers": {
			"Authorization": "<YOUR AUTH>",
			"Content-Type": "application/xml"
		},
	}).getContentText();
 
  var document = XmlService.parse(jamfXml);
  var computers = document.getRootElement().getChildren("computers");
  
  var listComputers=[]
  // listLabels is an array used to store the xml element names for a header row
  var listLabels=[]
  
  // loop through and get xml values to the listComputers Array
  // also get the element labels to the listLabels array
  computers.forEach(function (item) {
    item.getChildren().forEach(function (details) {
      if (details.getName() == "computer") {
        var computer = []
        var label = []
        details.getChildren().forEach(function (moreDetails) {
          computer.push(moreDetails.getValue());
          label.push(moreDetails.getName());
        });
        listComputers.push(computer);
        listLabels.push(label);
      }
    });
  });

//console.log(listLabels)
  var listComputersNumAttributes = listComputers[0].length;
  var listComputersSize = listComputers.length;
  
  // shortening the array of name value pairs for lables to just one record
  for (var i=1; i<listComputersSize; i++){
      listLabels.pop();
  };
 
  // finding the attribute that has "id" or the JamfID to move it to the front of the array
  // doing this because it needs to be at the front of the return for vlookups in sheets
  var searchString="id"
  var needle
  for (var i=0; i<listComputersNumAttributes; i++){
      if (searchString == listLabels[0][i]){
        needle = i;
      }
      //console.log(listLabels[0][i]);
  }; 
 
  var newListLabels = [];

  // sort a new array 
  for (var i=0; i<listComputersNumAttributes; i++){
      if (i==0){
        newListLabels[0] = listLabels[0][needle];
      } else if (i!=needle){
        newListLabels[i] = listLabels[0][i];
      } else {
        newListLabels[needle] = listLabels[0][0];
      }
  }; 
  // put the data back in the origional multidimensional array element
  listLabels[0]=newListLabels;
  
  // now do the same sorting on the same index for the computers array
  for (var i=0; i<listComputersSize; i++){
    var computersSort = [];
    for (var y=0; y<listComputersNumAttributes; y++){
        if (y==0){
          computersSort[0] = listComputers[i][needle];
        } else if (y!=needle){
         computersSort[y] = listComputers[i][y];
        } else {
          computersSort[needle] = listComputers[i][0];
        }
    }; 
    listComputers[i]=computersSort;
  };
 
// this is where you write to the spreadsheet.  I am running mine by opening app script while in the spreadsheet
// then it is attached to that spreadsheet and I am using a custom trigger to make it run atOpen.  onOpen was not
// working for me. 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(sheetname)).activate();
 // Add the labels as the first row
  var headerRange = sheet.getRange(1,1,1,listComputersNumAttributes).setValues(listLabels);
  // Now write all of the data in rows. 
  var dataRange = sheet.getRange(2,1,listComputersSize, listComputersNumAttributes).setValues(listComputers);

};

 @Habib-Rahman Hope this helps.  

[Habib-Rahman]

Hi @chelm, thanks for the one question. I have this line of code when I am running it keeps getting the error: 

 sheet.getRange(sheet.getLastRow()+1,1,list.length,list[0].length).setValues(list)
 sheet.appendRow (list)

[chelm]

If sheet.getrange is returning null then these lines must not be working:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(sheetname)).activate();

Are you passing the sheetname to the function?  Is it exactly the same as you are passing with no trialling or leading spaces? The other thing you may want to do is make sure that you are not running the JamfGetJamfData function when you do not want to.  If you run all the code it will run everything in the atOpen and then run JamfGetJamfData with no passed parameters.  Thats about all I can think of:

Ignore the third parameter, I added a boolean parameter to hide the sheets automatically.  I do that like this:

 if (hideSheet){
    sheet.hideSheet();
    console.log("true")
  };

[Habib-Rahman]

Hi @chelm, This is what I am using. I have a smart group which looks for all slack versions on JAMF, and I want all the data to pass throw on a google sheet. I am using the code below; however, I am new to this and trying to understand if I am doing this correctly. Could you please see if I am doing it right, I have used your code to do this and am getting little output; however, I think I am doing something incorrectly. Could you please help me? I would appreciate the help you have given me so far. Thank you    

function Slack() {

  var url = 'URL/JSSResource/computergroups/id/xxx'
  var jamfXml = UrlFetchApp.fetch(url, {
    "method": "GET",
		"headers": {
			"Authorization": "xxx",
			"x-api-key": "xxx",
			"cache-control": "no-cache",
			"Content-Type": "xxx"
		},
	}).getContentText();
  console.log(jamfXml)
  var ss = SpreadsheetApp.openById('xxx');
  var sheet = ss.getSheetByName("Sheet1");
  var document = XmlService.parse(jamfXml)
  var root = document.getRootElement()
  var Slack = root.getChildren("Slack")
  
  var list=[]

  Slack.forEach(function(item){
    var Slack=[]
    item.getChildren().forEach(function(details){
      Slack.push(details.getValue())
    })
    list.push(Slack);
     console.log(JSON.stringify(Slack))
   sheet.appendRow (Slack)
  })

SpreadsheetApp.openById('xxx');
   var sheet = SpreadsheetApp.openById('xxx');
nById("xxx").getSheetByName("Sheet1")

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName(sheetname)).activate();
  
  }