Last active
February 17, 2021 16:17
-
-
Save muness/295fcf21d4937425718eddc54fbfc223 to your computer and use it in GitHub Desktop.
BambooHR API client for Google Sheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// global vars that are taken from userProperties | |
var BAMBOOHR_SUBDOMAIN = '<fill this out>'; | |
// add a custom menu to enter API credentials, so they don't need to be saved on the script | |
function onOpen() { | |
// Add API credentials menu to sheet | |
SpreadsheetApp.getUi() | |
.createMenu("BambooHR API Credentials") | |
.addItem("Set Credentials", "setCred") | |
.addItem("Remove Credentials", "deleteCred") | |
.addToUi(); | |
} | |
/** | |
* Get a Custom Report from BambooHR directly in Google Sheets using the listed fields | |
* | |
* @return The data needed for the gender and ethnicity report | |
* @customfunction | |
*/ | |
function BambooHR(sheetWithFields = "Fields"){ | |
if (checkAPIKey() == false) { | |
return "Set BambooHR API key and then remove/re-add the formula"; | |
} | |
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetWithFields); | |
var fieldsToFetch = sheet.getRange(2, 1, sheet.getLastRow(), 1).getValues().join().split(',').filter(Boolean);; | |
Logger.log(fieldsToFetch); | |
return(bambooHRAPI(fieldsToFetch, "reports/custom", 'post')); | |
} | |
/** | |
* Get a list of fields available from BambooHR | |
* | |
* @return List of fields | |
* @customfunction | |
*/ | |
function BambooHRAvailbleFields() { | |
if (checkAPIKey() == false) { | |
return "Set BambooHR API key and then remove/re-add the formula"; | |
} | |
var output = bambooHRAPI([""], "meta/fields", 'get'); | |
Logger.log(output); | |
// output = '<?xml version="1.0"?> <fields> <field id="4175" type="date">Accrual Level Start Date</field></fields>'; | |
var xml = output.flat().filter(Boolean).join('\n'); | |
var document = XmlService.parse(xml); | |
fields = document.getRootElement().getChildren('field'); | |
var fieldList = [['id', 'field', 'alias']]; | |
for (var i = 0; i < fields.length; i++) { | |
var field = fields[i]; | |
var entry = [extractXMLElementAttribute(field, 'id'), field.getText(), extractXMLElementAttribute(field, 'alias')]; | |
fieldList.push(entry); | |
} | |
return(fieldList); | |
} | |
function extractXMLElementAttribute(xmlElement, attribute) { | |
var aliasAttribute = xmlElement.getAttribute(attribute); | |
if (aliasAttribute != null) { | |
return aliasAttribute.getValue(); | |
} else { | |
return null; | |
} | |
} | |
function checkAPIKey() { | |
var bamboohr_api_key = PropertiesService.getScriptProperties().getProperty("BAMBOOHR_API_KEY"); | |
if(bamboohr_api_key == null) { | |
return false; | |
} | |
return true; | |
} | |
function bambooHRAPI(fieldsToFetch, reportUri, httpMethod) { | |
var bamboohr_api_key = PropertiesService.getScriptProperties().getProperty("BAMBOOHR_API_KEY"); | |
const url = 'https://api.bamboohr.com/api/gateway.php/' + BAMBOOHR_SUBDOMAIN + '/v1/' + reportUri + '?format=csv'; | |
const formData = { | |
"title": "Custom report", | |
"filters": { | |
"lastChanged": { | |
"includeNull": "no", | |
"value": "2010-01-01T00:00:00Z" | |
} | |
}, | |
"fields": fieldsToFetch | |
}; | |
const headers = { | |
"Authorization": "Basic " + Utilities.base64Encode(bamboohr_api_key + ':x') | |
}; | |
const options = { | |
'method' : httpMethod, | |
'contentType': 'application/json', | |
'headers': headers, | |
}; | |
if (httpMethod == 'post') { | |
options['payload'] = JSON.stringify(formData); | |
} | |
const response = UrlFetchApp.fetch(url, options); | |
return Utilities.parseCsv(response); | |
} | |
// all API credential stuff | |
// We're using scriptProperties to store api creds script wide. | |
// Change this to userProperties to make it userSpecific. | |
// set credentials via prompt | |
function setCred() { | |
var ui = SpreadsheetApp.getUi(); | |
var bamboohr_api_key_input = ui.prompt("Set your BambooHR API key", "API Key:", ui.ButtonSet.OK_CANCEL); | |
PropertiesService.getScriptProperties().setProperty("BAMBOOHR_API_KEY", bamboohr_api_key_input.getResponseText().trim()); | |
ui.alert("Key set", "The API key has been set for this spreadsheet", ui.ButtonSet.OK); | |
} | |
// delete credentials from scriptProperties | |
function deleteCred() { | |
PropertiesService.getScriptProperties().deleteAllProperties(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment