Daily candidate import from Google Sheets to SourceWhale

Every weekday at 7am, load newly sourced candidates from my Google Sheets sourcing sheet into SourceWhale and enrol them into their outreach campaign, without ever adding the same person twice. Build this as a code workflow on a cron trigger.

Start by reading the sourcing sheet with the Google Sheets Get Values operation, pulling the whole data range in one pass using A1 notation (for example Sourcing!A2:H1000). The sheet holds one candidate per row, with columns for first name, last name, email, company, job title, LinkedIn URL, Campaign, and Status. There is no pagination to deal with: the requested range comes back in a single response.

Process only the rows that have an email address AND a blank Status cell. A row whose Status is already filled in was handled on an earlier run and must be skipped. A row with no email address must be left completely untouched with its Status still blank, so a person can fix the missing address by hand and the next run will pick it up.

For each row to process, look the person up in SourceWhale using Search Candidates, passing that row's email address. SourceWhale matches on a single identifying field, so dedupe on email only and never fall back to matching by name, since two different candidates can share a name. If the search returns a candidate, that person is already in SourceWhale: do not add them, and record Skipped duplicate for the row.

If the search returns nothing, create them with SourceWhale Add Candidates, mapping first name, last name, email, company, job title, and LinkedIn URL from the sheet columns. Add Candidates supports campaign enrolment directly, so enrol the candidate into the campaign named in that row's Campaign column as part of the same call rather than making a second request. At the start of the run, call List Campaigns once and build a case-insensitive lookup from campaign name to the identifier Add Candidates expects, then reuse that lookup for every row. If a row's Campaign name matches no campaign, treat the row as an error rather than adding the candidate with no campaign.

After each row is handled, write the result back to that same row with the Google Sheets Update Values operation, targeting only that row's Status cell and the timestamp cell beside it. Set Status to Added, Skipped duplicate, or Error, together with the time the row was processed. This write-back is what makes the workflow safe to run daily: it is the record that stops the same person being re-imported and re-emailed tomorrow.

Handle failures per row, not per run. If a lookup or an add fails for one candidate, mark that row Error with a short reason and the timestamp, then continue with the remaining rows so one bad row never blocks the import. Note that SourceWhale returns a 401 both for an invalid API key and for a key that lacks access, so do not treat a 401 as a transient failure worth retrying. Keep Google Sheets writes to roughly one per second to stay inside its per-user write quota.