Connecting to Google Docs
Connect to the Snipe-IT API from Google Docs
This is an example of a script that that can be used to sort assets by department. It takes a list of department names from column 1 of the sheet, and searches all the users within each department and spits out a list of assets with their url to a tab with that department name.
- Create a new Google sheet.
- Open script editor and paste in the code from below.
- At the top, enter your Snipe-it server URL and API key.
- Your API key can be created by choosing YourName > Manage API keys from the top right menu in Snipe-IT.
- In column 1, enter department names each in their own box.
- Create a new tab for each department that matches the name.
- Quit the sheet and relaunch. You should see a new menu option for Run Script.
- Select the Department names from column 1 and run the script. It will write asset urls to the appropriate tabs based on user's departments and the assets assigned to them.
The code is basically JavaScript but is specific to Google for how it accesses and writes to a sheet and makes web requests. If you are familiar with JavaScript, you should be able to manipulate the functions to make calls to the API for information on Hardware, Licenses, and anything else accessible by the API (instead of users from this example).
//SETUP
serverURL = 'https://yourserver.yourdomain.com/';
apiKey = 'your_long_api_key_here'
function onOpen(e) {
createCommandsMenu();
}
//Create a menu option on the sheet to run the runGetAssetsByDepartment function
function createCommandsMenu() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Run Script')
.addItem('Get Assets By Department', 'runGetAssetsByDepartment')
.addToUi();
}
function testGetUsersByDept(){
var output = getUsersByDept("The Office of Market & Comm")
return output
}
//Get All Users for a department
//Returns a list of user IDs
function getUsersByDept(department) {
//Get the number of rows to use for the limit
//If you don't do this, snipe may return an incomplete list
var url = serverURL + 'api/v1/users?search=' + department;
var headers = {
"Authorization" : "Bearer " + apiKey
};
var options = {
"method" : "GET",
"contentType" : "application/json",
"headers" : headers
};
var response = JSON.parse(UrlFetchApp.fetch(url, options));
var limit = response.total
//Now run the request again with the limit
var url = serverURL + 'api/v1/users?search=' + department + '&limit=' + limit;
var headers = {
"Authorization" : "Bearer " + apiKey
};
var options = {
"method" : "GET",
"contentType" : "application/json",
"headers" : headers
};
var response = JSON.parse(UrlFetchApp.fetch(url, options));
var rows = response.rows;
var userIDs = []
for (var i=0; i<rows.length; i++) {
var row = rows[i];
//if (row.department.name == department) {
var userID = row.id
userIDs.push(userID)
Logger.log(userIDs)
//}
}
return userIDs
}
function testGetAssetsByUser(){
getAssetsByUser("902")
}
//Get assets for a user by id
//Returns a list of assets by id
function getAssetsByUser(userID) {
var url = serverURL + 'api/v1/users/' + userID + '/assets';
var headers = {
"Authorization" : "Bearer " + apiKey
};
var options = {
"method" : "GET",
"contentType" : "application/json",
"headers" : headers
};
var response = JSON.parse(UrlFetchApp.fetch(url, options));
var rows = response.rows;
var assets = []
for (var i=0; i<rows.length; i++) {
var row = rows[i];
if (row.category.name == "Laptop" || row.category.name == "Desktop" || row.category.name == "2-in-1") {
var asset = row.id
assets.push(asset)
}
}
return assets
}
function testGetAssetsByDepartment(){
getAssetsByDepartment("The Office of Market & Comm")
}
//Get all Laptops and Desktops assigned to users of a specific Department
function getAssetsByDepartment(department){
var assets = []
var userIDs = getUsersByDept(department)
for (var i=0; i<userIDs.length; i++) {
var userID = userIDs[i]
var assetIDs = getAssetsByUser(userID)
for (var j=0; j<assetIDs.length; j++){
var assetID = assetIDs[j]
assets.push(assetID)
}
}
var ss = SpreadsheetApp.getActive();
var assetsSheet = ss.getSheetByName(department)
//var lastRow = assetsSheet.getLastRow();
//if (lastRow != 0){
assetsSheet.clear()
//}
for (var k=0; k<assets.length; k++){
var asset = assets[k]
assetsSheet.appendRow([serverURL + "/hardware/" + asset])
}
}
function runGetAssetsByDepartment(){
//Get departments from selected cells
var ss = SpreadsheetApp.getActive();
var sh = ss.getActiveSheet();
var values = ss.getActiveRange().getValues()
var departments = []
for (var i=0; i<values.length; i++){
var department = values[i]
departments.push(department)
}
//Run getAssetsByDepartment with each department retrieved above
for (var j=0; j<departments.length; j++){
var department = departments[j]
getAssetsByDepartment(department)
}
}
Thanks to @bricelabelle for this example script and documentation!
Updated 7 months ago