Google Hire data export to useful CSV

google-hire-to-csv.js

// This is free and unencumbered software released into the public domain.

const csv = require('fast-csv')

const dir = process.argv[2]
if (!dir) {
  throw new Error('usage: export.js <export-dir>')
}

// Raw data from Google Hire export
const data = {
  stages: require(`./${dir}/ProcessStage.json`),
  closeReasons: require(`./${dir}/Hiring_JobApplicationCloseReason.json`),
  positionInfo: require(`./${dir}/Hiring_JobPositionBasicInfo.json`),
  applicationInfo: require(`./${dir}/Hiring_JobApplicationBasicInfo.json`),
  applications: require(`./${dir}/JobApplication.json`),
  chatMessages: require(`./${dir}/ChatEventMessage.json`),
  candidates: require(`./${dir}/Candidate.json`),
  personas: require(`./${dir}/CompanyPersona.json`),
  emails: require(`./${dir}/EmailContact.json`),
}

// Hard code a list of people that appear, such as for sources
const users = {
  'P_AAAAAAEAAA9Gk3XXXXXXXX': 'Alice',
  "P_AAAAAAEAAA9GVEXXXXXXXX": 'Bob',
  "P_AAAAAAEAAA9NeXXXXXXXXX": 'Charlie',
}

// SETUP ------------------------------------------------------------------
// 
// Build various maps of OID -> data

const build = (dataset, fn) => {
  const ret = {}
  for (const obj of dataset) {
    const value = fn ? fn(obj) : obj
    if (value !== false) ret[obj.__oid] = value
  }
  return ret
}

const stages = build(data.stages, o => o.name)
const closeReasons = build(data.closeReasons, o => o.text)
const applicationInfo = build(data.applicationInfo,
  o => ({
    docs: o.resumeDocs.map(d => d.__oid).concat(o.otherDocs.map(d => d.__oid)),
    url: o.profileUrl
  }))
const candidates = build(data.candidates)
const personas = build(data.personas)

const chatMessages = {}
for (const obj of data.chatMessages) {
  const key = obj.timeline.__oid
  if (!chatMessages[key]) chatMessages[key] = []
  chatMessages[key].push(obj)
}

const positionNames = {}
for (const obj of data.positionInfo) {
  positionNames[obj.parentJobPosition.__oid] = obj.jobTitle
}

const emails = {}
for (const obj of data.emails) {
  emails[obj.parentForAcl.__oid] = obj.emailAddress
}

// MAIN -------------------------------------------------------------------

// Replace references with names
const cleanMessage = str => str.replace(/@([-\w]+)/g,
  (_, oid) => '@' + (users[oid] || '???'))

const out = csv.format({ headers: true })
out.pipe(process.stdout).on('end', process.exit)

for (const obj of data.applications) {
  const messages = chatMessages[obj.channel.__oid]
  const persona = personas[obj.applicant.__oid]
  const appInfo = applicationInfo[obj.basicInfo.__oid]
  const closeReason = obj.closeReason ? closeReasons[obj.closeReason.__oid] : null
  const positions = obj.positions.map(p => positionNames[p.__oid])

  // Filter only on the two Software Engineer jobs we posted
  const job = positions.join(', ')
  if (!/Software Engineer/.test(job)) continue

  let stage = null
  try { stage = stages[obj.currentStage.__oid] } catch (e) {}

  let email = null
  try { email = emails[persona.records.Candidate.__oid] } catch (e) {}
  
  const name = persona.personName.displayName
  const status = obj.status.split('.').slice(-1) + (closeReason ? ` (${closeReason})` : '')
  const source = users[obj.creator.__oid]
  const notes = messages ? messages.map(m => cleanMessage(m.message)).join('\n\n---\n\n') : null
  const log = messages ? messages.map(m => `${m.__validTime}`).join('\n') : null
  const { docs, url } = appInfo

  let linkedin = null
  let github = null
  let other = null
  if (/linkedin/.test(url)) linkedin = url
  else if (/github/.test(url)) github = url
  else other = url

  out.write({
    Name: name,
    Job: job,
    Status: status,
    Source: source,
    'Sourcing Notes': notes,
    Log: log,
    Email: email,
    Phone: null,
    Resume: docs,
    LinkedIn: linkedin,
    GitHub: github,
    Other: other,
  })
}

out.end()

Ian. Just sent you an email. This is exactly what I have been looking for

Hey, man. This is similar to the code that lets you sort a Google spreadsheet. I've been working with Google spreadsheet for about 6 years and I know a lot. I always liked the Google spreadsheet, unlike salesforce, because it is more functional and convenient. But once I had to switch to salesforce because the boss wanted it, he had to send some report to the client in salesforce. I didn't know how to work in salesforce and my Google spreadsheet had a lot of useful data, a whole database. I went to read this article https://blog.coupler.io/salesforce-data-export/ and found out that with the help of a coupler, you can link a Google table to salesforce. I did so and in a short time, I had everything I needed.