/** * @OnlyCurrentDoc * Interview Tracker 1.0.0 for Google Sheets: interview stages, reminders, no-show and feedback follow-ups as Gmail drafts. * https://karunalabs.pages.dev/tools/interview-tracker/ · Paste this whole file into Extensions > Apps Script > Code.gs, save, reload the sheet. * Runs only in your Google account. Creates drafts (never sends to candidates). No server; the licence key is checked offline. */ var IT_VERSION = '1.0.0'; /* ---- Karuna Labs licence check (offline Ed25519): tweetnacl-js 1.0.3 (public domain) + KLKey ---- */ var IT_LIB = (function () { var self = {}, module, require; var atob = function (s) { var A = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/', out = '', buf = 0, bits = 0; s = String(s).replace(/=+$/, ''); for (var i = 0; i < s.length; i++) { var v = A.indexOf(s.charAt(i)); if (v < 0) throw new Error('bad base64'); buf = (buf << 6) | v; bits += 6; if (bits >= 8) { bits -= 8; out += String.fromCharCode((buf >> bits) & 255); } } return out; }; function TextDecoder() {} TextDecoder.prototype.decode = function (b) { var s = '', i = 0; while (i < b.length) { var c = b[i++]; if (c < 128) s += String.fromCharCode(c); else if (c < 224) s += String.fromCharCode(((c & 31) << 6) | (b[i++] & 63)); else if (c < 240) s += String.fromCharCode(((c & 15) << 12) | ((b[i++] & 63) << 6) | (b[i++] & 63)); else { var cp = ((c & 7) << 18) | ((b[i++] & 63) << 12) | ((b[i++] & 63) << 6) | (b[i++] & 63); cp -= 0x10000; s += String.fromCharCode(0xD800 + (cp >> 10), 0xDC00 + (cp & 1023)); } } return s; }; !function(i){"use strict";var m=function(r,n){this.hi=0|r,this.lo=0|n},v=function(r){var n,e=new Float64Array(16);if(r)for(n=0;n>>32-n}function b(r,n){var e=255&r[n+3];return(e=(e=e<<8|255&r[n+2])<<8|255&r[n+1])<<8|255&r[n+0]}function B(r,n){var e=r[n]<<24|r[n+1]<<16|r[n+2]<<8|r[n+3],t=r[n+4]<<24|r[n+5]<<16|r[n+6]<<8|r[n+7];return new m(e,t)}function p(r,n,e){var t;for(t=0;t<4;t++)r[n+t]=255&e,e>>>=8}function S(r,n,e){r[n]=e.hi>>24&255,r[n+1]=e.hi>>16&255,r[n+2]=e.hi>>8&255,r[n+3]=255&e.hi,r[n+4]=e.lo>>24&255,r[n+5]=e.lo>>16&255,r[n+6]=e.lo>>8&255,r[n+7]=255&e.lo}function u(r,n,e,t,o){var i,a=0;for(i=0;i>>8)-1}function A(r,n,e,t){return u(r,n,e,t,16)}function _(r,n,e,t){return u(r,n,e,t,32)}function U(r,n,e,t,o){var i,a,f,u=new Uint32Array(16),c=new Uint32Array(16),w=new Uint32Array(16),y=new Uint32Array(4);for(i=0;i<4;i++)c[5*i]=b(t,4*i),c[1+i]=b(e,4*i),c[6+i]=b(n,4*i),c[11+i]=b(e,16+4*i);for(i=0;i<16;i++)w[i]=c[i];for(i=0;i<20;i++){for(a=0;a<4;a++){for(f=0;f<4;f++)y[f]=c[(5*a+4*f)%16];for(y[1]^=h(y[0]+y[3]|0,7),y[2]^=h(y[1]+y[0]|0,9),y[3]^=h(y[2]+y[1]|0,13),y[0]^=h(y[3]+y[2]|0,18),f=0;f<4;f++)u[4*a+(a+f)%4]=y[f]}for(f=0;f<16;f++)c[f]=u[f]}if(o){for(i=0;i<16;i++)c[i]=c[i]+w[i]|0;for(i=0;i<4;i++)c[5*i]=c[5*i]-b(t,4*i)|0,c[6+i]=c[6+i]-b(n,4*i)|0;for(i=0;i<4;i++)p(r,4*i,c[5*i]),p(r,16+4*i,c[6+i])}else for(i=0;i<16;i++)p(r,4*i,c[i]+w[i]|0)}function E(r,n,e,t){U(r,n,e,t,!1)}function x(r,n,e,t){return U(r,n,e,t,!0),0}var d=new Uint8Array([101,120,112,97,110,100,32,51,50,45,98,121,116,101,32,107]);function K(r,n,e,t,o,i,a){var f,u,c=new Uint8Array(16),w=new Uint8Array(64);if(!o)return 0;for(u=0;u<16;u++)c[u]=0;for(u=0;u<8;u++)c[u]=i[u];for(;64<=o;){for(E(w,c,a,d),u=0;u<64;u++)r[n+u]=(e?e[t+u]:0)^w[u];for(f=1,u=8;u<16;u++)f=f+(255&c[u])|0,c[u]=255&f,f>>>=8;o-=64,n+=64,e&&(t+=64)}if(0>>=8}var z=new Uint32Array([5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,252]);function R(r,n,e,t,o,i){var a,f,u,c,w=new Uint32Array(17),y=new Uint32Array(17),l=new Uint32Array(17),s=new Uint32Array(17),h=new Uint32Array(17);for(u=0;u<17;u++)y[u]=l[u]=0;for(u=0;u<16;u++)y[u]=i[u];for(y[3]&=15,y[4]&=252,y[7]&=15,y[8]&=252,y[11]&=15,y[12]&=252,y[15]&=15;0>>=8;for(c=c+l[16]|0,l[16]=3&c,c=5*(c>>>2)|0,u=0;u<16;u++)c=c+l[u]|0,l[u]=255&c,c>>>=8;c=c+l[16]|0,l[16]=c}for(u=0;u<17;u++)h[u]=l[u];for(k(l,z),a=0|-(l[16]>>>7),u=0;u<17;u++)l[u]^=a&(h[u]^l[u]);for(u=0;u<16;u++)s[u]=i[u+16];for(s[16]=0,k(l,s),u=0;u<16;u++)r[n+u]=l[u];return 0}function P(r,n,e,t,o,i){var a=new Uint8Array(16);return R(a,0,e,t,o,i),A(r,n,a,0)}function M(r,n,e,t,o){var i;if(e<32)return-1;for(T(r,0,n,0,e,t,o),R(r,16,r,32,e-32,r),i=0;i<16;i++)r[i]=0;return 0}function N(r,n,e,t,o){var i,a=new Uint8Array(32);if(e<32)return-1;if(L(a,0,32,t,o),0!==P(n,16,n,32,e-32,a))return-1;for(T(r,0,n,0,e,t,o),i=0;i<32;i++)r[i]=0;return 0}function O(r,n){var e;for(e=0;e<16;e++)r[e]=0|n[e]}function C(r){var n,e;for(e=0;e<16;e++)r[e]+=65536,n=Math.floor(r[e]/65536),r[(e+1)*(e<15?1:0)]+=n-1+37*(n-1)*(15===e?1:0),r[e]-=65536*n}function F(r,n,e){for(var t,o=~(e-1),i=0;i<16;i++)t=o&(r[i]^n[i]),r[i]^=t,n[i]^=t}function Z(r,n){var e,t,o,i=v(),a=v();for(e=0;e<16;e++)a[e]=n[e];for(C(a),C(a),C(a),t=0;t<2;t++){for(i[0]=a[0]-65517,e=1;e<15;e++)i[e]=a[e]-65535-(i[e-1]>>16&1),i[e-1]&=65535;i[15]=a[15]-32767-(i[14]>>16&1),o=i[15]>>16&1,i[14]&=65535,F(a,i,1-o)}for(e=0;e<16;e++)r[2*e]=255&a[e],r[2*e+1]=a[e]>>8}function G(r,n){var e=new Uint8Array(32),t=new Uint8Array(32);return Z(e,r),Z(t,n),_(e,0,t,0)}function q(r){var n=new Uint8Array(32);return Z(n,r),1&n[0]}function D(r,n){var e;for(e=0;e<16;e++)r[e]=n[2*e]+(n[2*e+1]<<8);r[15]&=32767}function I(r,n,e){var t;for(t=0;t<16;t++)r[t]=n[t]+e[t]|0}function V(r,n,e){var t;for(t=0;t<16;t++)r[t]=n[t]-e[t]|0}function X(r,n,e){var t,o,i=new Float64Array(31);for(t=0;t<31;t++)i[t]=0;for(t=0;t<16;t++)for(o=0;o<16;o++)i[t+o]+=n[t]*e[o];for(t=0;t<15;t++)i[t]+=38*i[t+16];for(t=0;t<16;t++)r[t]=i[t];C(r),C(r)}function j(r,n){X(r,n,n)}function H(r,n){var e,t=v();for(e=0;e<16;e++)t[e]=n[e];for(e=253;0<=e;e--)j(t,t),2!==e&&4!==e&&X(t,t,n);for(e=0;e<16;e++)r[e]=t[e]}function J(r,n){var e,t=v();for(e=0;e<16;e++)t[e]=n[e];for(e=250;0<=e;e--)j(t,t),1!==e&&X(t,t,n);for(e=0;e<16;e++)r[e]=t[e]}function Q(r,n,e){var t,o,i=new Uint8Array(32),a=new Float64Array(80),f=v(),u=v(),c=v(),w=v(),y=v(),l=v();for(o=0;o<31;o++)i[o]=n[o];for(i[31]=127&n[31]|64,i[0]&=248,D(a,e),o=0;o<16;o++)u[o]=a[o],w[o]=f[o]=c[o]=0;for(f[0]=w[0]=1,o=254;0<=o;--o)F(f,u,t=i[o>>>3]>>>(7&o)&1),F(c,w,t),I(y,f,c),V(f,f,c),I(c,u,w),V(u,u,w),j(w,y),j(l,f),X(f,c,f),X(c,u,y),I(y,f,c),V(f,f,c),j(u,f),V(c,w,l),X(f,c,g),I(f,f,w),X(c,c,f),X(f,w,l),X(w,u,a),j(u,y),F(f,u,t),F(c,w,t);for(o=0;o<16;o++)a[o+16]=f[o],a[o+32]=c[o],a[o+48]=u[o],a[o+64]=w[o];var s=a.subarray(32),h=a.subarray(16);return H(s,s),X(h,h,s),Z(r,h),0}function W(r,n){return Q(r,n,e)}function $(r,n){return a(n,32),W(r,n)}function rr(r,n,e){var t=new Uint8Array(32);return Q(t,e,n),x(r,o,t,d)}var nr=M,er=N;function tr(){var r,n,e,t=0,o=0,i=0,a=0,f=65535;for(e=0;e>>16,i+=(n=arguments[e].hi)&f,a+=n>>>16;return new m((i+=(o+=t>>>16)>>>16)&f|(a+=i>>>16)<<16,t&f|o<<16)}function or(r,n){return new m(r.hi>>>n,r.lo>>>n|r.hi<<32-n)}function ir(){var r,n=0,e=0;for(r=0;r>>n|r.lo<>>n|r.hi<>>n|r.hi<>>n|r.lo<>(7&o)&1),yr(n,r),yr(r,r),lr(r,n,t)}function vr(r,n){var e=[v(),v(),v(),v()];O(e[0],t),O(e[1],f),O(e[2],w),X(e[3],t,f),hr(r,e,n)}function gr(r,n,e){var t,o=new Uint8Array(64),i=[v(),v(),v(),v()];for(e||a(n,32),wr(o,n,32),o[0]&=248,o[31]&=127,o[31]|=64,vr(i,o),sr(r,i),t=0;t<32;t++)n[t+32]=r[t];return 0}var br=new Float64Array([237,211,245,92,26,99,18,88,214,156,247,162,222,249,222,20,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,16]);function pr(r,n){var e,t,o,i;for(t=63;32<=t;--t){for(e=0,o=t-32,i=t-12;o>4)*br[o],e=n[o]>>8,n[o]&=255;for(o=0;o<32;o++)n[o]-=e*br[o];for(t=0;t<32;t++)n[t+1]+=n[t]>>8,r[t]=255&n[t]}function Ar(r){var n,e=new Float64Array(64);for(n=0;n<64;n++)e[n]=r[n];for(n=0;n<64;n++)r[n]=0;pr(r,e)}function _r(r,n,e,t){var o,i,a=new Uint8Array(64),f=new Uint8Array(64),u=new Uint8Array(64),c=new Float64Array(64),w=[v(),v(),v(),v()];wr(a,t,32),a[0]&=248,a[31]&=127,a[31]|=64;var y=e+64;for(o=0;o>7&&V(r[0],c,r[0]),X(r[3],r[0],r[1])}(u,t))return-1;for(o=0;o. * Payload: { v:1, id:"", p:["payment-files" | "*" …], s:, iat:"YYYY-MM-DD", exp:"YYYY-MM-DD"|null, src:"appsumo"|"direct"|"comp"|"test", t?: } * Issued only by shared/keys/kl-keys.js with the private key kept in ~/.config/ventures/licence/ (never in the repo or site-dist). * Honest limit: an offline key can be copied between people; revocation works only through the REVOKED list shipped with the site. */ var KLKey = (function () { 'use strict'; var PUBLIC_KEY = "JQxnkHnLrq+PlqQZlhbOqxoWObbKZl7q6f6At8Vi6Pw="; // base64 raw 32-byte Ed25519 public key; filled in by sync-license.sh / build var REVOKED = ["6825066289c9","b1b15b10c543","f8c72d0e3e22"]; // key ids; filled in by sync-license.sh from shared/revoked.json var naclRef = (typeof nacl !== 'undefined') ? nacl : (function () { try { return require('./vendor/nacl.min.js'); } catch (e) { return null; } })(); function b64uToBytes(s) { s = String(s).replace(/-/g, '+').replace(/_/g, '/'); while (s.length % 4) s += '='; var bin = typeof atob === 'function' ? atob(s) : Buffer.from(s, 'base64').toString('binary'), out = new Uint8Array(bin.length); for (var i = 0; i < bin.length; i++) out[i] = bin.charCodeAt(i); return out; } function utf8(bytes) { return typeof TextDecoder !== 'undefined' ? new TextDecoder().decode(bytes) : Buffer.from(bytes).toString('utf8'); } function looksLike(key) { return /^KL1-[A-Za-z0-9_-]+\.[A-Za-z0-9_-]+$/.test(String(key || '').trim()); } /** -> { ok, error?, payload? } opts: { publicKey?, product?, now?, revoked? } */ function verify(key, opts) { opts = opts || {}; key = String(key || '').trim().replace(/\s+/g, ''); if (!looksLike(key)) return { ok: false, error: 'Not a Karuna Labs offline licence key.' }; var pk = opts.publicKey || PUBLIC_KEY; if (!pk || /__KL_/.test(pk)) return { ok: false, error: 'Licence public key not configured.' }; var parts = key.slice(4).split('.'), msg, sig, payload; try { msg = b64uToBytes(parts[0]); sig = b64uToBytes(parts[1]); } catch (e) { return { ok: false, error: 'Licence key is damaged (copy it again).' }; } if (sig.length !== 64) return { ok: false, error: 'Licence key is damaged (copy it again).' }; if (!naclRef || !naclRef.sign.detached.verify(msg, sig, b64uToBytes(pk))) return { ok: false, error: 'Licence key signature is not valid.' }; try { payload = JSON.parse(utf8(msg)); } catch (e) { return { ok: false, error: 'Licence key is damaged.' }; } if (payload.v !== 1) return { ok: false, error: 'Unsupported licence version.' }; if ((opts.revoked || REVOKED).indexOf(payload.id) >= 0) return { ok: false, error: 'This licence has been refunded or revoked.', payload: payload }; var now = opts.now || Date.now(); if (payload.exp && now > Date.parse(payload.exp + 'T23:59:59Z')) return { ok: false, error: 'This licence expired on ' + payload.exp + '.', payload: payload }; if (opts.product && !(payload.p || []).some(function (x) { return x === '*' || x === opts.product; })) return { ok: false, error: 'This licence is for a different tool (' + (payload.p || []).join(', ') + ').', payload: payload }; return { ok: true, payload: payload }; } return { verify: verify, looksLike: looksLike, b64uToBytes: b64uToBytes, PUBLIC_KEY: PUBLIC_KEY }; })(); if (typeof module !== 'undefined') module.exports = KLKey; return { KLKey: KLKey }; })(); var KLKey = IT_LIB.KLKey; /* ---- Core.js: pure logic (no Apps Script calls), shared by the script and the node tests ---- */ var IT_CORE = (function () { var STAGES = ['Applied', 'Screening', 'Interview scheduled', 'Interviewed', 'Feedback pending', 'Offer', 'Hired', 'Rejected', 'Withdrawn', 'No-show']; var CLOSED = { 'Hired': 1, 'Rejected': 1, 'Withdrawn': 1 }; var COLUMNS = ['Candidate', 'Email', 'Phone', 'Role', 'Stage', 'Interview date', 'Interview time', 'Interviewer', 'Interviewer email', 'Location / link', 'Confirmed?', 'Last contacted', 'Status', 'Next action', 'Notes', 'Drafts log']; var FREE_ACTIVE = 10; var TEMPLATES = [ ['Invite', 'Interview for {{role}} at {{company}}: {{date}} {{time}}', 'Hi {{first}},\n\nThank you for applying for the {{role}} role at {{company}}. We would like to invite you to an interview on {{date}} at {{time}} with {{interviewer}}.\n\nWhere: {{location}}\n\nPlease reply to confirm this time works for you, or suggest another time.\n\nBest regards,\n{{sender}}\n{{company}}'], ['Reminder', 'Reminder: your interview tomorrow, {{date}} at {{time}}', 'Hi {{first}},\n\nA quick reminder of your interview for the {{role}} role tomorrow, {{date}} at {{time}}, with {{interviewer}}.\n\nWhere: {{location}}\n\nIf anything has changed, just reply to this email and we will find another time.\n\nSee you then,\n{{sender}}\n{{company}}'], ['Confirm nudge', 'Can you confirm your interview on {{date}}?', 'Hi {{first}},\n\nI wanted to check that {{date}} at {{time}} still works for your interview for the {{role}} role. Could you reply to confirm?\n\nWhere: {{location}}\n\nThanks,\n{{sender}}\n{{company}}'], ['No-show', 'Sorry we missed you: {{role}} interview', 'Hi {{first}},\n\nWe had your interview for the {{role}} role booked for {{date}} at {{time}}, but we did not manage to connect. I hope everything is all right.\n\nIf you are still interested, reply with two or three times that suit you this week and I will try to rebook.\n\nBest regards,\n{{sender}}\n{{company}}'], ['Feedback chase', 'Feedback needed: {{candidate}} ({{role}})', 'Hi {{interviewer_first}},\n\nCould you share your feedback on {{candidate}}, who you interviewed for the {{role}} role on {{date}}? A few lines and a yes / no / maybe is enough so we can update the candidate.\n\nThanks,\n{{sender}}'], ['Update', 'Your application for {{role}}', 'Hi {{first}},\n\nThank you again for your time. I wanted to let you know we are still collecting feedback and expect to come back to you within the next few days.\n\nBest regards,\n{{sender}}\n{{company}}'] ]; function first(name) { return String(name || '').trim().split(/\s+/)[0] || 'there'; } function addDays(iso, n) { var d = new Date(Date.UTC(+iso.slice(0, 4), +iso.slice(5, 7) - 1, +iso.slice(8, 10)) + n * 86400000); return d.toISOString().slice(0, 10); } function daysBetween(a, b) { return Math.round((Date.UTC(+b.slice(0, 4), +b.slice(5, 7) - 1, +b.slice(8, 10)) - Date.UTC(+a.slice(0, 4), +a.slice(5, 7) - 1, +a.slice(8, 10))) / 86400000); } /** r: { name, email, stage, date (yyyy-MM-dd|''), time ('HH:mm'|''), interviewer, interviewerEmail, confirmed, last (yyyy-MM-dd|''), log } * now: { today: 'yyyy-MM-dd', hm: 'HH:mm' }, s: { staleDays, feedbackDays } * -> { status, action, draft: null | 'Reminder' | 'Confirm nudge' | 'No-show' | 'Feedback chase', to: 'candidate' | 'interviewer', tag } */ function evaluate(r, now, s) { s = s || {}; var stale = +s.staleDays || 5, fb = +s.feedbackDays || 2, st = r.stage || '', out = { status: '', action: '', draft: null, to: 'candidate', tag: '' }; if (!r.name && !r.email) return out; if (CLOSED[st]) { out.status = 'Closed'; return out; } var log = String(r.log || ''); function want(tpl, to, tag) { if (log.indexOf(tag) < 0) { out.draft = tpl; out.to = to; out.tag = tag; } } if (st === 'No-show') { out.status = 'No-show'; out.action = 'Send a rebook email, then update the stage'; if (r.email) want('No-show', 'candidate', 'noshow:' + (r.date || now.today)); return out; } if (st === 'Interview scheduled' || (st === 'Screening' && r.date)) { if (!r.date) { out.status = 'Needs a date'; out.action = 'Agree a time and fill in Interview date'; return out; } var d = daysBetween(now.today, r.date); if (d < 0 || (d === 0 && r.time && r.time < now.hm && st === 'Interview scheduled' && daysBetween(r.date, now.today) >= 0 && hoursPast(r.time, now.hm) >= 2)) { out.status = 'Interview passed: update stage'; out.action = 'Set the stage to Interviewed or No-show'; return out; } if (d === 0) { out.status = 'Interview today'; out.action = 'Interview at ' + (r.time || 'time not set') + (r.interviewer ? ' with ' + r.interviewer : ''); return out; } if (d === 1) { out.status = 'Interview tomorrow'; out.action = 'Send the reminder'; if (r.email) want('Reminder', 'candidate', 'rem:' + r.date); return out; } if (!/^y/i.test(String(r.confirmed || '')) && d <= 3) { out.status = 'Not confirmed'; out.action = 'Ask the candidate to confirm'; if (r.email) want('Confirm nudge', 'candidate', 'conf:' + r.date); return out; } out.status = 'Scheduled'; out.action = 'Interview in ' + d + ' days'; return out; } if (st === 'Interviewed' || st === 'Feedback pending') { var since = r.date ? daysBetween(r.date, now.today) : null; if (since !== null && since >= fb) { out.status = 'Feedback overdue'; out.action = 'Chase ' + (r.interviewer || 'the interviewer') + ' for feedback'; if (r.interviewerEmail) want('Feedback chase', 'interviewer', 'fb:' + r.date); return out; } out.status = 'Waiting for feedback'; out.action = 'Feedback due ' + (r.date ? addDays(r.date, fb) : 'soon'); return out; } var idle = r.last ? daysBetween(r.last, now.today) : null; if (st === 'Offer') { out.status = idle !== null && idle >= 3 ? 'Offer: chase answer' : 'Offer out'; out.action = idle !== null && idle >= 3 ? 'Call or email the candidate for an answer' : 'Wait for the answer'; return out; } if (idle !== null && idle >= stale) { out.status = 'Stale: ' + idle + ' days'; out.action = 'Update the candidate or close the application'; return out; } out.status = st ? 'Open' : 'Set a stage'; out.action = st === 'Applied' ? 'Screen the application' : st === 'Screening' ? 'Book the interview' : 'Pick a stage'; return out; } function hoursPast(t, hm) { var a = t.split(':'), b = hm.split(':'); return ((+b[0] * 60 + +b[1]) - (+a[0] * 60 + +a[1])) / 60; } function fill(tpl, r, s) { var map = { first: first(r.name), candidate: r.name || '', role: r.role || 'open', company: s.company || 'our company', date: r.dateLabel || r.date || '[date]', time: r.time || '[time]', interviewer: r.interviewer || 'our team', interviewer_first: first(r.interviewer), location: r.location || '[location or video link]', sender: s.sender || '' }; tpl = String(tpl); if (!r.role) tpl = tpl.replace(/the \{\{role\}\} role/g, 'the role').replace(/ \(\{\{role\}\}\)/g, '').replace(/: \{\{role\}\} interview/g, ': your interview').replace(/for \{\{role\}\}/g, 'for the role'); return tpl.replace(/\{\{(\w+)\}\}/g, function (m, k) { return k in map ? map[k] : m; }); } /** picks which rows the free plan processes: the first FREE_ACTIVE open rows */ function activeLimit(rows, pro) { var n = 0; return rows.map(function (r) { if (!r.name && !r.email) return true; if (CLOSED[r.stage]) return true; n++; return pro || n <= FREE_ACTIVE; }); } return { STAGES: STAGES, CLOSED: CLOSED, COLUMNS: COLUMNS, TEMPLATES: TEMPLATES, FREE_ACTIVE: FREE_ACTIVE, evaluate: evaluate, fill: fill, addDays: addDays, daysBetween: daysBetween, activeLimit: activeLimit, first: first }; })(); if (typeof module !== 'undefined') module.exports = IT_CORE; /* ---- Code.js: Apps Script UI. Runs only in your Google account: reads this sheet, creates Gmail DRAFTS (never sends to candidates), * and can email YOU a daily agenda. No server; the licence key is checked offline. ---- */ var IT_SHEET = 'Candidates', IT_TPL = 'Templates', IT_SET = 'Settings', IT_TODAY = 'Today', IT_DAILY = 'itDaily'; var IT_LANDING = 'https://karunalabs.pages.dev/tools/interview-tracker/'; function onOpen() { SpreadsheetApp.getUi().createMenu('Interview Tracker') .addItem('Refresh statuses + Today list', 'itRefresh') .addItem('Create Gmail drafts for due emails', 'itDraftAll') .addItem('Email me today\'s agenda', 'itEmailAgenda') .addSeparator() .addItem('Turn on daily autopilot (Pro)', 'itAutopilotOn') .addItem('Turn off daily autopilot', 'itAutopilotOff') .addItem('Enter licence key', 'itEnterKey') .addSeparator() .addItem('Set up / repair this sheet', 'itSetup') .addItem('Help', 'itHelp') .addToUi(); } function itSs_() { return SpreadsheetApp.getActiveSpreadsheet(); } function itTz_() { return itSs_().getSpreadsheetTimeZone() || 'Etc/UTC'; } function itNotify_(msg) { try { SpreadsheetApp.getUi().alert(msg); } catch (e) { console.log(msg); } } function itSheet_(name) { var ss = itSs_(); return ss.getSheetByName(name) || ss.insertSheet(name); } function itSetup() { var sh = itSheet_(IT_SHEET), cols = IT_CORE.COLUMNS; sh.getRange(1, 1, 1, cols.length).setValues([cols]).setFontWeight('bold').setBackground('#1e3a8a').setFontColor('#ffffff'); sh.setFrozenRows(1); sh.getRange(2, 5, 999, 1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(IT_CORE.STAGES, true).setAllowInvalid(false).build()); sh.getRange(2, 11, 999, 1).setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], true).build()); sh.getRange(2, 6, 999, 1).setNumberFormat('yyyy-mm-dd'); sh.getRange(2, 12, 999, 1).setNumberFormat('yyyy-mm-dd'); sh.getRange(2, 7, 999, 1).setNumberFormat('HH:mm'); [180, 200, 120, 150, 150, 110, 80, 130, 180, 200, 90, 110, 190, 260, 220, 140].forEach(function (w, i) { sh.setColumnWidth(i + 1, w); }); sh.getRange('M1').setNote('Filled by Interview Tracker > Refresh. Do not type here.'); sh.getRange('P1').setNote('Interview Tracker notes which drafts it already created, so it never drafts the same email twice.'); var tp = itSheet_(IT_TPL); if (tp.getLastRow() < 2) { tp.getRange(1, 1, 1, 3).setValues([['Template', 'Subject', 'Body']]).setFontWeight('bold'); tp.getRange(2, 1, IT_CORE.TEMPLATES.length, 3).setValues(IT_CORE.TEMPLATES); tp.setFrozenRows(1); tp.setColumnWidth(2, 320); tp.setColumnWidth(3, 600); } var st = itSheet_(IT_SET); if (st.getLastRow() < 2) { st.getRange(1, 1, 7, 2).setValues([['Setting', 'Value'], ['Company name', ''], ['Your name (email sign-off)', ''], ['Stale after (days without contact)', 5], ['Chase interviewer feedback after (days)', 2], ['Daily autopilot hour (0-23)', 8], ['Licence key (Pro)', '']]); st.getRange(1, 1, 1, 2).setFontWeight('bold'); st.setColumnWidth(1, 300); st.setColumnWidth(2, 320); } if (sh.getLastRow() < 2) sh.getRange(2, 1, 1, 16).setValues([['Example Candidate (delete me)', 'candidate@example.com', '', 'Office Manager', 'Interview scheduled', '', '', 'Your Name', '', 'Google Meet link', 'No', '', '', '', 'Fill in a date and run Refresh', '']]); itRefresh(true); itNotify_('Interview Tracker is ready.\n\n1. Fill Company name and Your name on the Settings tab.\n2. Add candidates on the Candidates tab (one row each).\n3. Use the Interview Tracker menu: Refresh, then Create Gmail drafts. Drafts wait in Gmail for you to check and send.'); } function itSettings_() { var st = itSs_().getSheetByName(IT_SET), v = st ? st.getRange(1, 1, Math.max(1, st.getLastRow()), 2).getValues() : [], m = {}; v.forEach(function (r) { m[String(r[0])] = r[1]; }); return { company: String(m['Company name'] || '').trim(), sender: String(m['Your name (email sign-off)'] || '').trim(), staleDays: +m['Stale after (days without contact)'] || 5, feedbackDays: +m['Chase interviewer feedback after (days)'] || 2, hour: Math.min(23, Math.max(0, +m['Daily autopilot hour (0-23)'] || 8)), key: String(m['Licence key (Pro)'] || '').trim() }; } function itFmt_(v, f) { if (v instanceof Date && !isNaN(v)) return Utilities.formatDate(v, itTz_(), f); return String(v == null ? '' : v).trim(); } function itNormTime_(v) { if (v instanceof Date) return itFmt_(v, 'HH:mm'); var m = /^(\d{1,2})[:.](\d{2})\s*([ap]m)?$/i.exec(String(v || '').trim()); if (!m) return ''; var h = +m[1] % 12 + (/p/i.test(m[3] || '') ? 12 : (m[3] ? 0 : Math.floor(+m[1] / 12) * 12)); return ('0' + h).slice(-2) + ':' + m[2]; } function itNormDate_(v) { if (v instanceof Date) return itFmt_(v, 'yyyy-MM-dd'); var s = String(v || '').trim(); return /^\d{4}-\d{2}-\d{2}$/.test(s) ? s : ''; } function itRows_() { var sh = itSs_().getSheetByName(IT_SHEET); if (!sh || sh.getLastRow() < 2) return []; return sh.getRange(2, 1, sh.getLastRow() - 1, 16).getValues().map(function (v, i) { var date = itNormDate_(v[5]); return { row: i + 2, name: String(v[0]).trim(), email: String(v[1]).trim(), role: String(v[3]).trim(), stage: String(v[4]).trim(), date: date, dateLabel: date ? itFmt_(v[5] instanceof Date ? v[5] : new Date(date + 'T12:00:00Z'), 'EEE d MMM') : '', time: itNormTime_(v[6]), interviewer: String(v[7]).trim(), interviewerEmail: String(v[8]).trim(), location: String(v[9]).trim(), confirmed: String(v[10]).trim(), last: itNormDate_(v[11]), log: String(v[15]) }; }); } function itNow_() { var d = new Date(); return { today: itFmt_(d, 'yyyy-MM-dd'), hm: itFmt_(d, 'HH:mm') }; } function itIsPro_() { var key = itSettings_().key; if (!key) return false; var v = KLKey.verify(key, { product: 'interview-tracker' }); return v.ok && (v.payload.p || []).indexOf('interview-tracker') >= 0; } /** writes Status / Next action, rebuilds Today; returns evaluated rows */ function itRefresh(silent) { var sh = itSs_().getSheetByName(IT_SHEET); if (!sh) { itSetup(); return []; } var rows = itRows_(), s = itSettings_(), now = itNow_(), pro = itIsPro_(), allowed = IT_CORE.activeLimit(rows, pro), skipped = 0; var out = rows.map(function (r, i) { if (!allowed[i]) { skipped++; r.ev = { status: 'Free plan limit', action: 'Pro tracks more than ' + IT_CORE.FREE_ACTIVE + ' active candidates: ' + IT_LANDING, draft: null }; } else r.ev = IT_CORE.evaluate(r, now, s); return [r.ev.status, r.ev.action]; }); if (out.length) sh.getRange(2, 13, out.length, 2).setValues(out); itBuildToday_(rows, now); if (silent !== true) itNotify_('Statuses updated for ' + rows.filter(function (r) { return r.name || r.email; }).length + ' candidates.' + (skipped ? '\n\nFree plan: ' + skipped + ' active candidates beyond the first ' + IT_CORE.FREE_ACTIVE + ' were not checked. Pro ($9 one-time): ' + IT_LANDING : '')); return rows; } function itBuildToday_(rows, now) { var t = itSheet_(IT_TODAY), order = { 'Interview today': 1, 'Interview passed: update stage': 2, 'No-show': 3, 'Interview tomorrow': 4, 'Not confirmed': 5, 'Feedback overdue': 6, 'Offer: chase answer': 7 }; var list = rows.filter(function (r) { return r.ev && (order[r.ev.status] || /^Stale/.test(r.ev.status)); }).sort(function (a, b) { return (order[a.ev.status] || 9) - (order[b.ev.status] || 9) || String(a.time).localeCompare(String(b.time)); }); t.clear(); var head = [['Today ' + now.today, '', '', ''], ['Candidate', 'Status', 'Next action', 'Row']]; var body = list.map(function (r) { return [r.name, r.ev.status, r.ev.action, r.row]; }); if (!body.length) body = [['Nothing urgent today.', '', '', '']]; t.getRange(1, 1, head.length + body.length, 4).setValues(head.concat(body)); t.getRange(1, 1, 2, 4).setFontWeight('bold'); return list; } function itTemplates_() { var tp = itSs_().getSheetByName(IT_TPL), m = {}; IT_CORE.TEMPLATES.forEach(function (t) { m[t[0]] = { subject: t[1], body: t[2] }; }); if (tp && tp.getLastRow() > 1) tp.getRange(2, 1, tp.getLastRow() - 1, 3).getValues().forEach(function (r) { if (r[0]) m[String(r[0]).trim()] = { subject: String(r[1]), body: String(r[2]) }; }); return m; } /** creates Gmail drafts for every row that needs one (reminder, confirm nudge, no-show, feedback chase). Never sends. */ function itDraftAll(silent) { var rows = itRefresh(true), s = itSettings_(), T = itTemplates_(), sh = itSs_().getSheetByName(IT_SHEET), made = []; rows.forEach(function (r) { if (!r.ev || !r.ev.draft || !T[r.ev.draft]) return; var to = r.ev.to === 'interviewer' ? r.interviewerEmail : r.email; if (!to) return; GmailApp.createDraft(to, IT_CORE.fill(T[r.ev.draft].subject, r, s), IT_CORE.fill(T[r.ev.draft].body, r, s)); var log = (r.log ? r.log + ' ' : '') + r.ev.tag; sh.getRange(r.row, 16).setValue(log); r.log = log; if (r.ev.to === 'candidate') sh.getRange(r.row, 12).setValue(new Date()); made.push(r.ev.draft + ': ' + (r.name || to)); }); if (silent !== true) itNotify_(made.length ? made.length + ' Gmail draft' + (made.length > 1 ? 's' : '') + ' created. Open Gmail > Drafts, check each one and press Send.\n\n' + made.join('\n') : 'No emails are due right now.'); return made; } function itEmailAgenda(silent) { var rows = itRefresh(true), now = itNow_(), list = itBuildToday_(rows, now), me = Session.getEffectiveUser().getEmail(); var lines = list.map(function (r) { return '• ' + r.name + ': ' + r.ev.status + ' (' + r.ev.action + ')'; }); var body = 'Interview Tracker, ' + now.today + '\n\n' + (lines.length ? lines.join('\n') : 'Nothing urgent today.') + '\n\nOpen the sheet: ' + itSs_().getUrl(); MailApp.sendEmail(me, 'Interviews today: ' + list.filter(function (r) { return r.ev.status === 'Interview today'; }).length + ' · follow-ups: ' + list.filter(function (r) { return r.ev.status !== 'Interview today'; }).length, body); if (silent !== true) itNotify_('Agenda sent to ' + me + '.'); return body; } function itDaily() { itDraftAll(true); itEmailAgenda(true); } function itAutopilotOn() { if (!itIsPro_()) return itNotify_('Daily autopilot is a Pro feature ($9 one-time).\n\nGet a key: ' + IT_LANDING + '\nThen paste it on the Settings tab (Licence key) or use Interview Tracker > Enter licence key.'); itAutopilotOff(true); ScriptApp.newTrigger(IT_DAILY).timeBased().atHour(itSettings_().hour).everyDays(1).inTimezone(itTz_()).create(); itNotify_('Autopilot is on. Every morning around ' + itSettings_().hour + ':00 it creates the due Gmail drafts and emails you the agenda. Drafts are never sent without you.'); } function itAutopilotOff(silent) { ScriptApp.getProjectTriggers().forEach(function (t) { if (t.getHandlerFunction() === IT_DAILY) ScriptApp.deleteTrigger(t); }); if (silent !== true) itNotify_('Autopilot is off.'); } function itEnterKey() { var ui = SpreadsheetApp.getUi(), r = ui.prompt('Interview Tracker Pro', 'Paste your licence key (starts with KL1-):', ui.ButtonSet.OK_CANCEL); if (r.getSelectedButton() !== ui.Button.OK) return; itSetKey(r.getResponseText()); } function itSetKey(key) { key = String(key || '').trim().replace(/\s+/g, ''); var v = KLKey.verify(key, { product: 'interview-tracker' }); if (!v.ok) { itNotify_(v.error); return v; } var st = itSheet_(IT_SET), vals = st.getRange(1, 1, Math.max(1, st.getLastRow()), 1).getValues(), row = 0; vals.forEach(function (r, i) { if (String(r[0]) === 'Licence key (Pro)') row = i + 1; }); if (!row) { row = st.getLastRow() + 1; st.getRange(row, 1).setValue('Licence key (Pro)'); } st.getRange(row, 2).setValue(key); itNotify_('Pro is active. Thank you!'); return v; } function itHelp() { itNotify_('Interview Tracker ' + IT_VERSION + '\n\nStatuses: Interview today, Interview tomorrow (reminder drafted), Not confirmed (nudge drafted), Interview passed (update the stage), No-show (rebook email drafted), Feedback overdue (chase drafted to the interviewer), Stale.\n\nEmails are created as Gmail drafts only. Edit the wording on the Templates tab ({{first}}, {{role}}, {{date}}, {{time}}, {{interviewer}}, {{location}}, {{company}}, {{sender}}).\n\nFree: ' + IT_CORE.FREE_ACTIVE + ' active candidates. Pro ($9 one-time): unlimited + daily autopilot.\n' + IT_LANDING + '\nHelp: labskaruna@gmail.com'); }