BambooHR API client for Google Sheets

BambooHR.gs

// 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();
}