Import BambooHR Data to Google Sheets

BambooHR is an online human resources management tool. In this guide, we’ll walk through how to pull data from the BambooHR API directly into Google Sheets, using the API Connector add-on for Sheets. We’ll first get an API key from BambooHR, and then set up a request to pull in HR data to your spreadsheet.

PART 1: GET YOUR BAMBOOHR API KEY

  1. While logged into your BambooHR account, click your Profile icon > API keys from the header bar.
    bamboohr-img1
  2. Click the Add New Key button
    bamboohr-img2
  3. Give your API key a name and click Generate Key.
    bamboohr-img3
  4. One last step: Because the BambooHR API requires Basic Authentication, we need to encode our authentication info to base 64. You can do this by entering your your credentials in the format YOUR_API_KEY:x into this form (i.e. your API key, then a colon, and then an ‘x’). Your encoded credentials will appear underneath.The encoding script runs in your browser, and none of your credentials are seen or stored by this site.

    (If you have any problems with the above, please check this post for some alternate methods of encoding your credentials).

PART 2: CREATE YOUR BAMBOOHR API REQUEST URL

We’ll follow the documentation to access a list of job postings.

  • API root: https://api.bamboohr.com/api/gateway.php/
  • Endpoint: /YOUR_DOMAIN/v1/applicant_tracking/jobs
    Endpoint Example: /mixedanalytics/v1/applicant_tracking/jobs

Putting it together, we get the full API Request URL:

https://api.bamboohr.com/api/gateway.php/mixedanalytics/v1/applicant_tracking/jobs

PART 3: PULL BAMBOOHR API DATA INTO SHEETS

We can now enter our values into API Connector and import BambooHR API data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    bamboohr-img5
  3. Under Headers, enter Authorization as your Key, and Basic YOUR_BASE64_ENCODED_STRING as the value:
    AuthorizationBasic YOUR_BASE64_ENCODED_STRING

    Replace YOUR_BASE64_ENCODED_STRING with the base64-encoded string you created in part 1.
    bamboohr-img6

  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Jobs’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Jobs’
  6. Click Run and a moment later you’ll see a list of your BambooHR job postings in your Google Sheet:
    bamboohr-img7

PART 4: BAMBOOHR API + SHEETS NOTES

You can check the documentation for the full list of available API requests,  but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time), substituting in your own domain name:

https://api.bamboohr.com/api/gateway.php/YOUR_DOMAIN/v1/employees/directory
https://api.bamboohr.com/api/gateway.php/YOUR_DOMAIN/v1/meta/fields/
https://api.bamboohr.com/api/gateway.php/YOUR_DOMAIN/v1/meta/users/
https://api.bamboohr.com/api/gateway.php/YOUR_DOMAIN/v1/time_off/whos_out/
https://api.bamboohr.com/api/gateway.php/YOUR_DOMAIN/v1/benefits/settings/deduction_types/all
https://api.bamboohr.com/api/gateway.php/YOUR_DOMAIN/v1/benefitplans

Leave a Comment