The Scenario
Your people analytics lead is running a skills gap analysis before Q3 planning. Leadership wants to know which teams have employees certified in the platforms the company is evaluating, and which teams have nobody with relevant background.
All of that data is in BambooHR as custom employee fields — certifications, technical skills, language proficiencies — set up two years ago during the HRIS migration.
Nobody has ever pulled them in bulk into Excel.
The bad version:
- Open BambooHR, click into employee 1, scroll to custom fields, copy the values.
- Repeat for employee 2.
- After 15 employees realize the custom fields aren't in BambooHR's standard reports. You need to build a custom report for each field group.
- Build three reports. Export three CSVs. Open them in Excel. Write VLOOKUP formulas to join on employee ID. Discover two employees have inconsistent IDs between files.
The skills gap analysis is due in four days. The data gathering just consumed one of them.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It pulls custom employee field data from BambooHR in one call — every field, every employee.
Pull all BambooHR custom employee field values and write them into the Excel 'CustomFields' sheet with columns: employeeID, name, fieldName, fieldValue.
SheetXAI calls BambooHR's employee data endpoint requesting all custom fields, flattens the result to one row per employee-field pair, and writes into the CustomFields worksheet.
What You Get
- One row per employee per custom field value.
- Columns: employeeID, name, fieldName, fieldValue.
- All custom fields included — certifications, skills, whatever was configured.
- Employee IDs are consistent, so joining against the directory is straightforward.
What If the Data Is Not Quite Ready
You only need skills and certification fields, not personal preference fields
Pull BambooHR custom employee field values where the field name contains 'certification' or 'skill' and write employeeID, name, fieldName, and fieldValue into the CustomFields sheet.
You want the data pivoted — one row per employee, one column per field
Pull 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 SkillsMatrix sheet.
You need to count how many employees have each target skill
The TargetSkills worksheet has a list of skills leadership wants to assess.
Pull all BambooHR custom field values for skill-related fields. For each skill in column A of the TargetSkills sheet, count how many employees have that value in BambooHR and write the count in column B.
Full export, pivot, and gap score in one prompt
Pull all BambooHR custom employee field values. Pivot to one row per employee in the SkillsMatrix sheet. For each target skill in TargetSkills column A, add a column indicating whether the employee has it. Add a GapScore column counting how many target skills each employee is missing.
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 workbook, then ask it to pull BambooHR custom field data. Pull the employee directory alongside it if you need department and location context for the gap analysis.