Track new and closed jobs at any Greenhouse, Lever or Ashby company in Google Sheets

When a company opens three sales roles and a RevOps lead, it has budget and a plan. Hiring is one of the most honest buying signals there is. The trouble is that it lives on a few hundred careers pages, and nobody checks those by hand.

Most careers pages run on an applicant tracking system (ATS), and the big three, Greenhouse, Lever and Ashby, publish every open job through a public JSON API. No login and no key. This post wires those APIs into a Google Sheet that refreshes itself every morning and logs which jobs are new and which ones closed.

1. Find the board name

Open a company's careers page and click any job. The link tells you the ATS and the board name:

Link looks like	Board entry
`job-boards.greenhouse.io/stripe/...`	`greenhouse:stripe`
`jobs.lever.co/spotify/...`	`lever:spotify`
`jobs.ashbyhq.com/ramp/...`	`ashby:ramp`

Make a tab called Companies and put one board per row in column A, starting at A2.

2. The three APIs

These are the endpoints the script calls. You can open any of them in a browser to see the raw data:

Greenhouse: https://boards-api.greenhouse.io/v1/boards/stripe/jobs
Lever: https://api.lever.co/v0/postings/spotify?mode=json
Ashby: https://api.ashbyhq.com/posting-api/job-board/ramp

Each one returns every open job with an ID, a title, a location and a link. The ID is what makes change tracking possible: if an ID shows up that we haven't seen, the job is new, and if one we've seen disappears, it closed.

3. The script

In the sheet, open Extensions → Apps Script, paste this in, and save.

const HEADERS = ['Company', 'Job ID', 'Title', 'Location', 'Link']; function refreshJobs() { const ss = SpreadsheetApp.getActive(); const boards = ss.getSheetByName('Companies').getRange('A2:A').getValues() .map(function (r) { return String(r[0]).trim(); }) .filter(String); const jobs = []; boards.forEach(function (entry) { const parts = entry.split(':'); try { fetchBoard(parts[0].trim().toLowerCase(), parts[1].trim()).forEach(function (row) { jobs.push(row); }); } catch (e) { console.warn(entry + ': ' + e.message); } }); writeSheet(ss, 'Jobs', HEADERS, jobs); logChanges(ss, jobs); } function fetchBoard(ats, slug) { const urls = { greenhouse: 'https://boards-api.greenhouse.io/v1/boards/' + slug + '/jobs', lever: 'https://api.lever.co/v0/postings/' + slug + '?mode=json', ashby: 'https://api.ashbyhq.com/posting-api/job-board/' + slug, }; if (!urls[ats]) throw new Error('unknown job board "' + ats + '"'); if (ats === 'lever') Utilities.sleep(1000); // Lever's robots.txt asks for 1 request a second const res = UrlFetchApp.fetch(urls[ats], { muteHttpExceptions: true }); if (res.getResponseCode() !== 200) throw new Error('HTTP ' + res.getResponseCode()); const data = JSON.parse(res.getContentText()); if (ats === 'greenhouse') { return data.jobs.map(function (j) { return [slug, String(j.id), j.title, (j.location && j.location.name) || '', j.absolute_url]; }); } if (ats === 'lever') { return data.map(function (j) { return [slug, j.id, j.text, (j.categories && j.categories.location) || '', j.hostedUrl]; }); } return data.jobs.filter(function (j) { return j.isListed !== false; }).map(function (j) { return [slug, j.id, j.title, j.location || '', j.jobUrl]; }); } function logChanges(ss, jobs) { const seenSheet = ss.getSheetByName('Seen') || ss.insertSheet('Seen'); const before = {}; seenSheet.getDataRange().getValues().slice(1).forEach(function (r) { if (r[1]) before[r[0] + '|' + r[1]] = r; }); const now = {}; jobs.forEach(function (r) { now[r[0] + '|' + r[1]] = r; }); const today = new Date(); const changes = []; Object.keys(now).forEach(function (k) { if (!before[k]) changes.push([today, 'new'].concat(now[k])); }); Object.keys(before).forEach(function (k) { if (!now[k]) changes.push([today, 'closed'].concat(before[k])); }); const firstRun = Object.keys(before).length === 0; if (!firstRun && changes.length) { const log = ss.getSheetByName('Changes') || ss.insertSheet('Changes'); if (log.getLastRow() === 0) log.appendRow(['Date', 'Change'].concat(HEADERS)); log.getRange(log.getLastRow() + 1, 1, changes.length, changes[0].length).setValues(changes); } writeSheet(ss, 'Seen', HEADERS, jobs); } function writeSheet(ss, name, headers, rows) { const sheet = ss.getSheetByName(name) || ss.insertSheet(name); sheet.clearContents(); sheet.getRange(1, 1, 1, headers.length).setValues([headers]); if (rows.length) sheet.getRange(2, 1, rows.length, headers.length).setValues(rows); }

Pick refreshJobs in the function menu and click Run. Google asks you to approve access to the sheet and to outside URLs the first time. You get three tabs:

Jobs: every open job right now.
Seen: the script's memory of the last run.
Changes: one row per job that opened or closed, with the date. It stays empty after the first run, because the first run is the baseline.

4. Run it every morning

In Apps Script, open Triggers (the clock icon), add a trigger for refreshJobs, and choose Time-driven → Day timer. From then on the Changes tab fills itself. Sort it by date and you have a daily list of accounts that just started hiring.

A few honest limits:

It only knows the three job boards above. Workable, Recruitee, Workday and companies with home-grown careers pages need their own code.
You have to look up each board name by hand.
Apps Script has daily quotas on outside requests. A few dozen companies a day is fine, but a few hundred will hit them.
It keeps the title and location only. Department, remote or hybrid, and pay ranges are in the APIs if you want to add columns.

5. When the list gets long

Those limits are why I packaged the same idea as a tool on Apify. You give it domains instead of board names (it finds the board for you), it covers Workable and Recruitee too, and it tags each job by function (sales, engineering and so on) and seniority, with pay ranges when they're published. It can return only the new and closed jobs since the last run, and it costs $1 per 1,000 jobs.

You can still finish in Sheets. One formula pulls the latest run's results into a tab:

=IMPORTDATA("https://api.apify.com/v2/acts/hiringsignals~ats-hiring-signal-monitor/runs/last/dataset/items?format=csv&status=SUCCEEDED&fields=company,title,changeType,function,seniority,department,postedAt,url&token=YOUR_TOKEN")

Keep that sheet private, because the token is visible to anyone who can open it.