/**
* CSM Response Time Tracker
* Builds the whole workbook natively in Google Sheets.
* Run buildTracker() once. Safe to re-run - it rebuilds all four tabs from scratch.
*/
var BLACK = '#1B1B1B';
var BRICK = '#F63100';
var CEMENT = '#F2F0ED';
var GREY = '#6B6B6B';
var LINE = '#D5D0CA';
var LAST = 501; // last data row in the log
var MAX_CSM = 12; // rows available for CSM names
var REASONS = [
'In auto comm inbox',
'CSA mistake',
'In personal inbox',
'Front / API / automation setup',
'In the wrong inbox (Onboarding / Expansion / non-CX)',
'Routed to wrong CSM',
'Parahelp resolving / buried',
'No message, but flagged in Slack',
'Other (free text - explain in Notes)'
];
var CSMS = [
'Peter Sanderson'
];
var LOG_COLS = [
'Date of Slow Response',
'CSM',
'Customer / Pro Name',
'Slug',
'Front Conversation Link',
'Missed it because...',
'Notes - what actually happened'
];
function buildTracker() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var lists = resetSheet(ss, 'Lists');
var log = resetSheet(ss, 'Response Log');
var dash = resetSheet(ss, 'Dashboard');
var how = resetSheet(ss, 'How to Use');
buildLists(lists);
buildLog(log, lists);
buildDashboard(dash);
buildHowTo(how);
// Final tab order
ss.setActiveSheet(log); ss.moveActiveSheet(1);
ss.setActiveSheet(dash); ss.moveActiveSheet(2);
ss.setActiveSheet(how); ss.moveActiveSheet(3);
ss.setActiveSheet(lists); ss.moveActiveSheet(4);
// Drop the default empty sheet if it is still hanging around
var leftover = ss.getSheetByName('Sheet1');
if (leftover && ss.getSheets().length > 4) ss.deleteSheet(leftover);
ss.setActiveSheet(log);
ss.rename('CSM Response Time Tracker');
}
function resetSheet(ss, name) {
var sh = ss.getSheetByName(name);
if (sh) {
sh.clear();
sh.clearConditionalFormatRules();
if (sh.getMaxRows() > 1) sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns()).setDataValidation(null);
} else {
sh = ss.insertSheet(name);
}
sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns())
.setFontFamily('Arial').setFontSize(10).setFontColor(BLACK)
.setBackground(null).setFontWeight('normal').setFontStyle('normal');
return sh;
}
function headerRow(sh, row, values) {
var rng = sh.getRange(row, 1, 1, values.length);
rng.setValues([values])
.setBackground(BLACK).setFontColor('#FFFFFF').setFontWeight('bold')
.setVerticalAlignment('middle').setWrap(true)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.setRowHeight(row, 34);
}
function sectionLabel(sh, row, text) {
sh.getRange(row, 1).setValue(text).setFontSize(12).setFontWeight('bold');
}
/* ------------------------------------------------------------------ Lists */
function buildLists(sh) {
headerRow(sh, 1, ['CSM Names', 'Reasons']);
var names = [];
for (var i = 0; i < MAX_CSM; i++) names.push([CSMS[i] || '']);
sh.getRange(2, 1, MAX_CSM, 1).setValues(names)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
var reasons = REASONS.map(function (r) { return [r]; });
sh.getRange(2, 2, reasons.length, 1).setValues(reasons)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.getRange('D2').setValue(
'Add CSM names in column A (rows 2-' + (MAX_CSM + 1) + '). They feed the dropdown on the ' +
'Response Log and the breakdown on the Dashboard. Reasons in column B feed the Reason ' +
'dropdown. If you add a 10th reason, extend the dropdown range and add a matching row ' +
'on the Dashboard.'
).setFontStyle('italic').setFontColor(GREY).setWrap(true).setVerticalAlignment('top');
sh.setColumnWidth(1, 200);
sh.setColumnWidth(2, 320);
sh.setColumnWidth(3, 30);
sh.setColumnWidth(4, 420);
sh.setRowHeight(2, 90);
}
/* ----------------------------------------------------------- Response Log */
function buildLog(sh, lists) {
headerRow(sh, 1, LOG_COLS);
var widths = [160, 150, 190, 150, 300, 230, 380];
for (var i = 0; i < widths.length; i++) sh.setColumnWidth(i + 1, widths[i]);
sh.getRange(2, 1, LAST - 1, 1).setNumberFormat('mm/dd/yyyy');
sh.getRange(2, 7, LAST - 1, 1).setWrap(true).setVerticalAlignment('top');
sh.getRange(2, 1, LAST - 1, LOG_COLS.length).setVerticalAlignment('top');
sh.setFrozenRows(1);
var reasonRule = SpreadsheetApp.newDataValidation()
.requireValueInRange(lists.getRange(2, 2, REASONS.length, 1), true)
.setAllowInvalid(false)
.setHelpText('Missed it because... Pick from the dropdown, or pick Other and explain in Notes.')
.build();
sh.getRange(2, 6, LAST - 1, 1).setDataValidation(reasonRule);
var csmRule = SpreadsheetApp.newDataValidation()
.requireValueInRange(lists.getRange(2, 1, MAX_CSM, 1), true)
.setAllowInvalid(false)
.setHelpText('Add your name on the Lists tab if it is not in the dropdown.')
.build();
sh.getRange(2, 2, LAST - 1, 1).setDataValidation(csmRule);
// Banding for readability
sh.getRange(2, 1, LAST - 1, LOG_COLS.length)
.applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false);
}
/* ------------------------------------------------------------- Dashboard */
function buildDashboard(sh) {
var LOG = "'Response Log'";
sh.setColumnWidth(1, 330);
sh.setColumnWidth(2, 90);
sh.setColumnWidth(3, 90);
for (var c = 4; c <= 3 + MAX_CSM; c++) sh.setColumnWidth(c, 110);
sh.getRange('A1').setValue('CSM Slow Response - Summary').setFontSize(16).setFontWeight('bold');
sh.getRange('A2').setValue('Auto-calculated from the Response Log tab. Nothing to fill in here.')
.setFontStyle('italic').setFontColor(GREY);
sh.getRange('A4').setValue('Total slow responses logged').setFontSize(11).setFontWeight('bold');
sh.getRange('B4').setFormula('=COUNTA(' + LOG + '!$A$2:$A$' + LAST + ')')
.setFontSize(14).setFontWeight('bold').setFontColor(BRICK);
/* --- By reason --- */
sectionLabel(sh, 6, 'By reason');
headerRow(sh, 7, ['Reason', 'Count', '% of all']);
var rRows = REASONS.length;
var rF = [];
for (var i = 0; i < rRows; i++) {
var r = 8 + i;
rF.push([
'=Lists!$B$' + (2 + i),
'=COUNTIF(' + LOG + '!$F$2:$F$' + LAST + ',$A' + r + ')',
'=IFERROR($B' + r + '/$B$4,"")'
]);
}
sh.getRange(8, 1, rRows, 3).setFormulas(rF)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.getRange(8, 2, rRows, 2).setHorizontalAlignment('center');
sh.getRange(8, 3, rRows, 1).setNumberFormat('0.0%');
var lastReason = 7 + rRows; // 16
/* --- By CSM --- */
var csmSection = lastReason + 2; // 18
sectionLabel(sh, csmSection, 'By CSM');
headerRow(sh, csmSection + 1, ['CSM', 'Count', '% of all']);
var firstCsm = csmSection + 2; // 20
var cF = [];
for (var j = 0; j < MAX_CSM; j++) {
var cr = firstCsm + j;
var src = 'Lists!$A$' + (2 + j);
cF.push([
'=IF(' + src + '="","",' + src + ')',
'=IF($A' + cr + '="","",COUNTIF(' + LOG + '!$B$2:$B$' + LAST + ',$A' + cr + '))',
'=IFERROR($B' + cr + '/$B$4,"")'
]);
}
sh.getRange(firstCsm, 1, MAX_CSM, 3).setFormulas(cF)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.getRange(firstCsm, 2, MAX_CSM, 2).setHorizontalAlignment('center');
sh.getRange(firstCsm, 3, MAX_CSM, 1).setNumberFormat('0.0%');
var lastCsm = firstCsm + MAX_CSM - 1; // 31
/* --- Reason by CSM --- */
var xtSection = lastCsm + 2; // 33
sectionLabel(sh, xtSection, 'Reason by CSM');
var xtHead = xtSection + 1; // 34
var headFormulas = [['Reason']];
for (var k = 0; k < MAX_CSM; k++) {
var hs = 'Lists!$A$' + (2 + k);
headFormulas[0].push('=IF(' + hs + '="","",' + hs + ')');
}
var headRng = sh.getRange(xtHead, 1, 1, MAX_CSM + 1);
headRng.setFormulas(headFormulas)
.setBackground(BLACK).setFontColor('#FFFFFF').setFontWeight('bold')
.setVerticalAlignment('middle').setWrap(true).setHorizontalAlignment('center')
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.getRange(xtHead, 1).setHorizontalAlignment('left');
sh.setRowHeight(xtHead, 34);
var xF = [];
for (var m = 0; m < rRows; m++) {
var xr = xtHead + 1 + m;
var line = ['=Lists!$B$' + (2 + m)];
for (var n = 0; n < MAX_CSM; n++) {
var cl = columnLetter(2 + n);
line.push(
'=IF(' + cl + '$' + xtHead + '="","",COUNTIFS(' +
LOG + '!$F$2:$F$' + LAST + ',$A' + xr + ',' +
LOG + '!$B$2:$B$' + LAST + ',' + cl + '$' + xtHead + '))'
);
}
xF.push(line);
}
sh.getRange(xtHead + 1, 1, rRows, MAX_CSM + 1).setFormulas(xF)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.getRange(xtHead + 1, 2, rRows, MAX_CSM).setHorizontalAlignment('center');
var lastXt = xtHead + rRows; // 43
/* --- Last 12 months --- */
var mSection = lastXt + 2; // 45
sectionLabel(sh, mSection, 'Last 12 months');
headerRow(sh, mSection + 1, ['Month', 'Count']);
var firstMonth = mSection + 2; // 47
var mF = [];
for (var p = 0; p < 12; p++) {
var mr = firstMonth + p;
var offset = -(11 - p);
mF.push([
'=TEXT(EOMONTH(TODAY(),' + offset + '),"YYYY-MM")',
'=SUMPRODUCT((TEXT(' + LOG + '!$A$2:$A$' + LAST + ',"YYYY-MM")=$A' + mr + ')*1)'
]);
}
sh.getRange(firstMonth, 1, 12, 2).setFormulas(mF)
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.getRange(firstMonth, 2, 12, 1).setHorizontalAlignment('center');
}
function columnLetter(n) {
var s = '';
while (n > 0) {
var rem = (n - 1) % 26;
s = String.fromCharCode(65 + rem) + s;
n = Math.floor((n - 1) / 26);
}
return s;
}
/* ------------------------------------------------------------ How to Use */
function buildHowTo(sh) {
sh.setColumnWidth(1, 320);
sh.setColumnWidth(2, 620);
sh.getRange('A1').setValue('How to use this tracker').setFontSize(16).setFontWeight('bold');
var lines = [
['Purpose', 'Log every conversation where the response took longer than it should have, and why. The point is pattern-spotting, not blame. The Dashboard shows which causes repeat.'],
['Who fills it in', 'Each CSM logs their own. Self-reported.'],
['When', 'Log it the same day you notice it, while you still remember the reason.'],
['', ''],
['Date of Slow Response', 'The date the customer was waiting. Not the date you logged it.'],
['CSM', 'Your name, from the dropdown. Add yourself on the Lists tab if you are not there.'],
['Customer / Pro Name', 'Business name as it appears in Front or HubSpot.'],
['Slug', 'Topline slug, if you have it handy. Optional.'],
['Front Conversation Link', 'Paste the Front conversation URL so anyone can open the thread. If there was no Front message, paste the Slack permalink instead.'],
['Missed it because...', 'Pick one from the dropdown. If nothing fits, pick Other and write the real reason in Notes.'],
['Notes', 'One or two sentences on what actually happened. Specific beats polished.'],
['', ''],
['Reason definitions', 'Missed it because...'],
['In auto comm inbox', 'The reply landed in the automated comms inbox instead of mine, so it was not in my normal queue.'],
['CSA mistake', 'A CSA handling error put the conversation in the wrong place or left it unanswered.'],
['In personal inbox', 'It came to my individual inbox rather than a shared or team inbox, and got buried.'],
['Front / API / automation setup', 'A rule, integration, or automation did not fire the way it should have.'],
['In the wrong inbox (Onboarding / Expansion / non-CX)', 'It was sitting in a non-CX inbox nobody on CX was watching.'],
['Routed to wrong CSM', 'It was assigned to a CSM who does not own the account.'],
['Parahelp resolving / buried', 'Parahelp was working the conversation or had marked it resolved, so it never surfaced as needing a human reply.'],
['No message, but flagged in Slack', 'Nothing came through Front. The issue was raised in Slack, so there was no conversation to respond to. Leave the Front link blank and paste the Slack permalink instead.'],
['Other (free text - explain in Notes)', 'Anything else. Write the real reason in Notes. If the same Other reason shows up three times, add it to the Lists tab as its own option.']
];
sh.getRange(3, 1, lines.length, 2).setValues(lines)
.setWrap(true).setVerticalAlignment('top');
sh.getRange(3, 1, lines.length, 1).setFontWeight('bold');
var exRow = 3 + lines.length + 1;
sh.getRange(exRow, 1).setValue('Example row').setFontSize(12).setFontWeight('bold').setFontColor(BRICK);
headerRow(sh, exRow + 1, LOG_COLS);
var example = [[
'08/18/2026',
'Peter Sanderson',
'Eaglewood Gutters',
'eaglewood-gutters',
'https://app.frontapp.com/open/cnv_abc123',
'In the wrong inbox (Onboarding / Expansion / non-CX)',
'Rob replied on the Onboarding inbox thread. It sat there two days before anyone on CX saw it and assigned it to me.'
]];
sh.getRange(exRow + 2, 1, 1, LOG_COLS.length).setValues(example)
.setFontStyle('italic').setFontColor(GREY).setWrap(true).setVerticalAlignment('top')
.setBorder(true, true, true, true, true, true, LINE, SpreadsheetApp.BorderStyle.SOLID);
sh.setRowHeight(exRow + 2, 60);
for (var i = 3; i <= LOG_COLS.length; i++) sh.setColumnWidth(i, 200);
}