The Scenario
Referral bonuses for the year are due to payroll by next Wednesday. HR needs a tracking sheet with every referred candidate who was hired in the past 12 months — opportunity ID, candidate name, referrer name, referrer email, and hire date. There are 120 opportunity IDs in a list that someone compiled from Lever manually. The referral details themselves are sitting in Lever on individual opportunity records.
The bad version:
- Click into the first opportunity, find the referral section, note the referrer's name and email
- Write those down in the tracking sheet, move to the next opportunity
- By row 30 you are copy-pasting names and realizing the referral field is sometimes empty even for people you know were referred
Payroll has a hard deadline. The referral bonus amount per person is not trivial. This data needs to be right.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet. It reads the opportunity IDs in Column A and pulls referral data for each one from Lever — referrer name and email — writing the result directly into your tracking sheet.
Open your referral bonus tracking sheet with opportunity IDs in Column A and type:
For each opportunity ID in Column A, fetch the referrals from Lever and write the referrer's name and email into columns B and C — one row per referral
What You Get
- Column B: referrer's full name as it appears in Lever
- Column C: referrer's email address
- Opportunities with no referral on record produce no row (or a blank row depending on your follow-up prompt)
- Multiple referrals for one opportunity each get their own row with the opportunity ID repeated
What If the Data Is Not Quite Ready
Some referral records have no email on file
For each opportunity ID in Column A, fetch Lever referrals and write referrer name and email into columns B and C — for rows where email is null, write 'email missing' in Column C so they can be chased manually
You need candidate name included alongside referrer details
List all referrals for the Lever opportunities in Column A and produce a tracking sheet with opportunity ID, candidate name, referrer name, and referrer email in columns A through D
Some opportunity IDs have no referral and should be flagged
For each opportunity ID in Column A, fetch Lever referrals. Write referrer name and email into columns B and C. For opportunities with no referral at all, write 'No referral on record' in Column B so payroll knows which hires to verify manually
Full referral export, dedup, and bonus-ready summary in one shot
For each opportunity ID in Column A, fetch Lever referrals and write opportunity ID, candidate name, referrer name, and referrer email into columns A through D. Then deduplicate by referrer email in Column C — if the same referrer appears more than once, keep all rows but add a count in Column E showing how many times that referrer appears so payroll can calculate the bonus total per person
One prompt builds the tracking sheet and the bonus rollup simultaneously.
Try It
Get the 7-day free trial of SheetXAI and open your referral bonus tracking sheet with Lever opportunity IDs, then ask it to pull referrer details for all your hired candidates in one pass. See also the spoke on exporting offer data or return to the Lever integration overview.