Google Apps Script - Session Audit Tool
const ALLOWED_USERS = [EMAIL ADDRESSES HERE];
function isUserAuthorized() {
const currentUser = Session.getActiveUser().getEmail();
return ALLOWED_USERS.includes(currentUser);
}
function restrictedFunction() {
if (!isUserAuthorized()) {
console.error('You are not authorized to run this function');
return;
}
createButton();
}
// Main function to create the custom menu
function createButton() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Session Audit Tool')
.addItem('Begin Audit', 'showSidebar')
.addToUi();
}
// Function to display the custom sidebar
function showSidebar() {
const html = HtmlService.createHtmlOutputFromFile('Session Audit')
.setTitle('Session Audit Tool');
SpreadsheetApp.getUi().showSidebar(html);
}
function runAudit() {
var sheetId = 'SPREADSHEET ID';
var targetSpreadsheet = SpreadsheetApp.openById(sheetId);
var resultSheet = targetSpreadsheet.getSheetByName('SHEET NAME');
var expectedHeaders = ['Tutor ID', 'Due Date', 'Notes'];
var headersMissing = true;
var defaultPriorityTutors = new Map();
if (resultSheet) {
var existingData = resultSheet.getDataRange().getValues();
existingData.slice(1).forEach(row => {
var tutorId = row[0];
var dueDate = new Date(row[1]);
var notes = row[2];
if (notes === 'Default priority: No valid QA or session audit data found.') {
defaultPriorityTutors.set(tutorId, { dueDate, notes });
Logger.log("Default Priority Tutors Map: " + JSON.stringify([...defaultPriorityTutors]));
}
});
var existingHeaders = resultSheet.getRange(1, 1, 1, resultSheet.getLastColumn()).getValues()[0];
headersMissing = expectedHeaders.some(header => !existingHeaders.includes(header));
if (resultSheet.getLastRow() > 1) {
resultSheet.getRange(2, 1, resultSheet.getLastRow() - 1, resultSheet.getLastColumn()).clearContent();
}
}
// Add headers to "Audit_Results" sheet if missing
if (headersMissing) {
resultSheet.insertRowBefore(1);
resultSheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
}
var sheetId = 'SPREADSHEET ID';
var targetSpreadsheet = SpreadsheetApp.openById(sheetId);
var resultSheet = targetSpreadsheet.getSheetByName('SHEET NAME');
if (resultSheet) {
var lastRowExternal = resultSheet.getLastRow();
if (lastRowExternal > 1) {
resultSheet.getRange(2, 1, lastRowExternal - 1, resultSheet.getLastColumn()).clearContent();
}
}
const ss = SpreadsheetApp.getActiveSpreadsheet();
const districtTabs = [LIST OF SCHOOL DISTRICTS];
Logger.log('Processing district tabs: ' + districtTabs.join(', '));
// Call updateStatusAudit for each district tab
for (let i = 0; i < districtTabs.length; i++) {
Logger.log(`Calling updateStatusAudit for sheet: ${districtTabs[i]}`);
updateStatusAudit(districtTabs[i]);
}
const tutorAuditResults = new Map(); // Stores results for each tutor
// Step 1: Process each district tab and collect session audit data
districtTabs.forEach(tab => {
const sheet = ss.getSheetByName(tab);
if (!sheet) return;
const data = sheet.getDataRange().getValues();
if (data.length < 2 || data.slice(2).every(row => row.every(cell => cell === ""))) {
Logger.log(`Skipping empty or header-only sheet: ${tab}`);
return;
}
const headers = data[1];
const rows = data.slice(2);
const tutorIdIndex = headers.indexOf('Tutor ID');
const contractStartDateIndex = headers.indexOf('Contract Start Date');
const nextSessionAuditIndex = headers.indexOf('Next Session Audit');
const statusAuditIndex = headers.indexOf('Status of Audit'); // Get the index of the 'Status of Audit' column
Logger.log(`Headers in ${tab}: ${headers.join(', ')}`);
Logger.log(`Status of Audit column index: ${statusAuditIndex}`); // Log the index of the column
if (tutorIdIndex === -1 || contractStartDateIndex === -1 || nextSessionAuditIndex === -1 || statusAuditIndex === -1) {
Logger.log(`Headers missing required columns in sheet: ${tab}`);
return;
}
const processedTutors = processRows(rows, tutorIdIndex, contractStartDateIndex, ss, defaultPriorityTutors);
// Store the earliest audit due date for each tutor
processedTutors.forEach(({ tutorId, dueDate, priorityLevel, notes }) => {
if (!tutorAuditResults.has(tutorId) || tutorAuditResults.get(tutorId).dueDate > dueDate) {
tutorAuditResults.set(tutorId, { dueDate, priorityLevel, notes });
}
});
});
// Step 2: Apply the calculated next session audit dates and status back to district tabs
districtTabs.forEach(tab => {
const sheet = ss.getSheetByName(tab);
if (!sheet) return;
const data = sheet.getDataRange().getValues();
if (data.length < 2) return;
const headers = data[1];
const rows = data.slice(2);
const tutorIdIndex = headers.indexOf('Tutor ID');
const nextSessionAuditIndex = headers.indexOf('Next Session Audit');
const statusAuditIndex = headers.indexOf('Status of Audit');
const notesIndex = headers.indexOf('Notes'); // Add this line to capture the 'Notes' column index
if (tutorIdIndex === -1 || nextSessionAuditIndex === -1 || statusAuditIndex === -1) {
Logger.log(`Headers missing required columns in sheet: ${tab}`);
return;
}
const updates = [];
const statusUpdates = [];
const notesUpdates = []; // To hold updates for 'Notes' column
rows.forEach((row, rowIndex) => {
const tutorId = row[tutorIdIndex];
const tutorAudit = tutorAuditResults.get(tutorId);
if (!tutorId || !tutorAudit) {
updates.push([row[nextSessionAuditIndex]]); // Keep existing value
statusUpdates.push(['Not Started']);
notesUpdates.push(['']); // Empty note if no audit found
} else {
updates.push([tutorAudit.dueDate]); // Update with next session audit date
statusUpdates.push(['Not Started']);
notesUpdates.push([tutorAudit.notes]); // Add the notes for this tutor
}
// Log the row update for debugging
Logger.log(`Row ${rowIndex + 1}: Tutor ID: ${tutorId}, Next Session Audit: ${tutorAudit ? tutorAudit.dueDate : 'N/A'}, Status: Not Started, Notes: ${tutorAudit ? tutorAudit.notes : ''}`);
});
const startRow = 3; // Data starts from row 3 (excluding headers)
// Update 'Next Session Audit' column
const nextSessionAuditRange = sheet.getRange(startRow, nextSessionAuditIndex + 1, rows.length, 1);
nextSessionAuditRange.setValues(updates);
// **Force the update of the 'Status of Audit' column explicitly**
const statusAuditRange = sheet.getRange(startRow, statusAuditIndex + 1, rows.length, 1);
Logger.log(`Updating Status of Audit column at range: ${statusAuditRange.getA1Notation()}`); // Log the exact range being updated
statusAuditRange.setValues(statusUpdates);
// Update 'Notes' column with the notes for each tutor
if (notesIndex !== -1) {
const notesRange = sheet.getRange(startRow, notesIndex + 1, rows.length, 1);
notesRange.setValues(notesUpdates); // Set notes for each row
}
// Log the range being updated for debugging
Logger.log(`Updated status and notes columns in range: ${statusAuditRange.getA1Notation()}`);
});
processSessionAudits();
defaultPriorityTutorList();
}
function updateStatusAudit(sheetName) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(sheetName); // Use sheet name passed as an argument
if (!sheet) {
Logger.log('Sheet not found for ' + sheetName);
return;
}
Logger.log('Processing sheet: ' + sheetName); // Log when processing starts
const data = sheet.getDataRange().getValues();
const headers = data[1];
const statusAuditIndex = headers.indexOf('Status of Audit');
if (statusAuditIndex === -1) {
Logger.log('Status of Audit column not found in ' + sheetName);
return;
}
const rows = data.slice(2); // Skip header row
const statusUpdates = rows.map(() => ['Not Started']); // Set all values to 'Not Started'
Logger.log('Updating "Status of Audit" column in sheet: ' + sheetName);
const startRow = 3; // Data starts from row 3 (excluding headers)
const statusAuditRange = sheet.getRange(startRow, statusAuditIndex + 1, rows.length, 1);
// Perform the update
statusAuditRange.setValues(statusUpdates);
Logger.log('Updated "Status of Audit" column successfully in sheet: ' + sheetName);
}
// Function to process rows and extract tutor data, including tutor name
function processRows(rows, tutorIdIndex, contractStartDateIndex, ss, defaultPriorityTutors) {
const now = new Date();
const tutorQAData = ss.getSheetByName('SHEET NAME').getDataRange().getValues();
const qaSessionAuditData = ss.getSheetByName('SHEET NAME').getDataRange().getValues();
const tutorConcernsData = ss.getSheetByName('SHEET NAME').getDataRange().getValues();
const qaHeaders = tutorQAData[0];
const sessionAuditHeaders = qaSessionAuditData[0];
const concernHeaders = tutorConcernsData[0];
const tutorQA = tutorQAData.slice(1);
const sessionAudits = qaSessionAuditData.slice(1);
const tutorConcerns = tutorConcernsData.slice(1);
const averageScoreIndex = qaHeaders.indexOf('Average Session Audit Score (out of 12)');
const latestScoreIndex = qaHeaders.indexOf('Latest Session Audit Score (out of 12)');
const sessionDateIndex = sessionAuditHeaders.indexOf('Timestamp');
const qaTutorIdIndex = qaHeaders.indexOf('Tutor ID');
const sessionAuditTutorIdIndex = sessionAuditHeaders.indexOf('Tutor ID of the tutor receiving the audit');
const concernTutorIdIndex = concernHeaders.indexOf('1099 Tutor ID');
const concernDateIndex = concernHeaders.indexOf('Date of Concern');
const tutorResults = new Map();
// Create a map of tutor names based on Tutor ID from 'Tutor_QA'
const tutorNameMap = new Map();
tutorQA.forEach(row => {
const tutorId = row[qaTutorIdIndex];
const firstName = row[qaHeaders.indexOf('Tutor First Name')];
const lastName = row[qaHeaders.indexOf('Tutor Last Name')];
tutorNameMap.set(tutorId, `${firstName} ${lastName}`);
});
rows.forEach(row => {
const tutorId = row[tutorIdIndex];
const rawContractDate = row[contractStartDateIndex];
if (!rawContractDate || isNaN(new Date(rawContractDate))) {
Logger.log(`Invalid contract start date for tutor ${tutorId}: ${rawContractDate}`);
return;
}
const contractStartDate = new Date(rawContractDate);
if (!tutorId || isNaN(contractStartDate)) return;
let dueDate = null;
let priorityLevel = 'Low';
let notes = '';
// Check for entries in QA_Tutor_Concerns sheet
const tutorConcernRecord = tutorConcerns.find(r => r[concernTutorIdIndex] === tutorId && r[concernDateIndex]);
if (tutorConcernRecord) {
dueDate = new Date(now);
dueDate.setDate(dueDate.getDate() + (7 - dueDate.getDay())); // Set to next Sunday
priorityLevel = 'High';
notes = 'Audit due based on concern entry. Scheduled every week on Sunday.';
}
// Proceed with other checks if no concerns entry found
if (!dueDate) {
const contractDueDate = new Date(contractStartDate);
contractDueDate.setDate(contractDueDate.getDate() + 7);
const tutorQARecord = tutorQA.find(r => r[qaTutorIdIndex] === tutorId);
const sessionAuditRecord = sessionAudits
.filter(r => r[sessionAuditTutorIdIndex] === tutorId)
.sort((a, b) => new Date(b[sessionDateIndex]) - new Date(a[sessionDateIndex]))[0];
if (tutorQARecord && sessionAuditRecord) {
const averageScore = parseFloat(tutorQARecord[averageScoreIndex]);
const latestScore = parseFloat(tutorQARecord[latestScoreIndex]);
const rawLastAuditDate = sessionAuditRecord[sessionDateIndex];
if (!rawLastAuditDate || isNaN(new Date(rawLastAuditDate))) {
Logger.log(`Invalid last audit date for tutor ${tutorId}: ${rawLastAuditDate}`);
return;
}
const lastAuditDate = new Date(rawLastAuditDate);
Logger.log(`Tutor ID: ${tutorId} - Average Score: ${averageScore} - Latest Score: ${latestScore} - Last Audit Date: ${lastAuditDate}`);
if (latestScore === 12) {
const threeWeeksDueDate = new Date(lastAuditDate);
threeWeeksDueDate.setDate(threeWeeksDueDate.getDate() + 21);
dueDate = threeWeeksDueDate;
notes = 'Latest session audit was 12. Next audit due in 3 weeks.';
} else if (latestScore < 12 && latestScore >= 10 && averageScore >= 10) {
const twoWeeksDueDate = new Date(lastAuditDate);
twoWeeksDueDate.setDate(twoWeeksDueDate.getDate() + 14);
dueDate = twoWeeksDueDate;
notes = 'Latest session audit > 10 but < 12. Average score > 10. Next audit due in 2 weeks.';
} else {
const nextAuditDates = [];
if (averageScore < 10) {
const avgDueDate = new Date(lastAuditDate);
avgDueDate.setDate(avgDueDate.getDate() + 14);
nextAuditDates.push(avgDueDate);
notes += 'Average score < 10. ';
}
if (latestScore < 10) {
const latestDueDate = new Date(lastAuditDate);
latestDueDate.setDate(latestDueDate.getDate() + 7);
nextAuditDates.push(latestDueDate);
notes += 'Latest score < 10. ';
}
if (nextAuditDates.length > 0) {
dueDate = nextAuditDates.sort((a, b) => a - b)[0];
priorityLevel = 'High';
}
}
}
if (!dueDate) {
if (defaultPriorityTutors.has(tutorId)) {
const defaultPriorityData = defaultPriorityTutors.get(tutorId);
if (!defaultPriorityData || isNaN(new Date(defaultPriorityData.dueDate))) {
Logger.log(`Invalid default priority due date for tutor ${tutorId}: ${defaultPriorityData.dueDate}`);
return;
}
dueDate = new Date(defaultPriorityData.dueDate);
notes = defaultPriorityData.notes;
Logger.log(`Using default priority data for Tutor ${tutorId}: Due Date - ${dueDate}, Notes - ${notes}`);
} else {
dueDate = new Date();
dueDate.setDate(now.getDate() + 14);
priorityLevel = 'High';
notes = 'Default priority: No valid QA or session audit data found.';
}
}
}
Logger.log("Calculated Due Date for Tutor " + tutorId + ": " + dueDate);
tutorResults.set(tutorId, { tutorId, dueDate, priorityLevel, notes, tutorName: tutorNameMap.get(tutorId) });
});
// Append results to "Audit_Results" sheet
const sheetId = 'SHEET ID';
const targetSpreadsheet = SpreadsheetApp.openById(sheetId);
// Access the "Audit_Results" sheet (create it if it doesn't exist)
const resultSheet = targetSpreadsheet.getSheetByName('SHEET NAME') || targetSpreadsheet.insertSheet('Audit_Results');
if (resultSheet.getLastRow() === 0) {
resultSheet.appendRow(['Tutor ID', 'Tutor Name', 'Next Session Audit', 'Priority Level', 'Notes']);
}
// Create a set of existing tutor IDs in "Audit_Results"
const lastRow = resultSheet.getLastRow();
const existingTutorIds = new Set();
if (lastRow > 1) {
const tutorIdRange = resultSheet.getRange(2, 1, lastRow - 1, 1).getValues();
tutorIdRange.forEach(row => existingTutorIds.add(row[0]));
}
const auditResults = Array.from(tutorResults.values());
auditResults.forEach(result => {
if (!existingTutorIds.has(result.tutorId)) {
resultSheet.appendRow([result.tutorId, result.tutorName, result.dueDate.toISOString().split('T')[0], result.priorityLevel, result.notes]);
existingTutorIds.add(result.tutorId); // Ensure tutor ID is added to the existing set
}
});
// Sort the results by 'Next Session Audit' date
const range = resultSheet.getRange(2, 1, resultSheet.getLastRow() - 1, resultSheet.getLastColumn());
range.sort({ column: 3, ascending: true });
return auditResults;
}
function processSessionAudits() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
// Load data from 'QA_Session_Audit_Form_Response' tab
const formSheet = ss.getSheetByName("SHEET NAME");
if (!formSheet) {
Logger.log("The 'SHEET NAME' sheet is missing.");
return;
}
const formData = formSheet.getDataRange().getValues();
const formHeaders = formData[0];
const tutorIdIndex = formHeaders.indexOf('Tutor ID of the tutor receiving the audit');
const contractIndex = formHeaders.indexOf('Session Audit Contract');
const pssNameIndex = formHeaders.indexOf('PSS/Auditor Name');
if (tutorIdIndex === -1 || contractIndex === -1 || pssNameIndex === -1) {
Logger.log("One or more required columns are missing in the QA form.");
return;
}
// Updated contract-to-tab mapping
const contractToTabMap = {CONTRACT NAME: SHEET NAME};
const familyTutoringPSS = "SPECIFIC USER";
let auditCounts = {};
let pssCounts = {};
// Process each row from the form responses to count Tutor ID + Contract combinations
for (let i = 1; i < formData.length; i++) {
let tutorId = formData[i][tutorIdIndex].toString().trim();
let contract = formData[i][contractIndex].toString().trim();
let pssName = formData[i][pssNameIndex].toString().trim();
if (!tutorId || !contract || !pssName) continue;
// Create a key from Tutor ID + Contract to count occurrences
let key = tutorId + "_" + contract;
if (!auditCounts[key]) auditCounts[key] = 0;
// Increment the count for this Tutor ID + Contract combination
auditCounts[key]++;
// Track PSS counts
if (!pssCounts[contract]) pssCounts[contract] = {};
if (!pssCounts[contract][pssName]) pssCounts[contract][pssName] = {};
if (!pssCounts[contract][pssName][tutorId]) pssCounts[contract][pssName][tutorId] = 0;
pssCounts[contract][pssName][tutorId]++;
}
// Load the other Google Sheet for PSS information
const pssSheet = SpreadsheetApp.openById("SPREADSHEET ID");
const pssData = pssSheet.getSheetByName("SHEET NAME");
if (!pssData) {
Logger.log("The 'active_operations' sheet in the PSS document is missing.");
return;
}
// Headers are in row 2
const pssValues = pssData.getRange(2, 1, pssData.getLastRow() - 1, pssData.getLastColumn()).getValues();
const pssHeaders = pssValues[0];
const contractIndexPSS = pssHeaders.indexOf('Contract');
const pssNameIndexPSS = pssHeaders.indexOf('Partnership Success Specialist');
if (contractIndexPSS === -1 || pssNameIndexPSS === -1) {
Logger.log("Required columns are missing in the PSS sheet.");
return;
}
let contractToPSSMap = {};
for (let i = 1; i < pssValues.length; i++) {
let contract = pssValues[i][contractIndexPSS].toString().trim();
let pssName = pssValues[i][pssNameIndexPSS].toString().trim();
if (!contract || !pssName) continue;
if (contract === "Family Tutoring") {
contractToPSSMap[contract] = familyTutoringPSS;
} else {
contractToPSSMap[contract] = pssName;
}
}
// Update the corresponding audit sheet with the counts
for (let contract in contractToTabMap) {
let auditSheetName = contractToTabMap[contract];
const auditSheet = ss.getSheetByName(auditSheetName);
if (!auditSheet) continue;
const auditData = auditSheet.getDataRange().getValues();
const auditHeaders = auditData[1];
const tutorIdIndexInAudit = auditHeaders.indexOf('Tutor ID');
const auditCountIndex = auditHeaders.indexOf('Total Session Audits');
const auditCountByPssIndex = auditHeaders.indexOf('Session Audits by PSS');
if (tutorIdIndexInAudit === -1 || auditCountIndex === -1 || auditCountByPssIndex === -1) {
Logger.log(`Required columns are missing in the ${auditSheetName} tab.`);
continue;
}
// Process each row in the audit sheet
for (let i = 2; i < auditData.length; i++) {
let tutorId = auditData[i][tutorIdIndexInAudit].toString().trim();
let key = tutorId + "_" + contract;
let count = auditCounts[key] || 0;
let pssCount = 0;
let pssName = contract === "Family Tutoring" ? familyTutoringPSS : contractToPSSMap[contract];
if (pssCounts[contract] && pssCounts[contract][pssName] && pssCounts[contract][pssName][tutorId]) {
pssCount = pssCounts[contract][pssName][tutorId];
}
// Log the match and count for debugging
Logger.log(`Tutor ID: ${tutorId} - Contract: ${contract} - Count: ${count}`);
Logger.log(`Tutor ID: ${tutorId} - PSS: ${pssName} - Count: ${pssCount}`);
// Set the Session Audits column
auditSheet.getRange(i + 1, auditCountIndex + 1).setValue(count);
auditSheet.getRange(i + 1, auditCountByPssIndex + 1).setValue(`${pssCount} - ${pssName}`);
}
}
}
function defaultPriorityTutorList() {
var sheetId = 'SPREADSHEET ID';
var targetSpreadsheet = SpreadsheetApp.openById(sheetId);
var resultSheet = targetSpreadsheet.getSheetByName('Audit_Results');
var outputSheet = targetSpreadsheet.getSheetByName('DefaultPriorityTutors');
var expectedHeaders = ['Tutor ID', 'Due Date', 'Notes'];
var defaultPriorityTutors = new Map();
if (resultSheet && outputSheet) {
var existingData = resultSheet.getDataRange().getValues();
existingData.slice(1).forEach(row => {
var tutorId = row[0];
var dueDate = new Date(row[2]);
var priority = row[3];
var notes = row[4];
if (notes === 'Default priority: No valid QA or session audit data found.') {
defaultPriorityTutors.set(tutorId, [tutorId, dueDate, notes]);
}
});
// Convert Map to an array for writing to the sheet
var dataToWrite = Array.from(defaultPriorityTutors.values());
// Clear existing data (optional, if you want to overwrite)
outputSheet.clearContents();
// Write headers
outputSheet.getRange(1, 1, 1, expectedHeaders.length).setValues([expectedHeaders]);
// Write collected data if any
if (dataToWrite.length > 0) {
outputSheet.getRange(2, 1, dataToWrite.length, expectedHeaders.length).setValues(dataToWrite);
}
Logger.log("Data written to DefaultPriorityTutors: " + JSON.stringify(dataToWrite));
} else {
Logger.log("One or both sheets not found.");
}
}
