The Scenario
Your people analytics lead has been asked to run a skills gap analysis before the Q3 planning cycle. Leadership wants to know which teams have employees with certifications in the systems the company is planning to adopt, and which teams have nobody with relevant experience.
All of that data is in BambooHR as custom employee fields — certifications, technical skills, language proficiencies, emergency contacts. The fields were set up two years ago when the company migrated from a legacy HRIS.
Nobody has ever exported them in bulk.
The bad version:
- Open BambooHR, click into the first employee's profile, scroll to the custom fields section, copy the values manually.
- Repeat for the next employee. Then the next.
- After 15 employees you realize the custom fields are not visible in BambooHR's standard reports — you would need to build a custom report for each field group, export each one separately, then join them on employee ID.
- Build three custom reports, export three CSVs, open them in separate tabs, write VLOOKUP formulas to join them, discover two employees have inconsistent IDs between reports.
The skills gap analysis is due in four days. The data gathering alone just ate one of them.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the sheet and pulls custom employee field data from BambooHR directly — every field, every employee, one query.
Fetch all custom employee field values from BambooHR for all employees and write employee ID, employee name, field name, and field value into this sheet one row per field value.
SheetXAI calls BambooHR's employee data endpoint requesting custom fields, flattens the result into one row per employee-field pair, and writes them into the sheet. Every custom field comes through — certifications, skills, whatever was configured.
What You Get
- One row per employee per custom field value.
- Columns: employee ID, employee name, field name, field value.
- All custom fields included — no per-field report configuration in BambooHR.
- Employee IDs are consistent across all rows, so joining against other exports is straightforward.
What If the Data Is Not Quite Ready
You only need specific field groups, not all custom fields
The skills gap analysis only needs certifications and technical skills — not emergency contacts or dietary preferences.
Fetch BambooHR custom employee field values where the field name contains 'certification' or 'skill' and write employee ID, name, field name, and value into this sheet.
You want the output pivoted — one row per employee, one column per field
The analysis model expects a wide format: employee ID as the row key, each custom field as its own column.
Fetch all BambooHR custom employee field values and pivot the results so each employee is one row and each custom field is a column. Write the pivoted table into the 'Skills' tab.
You need to cross-reference against a target skills list from another tab
The 'TargetSkills' tab has a column of skill names leadership wants to assess. You want a column indicating how many employees have each target skill.
Fetch all BambooHR custom field values for fields related to skills. For each skill in column A of the 'TargetSkills' tab, count how many employees have that value in BambooHR and write the count in column B.
Full export, pivot, and gap flag in one prompt
Fetch all BambooHR custom employee field values. Pivot to one row per employee. For each target skill in the 'TargetSkills' tab column A, add a column indicating whether the employee has that skill. Add a 'GapScore' column counting how many target skills each employee is missing. Write into the 'SkillsMatrix' tab.
The pattern: the data pull, the pivot, and the gap analysis all happen in one instruction.
Try It
Get the 7-day free trial of SheetXAI and open your workforce capability sheet, then ask it to pull BambooHR custom field data. You can also pull the full employee directory alongside it if you need department and location context for the gap analysis.