/**
* Google Apps Script: Lightweight ATS (Jobs + Applications)
* Components:
* – Google Sheet with two tabs: Jobs, Applications
* – Google Form for candidates (Name, Email, Phone, Job, Resume Text)
* – Scoring by job-specific keywords
* – Email alerts to HR and auto-replies to applicants
* – Helpers to add/close jobs and keep the Form dropdown in sync
*
* How to use (once you paste this into a Script Project):
* 1) Create a new Google Sheet and open Extensions → Apps Script, paste this code.
* 2) In the CONSTANTS below, set HR_EMAIL and (optionally) SPREADSHEET_ID.
* If SPREADSHEET_ID is left blank, the script assumes it’s bound to the target Sheet.
* 3) Run setup() once. It will create tabs, build the Form, and wire the trigger.
* 4) Use addJobPosting(title, department, keywordsCSV) to add new jobs.
* 5) Share the Form link with candidates (printed in Logger after setup and stored in the Jobs sheet).
*/
/******************** CONFIG ********************/
const HR_EMAIL = ‘hr@example.com’; // TODO: change to your HR inbox
const AUTO_REPLY_ENABLED = true; // Set false to disable candidate auto-replies
const SPREADSHEET_ID = ”; // Optional: paste your target Sheet ID; or leave blank if bound script
// Tab names and headers
const TABS = {
JOBS: ‘Jobs’,
APPS: ‘Applications’
};
const JOBS_HEADERS = [
‘JobID’, ‘Title’, ‘Department’, ‘Keywords (comma-separated)’, ‘FormId’, ‘FormLink’, ‘Status’ // Status: Open|Closed
];
const APPS_HEADERS = [
‘Timestamp’, ‘JobID’, ‘Job Title’, ‘Name’, ‘Email’, ‘Phone’, ‘Resume Text’, ‘Score’, ‘Status’, ‘Notes’
];
// Form metadata
const FORM_TITLE = ‘Company Job Application’;
const FORM_DESC = ‘Apply to our open roles. Paste your resume/CV text in the last field.’;
// Internal constants
const STATUS = { OPEN: ‘Open’, CLOSED: ‘Closed’ };
/******************** ENTRY POINTS ********************/
function setup() {
const ss = getOrCreateSpreadsheet_();
const jobs = getOrCreateSheet_(ss, TABS.JOBS, JOBS_HEADERS);
const apps = getOrCreateSheet_(ss, TABS.APPS, APPS_HEADERS);
// Build or reuse a Form; link to Applications sheet
const form = createOrUpdateForm_(ss, apps);
// Ensure installable trigger on Form submit → onFormSubmit_
ensureFormSubmitTrigger_(form);
// If there are pre-existing jobs, sync the dropdown
rebuildJobChoices_(form, jobs);
Logger.log(‘Setup complete. Form link: ‘ + form.getEditUrl());
}
/** Add a new job posting and refresh the Form dropdown. */
function addJobPosting(title, department, keywordsCSV) {
const ss = getOrCreateSpreadsheet_();
const jobs = getOrCreateSheet_(ss, TABS.JOBS, JOBS_HEADERS);
const jobId = generateJobId_(title);
const row = [jobId, title, department, keywordsCSV, ”, ”, STATUS.OPEN];
jobs.appendRow(row);
const form = getOrCreateForm_();
rebuildJobChoices_(form, jobs);
// Store Form metadata to the job row
const formId = form.getId();
const formLink = form.getPublishedUrl();
writeFormMetaToJob_(jobs, jobId, formId, formLink);
Logger.log(`Job added: ${title} (${jobId}). Share form: ${formLink}`);
}
/** Close a job by JobID (removes it from the Form choices). */
function closeJob(jobId) {
const ss = getOrCreateSpreadsheet_();
const jobs = getOrCreateSheet_(ss, TABS.JOBS, JOBS_HEADERS);
const idx = findRowByValue_(jobs, 1, jobId); // JobID is column 1
if (idx === -1) throw new Error(‘JobID not found: ‘ + jobId);
jobs.getRange(idx, 7).setValue(STATUS.CLOSED); // Status col = 7
const form = getOrCreateForm_();
rebuildJobChoices_(form, jobs);
}
/******************** FORM HANDLERS ********************/
/**
* Installable trigger target. This runs when the Form (linked to Applications tab) is submitted.
* @param {GoogleAppsScript.Events.SheetsOnFormSubmit} e
*/
function onFormSubmit_(e) {
try {
const ss = getOrCreateSpreadsheet_();
const apps = getOrCreateSheet_(ss, TABS.APPS, APPS_HEADERS);
const jobs = getOrCreateSheet_(ss, TABS.JOBS, JOBS_HEADERS);
// Figure out which columns are which
const headers = apps.getRange(1,1,1,apps.getLastColumn()).getValues()[0];
const row = e.range.getRow();
const vals = apps.getRange(row, 1, 1, apps.getLastColumn()).getValues()[0];
const rowObj = asObject_(headers, vals);
const jobId = rowObj[‘JobID’];
const jobTitle = rowObj[‘Job Title’];
const resumeText = (rowObj[‘Resume Text’] || ”).toString();
const keywordsCSV = getKeywordsForJob_(jobs, jobId);
const score = scoreText_(resumeText, keywordsCSV);
// Write score & default status
const scoreCol = headers.indexOf(‘Score’) + 1;
const statusCol = headers.indexOf(‘Status’) + 1;
apps.getRange(row, scoreCol).setValue(score);
apps.getRange(row, statusCol).setValue(‘New’);
// Email HR
const name = rowObj[‘Name’];
const email = rowObj[‘Email’];
const phone = rowObj[‘Phone’];
notifyHR_(jobId, jobTitle, name, email, phone, score, resumeText, ss.getUrl());
// Auto-reply
if (AUTO_REPLY_ENABLED && email) {
autoReply_(email, name, jobTitle);
}
} catch (err) {
Logger.log(‘onFormSubmit_ error: ‘ + err);
}
}
/******************** BUILDERS ********************/
function createOrUpdateForm_(ss, appsSheet) {
let form = getExistingForm_();
if (!form) {
form = FormApp.create(FORM_TITLE);
form.setDescription(FORM_DESC);
form.setCollectEmail(true);
form.setLimitOneResponsePerUser(false);
}
// Wipe existing items (we fully control structure)
form.getItems().forEach(item => form.deleteItem(item));
// Build items
form.addTextItem().setTitle(‘Full Name’).setRequired(true);
form.addTextItem().setTitle(‘Email’).setRequired(true);
form.addTextItem().setTitle(‘Phone’);
form.addListItem().setTitle(‘Job’).setChoices([form.createChoice(‘— syncing —’)]).setRequired(true);
form.addParagraphTextItem().setTitle(‘Resume Text’).setHelpText(‘Paste your resume / CV text here.’).setRequired(true);
// Link to the Applications sheet (responses destination)
form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
// Ensure the Apps sheet has correct headers and order
primeApplicationsHeader_(appsSheet);
return form;
}
function rebuildJobChoices_(form, jobsSheet) {
const data = jobsSheet.getRange(2,1, jobsSheet.getLastRow()-1, jobsSheet.getLastColumn()).getValues();
const openJobs = data.filter(r => (r[6] || STATUS.OPEN) === STATUS.OPEN); // Status col index 6
const choices = openJobs.map(r => ({ id: r[0], title: r[1] }));
const jobItem = form.getItems(FormApp.ItemType.LIST)[0].asListItem();
const formChoices = choices.length
? choices.map(c => form.createChoice(`${c.title} [${c.id}]`))
: [form.createChoice(‘No open jobs at the moment’)];
jobItem.setChoices(formChoices);
// Write form meta back to every open job for convenience
const formId = form.getId();
const formLink = form.getPublishedUrl();
choices.forEach(c => writeFormMetaToJob_(jobsSheet, c.id, formId, formLink));
}
/******************** HELPERS ********************/
function getOrCreateSpreadsheet_() {
if (SPREADSHEET_ID) return SpreadsheetApp.openById(SPREADSHEET_ID);
// If this is a container-bound script, use the active sheet
try {
return SpreadsheetApp.getActiveSpreadsheet();
} catch (e) {
throw new Error(‘No spreadsheet bound and SPREADSHEET_ID not set.’);
}
}
function getOrCreateSheet_(ss, name, headers) {
let sh = ss.getSheetByName(name);
if (!sh) {
sh = ss.insertSheet(name);
sh.getRange(1,1,1,headers.length).setValues([headers]);
sh.setFrozenRows(1);
} else {
// Ensure headers
const existing = sh.getRange(1,1,1,headers.length).getValues()[0];
if (existing.join(‘\u0001’) !== headers.join(‘\u0001’)) {
sh.clear();
sh.getRange(1,1,1,headers.length).setValues([headers]);
sh.setFrozenRows(1);
}
}
return sh;
}
function getExistingForm_() {
// If this script is bound to a Form, use it; else try to find via URL in Jobs sheet later
try { return FormApp.getActiveForm(); } catch (e) { return null; }
}
function getOrCreateForm_() {
let form = getExistingForm_();
if (!form) {
// Try to find a form via Drive with the given title
const files = DriveApp.searchFiles(`title = ‘${FORM_TITLE.replace(/’/g, “\\'”)}’ and mimeType = ‘application/vnd.google-apps.form’`);
if (files.hasNext()) {
form = FormApp.openById(files.next().getId());
} else {
form = FormApp.create(FORM_TITLE);
form.setDescription(FORM_DESC);
}
}
return form;
}
function ensureFormSubmitTrigger_(form) {
// Remove existing duplicates
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(t => {
if (t.getHandlerFunction() === ‘onFormSubmit_’) ScriptApp.deleteTrigger(t);
});
ScriptApp.newTrigger(‘onFormSubmit_’)
.forForm(form)
.onFormSubmit()
.create();
}
function primeApplicationsHeader_(apps) {
const headers = apps.getRange(1,1,1,apps.getLastColumn()).getValues()[0];
if (headers.join(‘\u0001’) !== APPS_HEADERS.join(‘\u0001’)) {
apps.clear();
apps.getRange(1,1,1,APPS_HEADERS.length).setValues([APPS_HEADERS]);
apps.setFrozenRows(1);
}
}
function generateJobId_(title) {
const base = title.toLowerCase().replace(/[^a-z0-9]+/g, ‘-‘).replace(/(^-|-$)/g, ”);
const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), ‘yyyyMMddHHmmss’);
return base.substring(0,20) + ‘-‘ + stamp; // keep it readable + unique
}
function findRowByValue_(sheet, colIndex1Based, value) {
const rng = sheet.getRange(2, colIndex1Based, sheet.getLastRow()-1, 1).getValues();
for (let i = 0; i < rng.length; i++) {
if (String(rng[i][0]) === String(value)) return i + 2; // row index in sheet
}
return -1;
}
function asObject_(headers, values) {
const o = {};
headers.forEach((h,i) => o[h] = values[i]);
return o;
}
function getKeywordsForJob_(jobsSheet, jobId) {
const idx = findRowByValue_(jobsSheet, 1, jobId);
if (idx === -1) return ”;
return (jobsSheet.getRange(idx, 4).getValue() || ”).toString(); // Keywords col
}
function scoreText_(text, keywordsCSV) {
const words = (keywordsCSV || ”)
.split(‘,’)
.map(s => s.trim().toLowerCase())
.filter(Boolean);
if (!words.length) return 0;
const hay = (text || ”).toLowerCase();
let hits = 0;
words.forEach(w => {
const re = new RegExp(`\\b${escapeRegExp_(w)}\\b`, ‘g’);
const m = hay.match(re);
hits += m ? m.length : 0;
});
// Simple score: hits per keyword, scaled to 100
const score = Math.round((hits / Math.max(words.length,1)) * 100);
return Math.min(score, 100);
}
function escapeRegExp_(s){
return s.replace(/[.*+?^${}()|[\\]\\\\]/g, ‘\\$&’);
}
function writeFormMetaToJob_(jobsSheet, jobId, formId, formLink){
const idx = findRowByValue_(jobsSheet, 1, jobId);
if (idx === -1) return;
jobsSheet.getRange(idx, 5, 1, 2).setValues([[formId, formLink]]);
}
function notifyHR_(jobId, jobTitle, name, email, phone, score, resumeText, sheetUrl){
const subj = `[ATS] New applicant for ${jobTitle} (${jobId}) — ${name}`;
const body = [
`Job: ${jobTitle} (ID: ${jobId})`,
`Name: ${name}`,
`Email: ${email}`,
`Phone: ${phone}`,
`Score: ${score}`,
”,
‘Resume Text:’,
resumeText.substring(0, 2000),
”,
`Open Applications sheet: ${sheetUrl}`
].join(‘\n’);
GmailApp.sendEmail(HR_EMAIL, subj, body);
}
function autoReply_(email, name, jobTitle){
const subj = `Thanks for applying — ${jobTitle}`;
const body = `Hello ${name || ”},\n\n` +
`Thanks for applying for the ${jobTitle} role. Our team will review your application and ` +
`reach out if your profile matches the next stage.\n\nRegards,\nHR Team`;
GmailApp.sendEmail(email, subj, body);
}
/******************** OPTIONAL: STATUS HELPERS ********************/
/** Mark an application row (by row number) with a status and optional note. */
function setApplicationStatus(rowNumber, status, note) {
const ss = getOrCreateSpreadsheet_();
const apps = getOrCreateSheet_(ss, TABS.APPS, APPS_HEADERS);
const headers = apps.getRange(1,1,1,apps.getLastColumn()).getValues()[0];
const statusCol = headers.indexOf(‘Status’) + 1;
const notesCol = headers.indexOf(‘Notes’) + 1;
apps.getRange(rowNumber, statusCol).setValue(status);
if (note !== undefined) apps.getRange(rowNumber, notesCol).setValue(note);
}
/******************** SYNC JOB DROPDOWN WITH APPS SHEET ********************/
/**
* Call this if you ever change headers or re-link the Form destination. It rebuilds the Apps header
* and re-wires the trigger.
*/
function relinkFormDestination() {
const ss = getOrCreateSpreadsheet_();
const apps = getOrCreateSheet_(ss, TABS.APPS, APPS_HEADERS);
const form = getOrCreateForm_();
form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
primeApplicationsHeader_(apps);
ensureFormSubmitTrigger_(form);
rebuildJobChoices_(form, getOrCreateSheet_(ss, TABS.JOBS, JOBS_HEADERS));
}