function createTAEvaluationForm() {
// 1. Create the Form
var form = FormApp.create('Confidential TA Evaluation Survey - BASM');
// 2. Force School Email Collection & Privacy
form.setCollectEmail(true);
try {
form.setRequireLogin(true); // Restricts to your school's Google Workspace domain
} catch(e) {
// Fails quietly if you are testing on a personal Gmail, but works on School accounts
}
form.setDescription('Confidential SLT Review\nDear Teachers, this evaluation is a direct, private communication between you and the Senior Leadership Team (SLT). The contents of this form are strictly confidential and will never be shared with the Teaching Assistant. Please be completely honest and objective.');
// --- SECTION 1: General Info ---
form.addTextItem()
.setTitle('1. Teacher Name')
.setRequired(true);
var taNames = [
'Mrs Assia Lahkim', 'Mr Aissam El Baroudi', 'Ms Khaoula Zitoune',
'Ms Wafa El Hajoubi', 'Ms Malika Ait Sidi Ali', 'Mr Ayman Khafou',
'Ms Sanaa Boudi', 'Mr El Mustapha Fatih', 'Ms Imane Benabid',
'Ms Imane Asehraoui', 'Ms Houda Hirouf', 'Ms Hafsa chaik',
'Ms Ghita Elaachiki', 'Ms Salma Abouzid'
];
form.addListItem()
.setTitle('2. Teaching Assistant (TA) Name')
.setChoiceValues(taNames)
.setRequired(true);
form.addMultipleChoiceItem()
.setTitle('3. Your Role')
.setChoiceValues(['Main Classroom Teacher (British Curriculum)', 'Specialist Teacher (Arabic, French, PE, Music, Art, ICT)'])
.setRequired(true);
// --- SECTION 2: Classroom Engagement ---
form.addPageBreakItem().setTitle('Section 2: Classroom Engagement & Proactivity');
var frequencyScale = ['Always (Consistent daily practice)', 'Most of the time (Reliable, needs minor reminders)', 'Sometimes (Inconsistent; requires regular tracking)', 'Rarely / Never (Fails to support)'];
form.addMultipleChoiceItem()
.setTitle('4. Proactive Support during Whole-Class Teaching: Does the TA actively scan the room and keep children on task without waiting for instruction?')
.setChoiceValues(frequencyScale).setRequired(true);
form.addMultipleChoiceItem()
.setTitle('5. Support during Lesson Cover: When managing the class together, do they actively manage student behavior?')
.setChoiceValues(frequencyScale).setRequired(true);
form.addMultipleChoiceItem()
.setTitle('6. Resource Preparation: Does the TA independently prep learning resources and tidy up efficiently?')
.setChoiceValues(frequencyScale).setRequired(true);
// --- SECTION 3: Immersion & Culture ---
form.addPageBreakItem().setTitle('Section 3: Language Immersion, Inclusion & Cultural Dynamics');
form.addMultipleChoiceItem()
.setTitle('7. English Language Immersion (EAL): Does the TA consistently speak English with the students, avoiding Arabic/French unless strictly necessary?')
.setChoiceValues(frequencyScale).setRequired(true);
form.addMultipleChoiceItem()
.setTitle('8. Support for SEND: How well does the TA implement adjustments for children on the inclusion register?')
.setChoiceValues(frequencyScale).setRequired(true);
form.addMultipleChoiceItem()
.setTitle('9. Respect Across Diversity: Does the TA show the exact same level of respect and work ethic to BOTH British Main Teachers and Moroccan Specialist Teachers?')
.setChoiceValues(frequencyScale).setRequired(true);
// --- SECTION 4: Safeguarding ---
form.addPageBreakItem().setTitle('Section 4: The Safeguarding & Vigilance Lens');
form.addMultipleChoiceItem()
.setTitle('10. Vigilance During Duties: During playground duty or transitions, is the TA fully focused on student safety?')
.setChoiceValues([
'Fully Focused: Excellent safety awareness.',
'Generally Alert: Good, but occasionally needs a reminder.',
'Easily Distracted: Frequently on phone, sitting, or ignoring students.'
]).setRequired(true);
form.addMultipleChoiceItem()
.setTitle('11. Professional Boundaries: Does the TA maintain professional boundaries in line with UK Child Protection standards?')
.setChoiceValues(['Yes', 'No (If No, SLT will contact you immediately)']).setRequired(true);
// --- SECTION 5: Written Reports ---
form.addPageBreakItem().setTitle('Section 5: Confidential Reports');
form.addParagraphTextItem()
.setTitle('12. Confidential Professional Commentary & Evidence: If this TA is a "passenger" or lacks professionalism, please write specific examples here. Your name is protected by SLT.')
.setRequired(false);
form.addParagraphTextItem()
.setTitle('13. Final Catch-All: Please use this space to mention anything not covered above (positive or negative).')
.setRequired(false);
// --- SECTION 6: Head of School Matrix ---
form.addPageBreakItem().setTitle('Section 6: Head of School Contract Matrix');
form.addMultipleChoiceItem()
.setTitle('14. Impact on Teacher Workload: Does having this TA make your job easier, or add to your workload?')
.setChoiceValues([
'They greatly reduce my workload (Asset)',
'They balance out evenly',
'They increase my workload because I have to manage them constantly (Liability)'
]).setRequired(true);
form.addMultipleChoiceItem()
.setTitle('15. Retention Recommendation: What is your professional recommendation regarding this TA\'s contract?')
.setChoiceValues([
'Renew Contract: Exceptional or highly proficient.',
'Renew with Strict Performance Targets: Needs training on proactivity.',
'Do Not Renew: Unsuitable for our school environment.'
]).setRequired(true);
Logger.log('Form published URL: ' + form.getPublishedUrl());
Logger.log('Form edit URL: ' + form.getEditUrl());
}
__________________________________________________________________________________________
/**
* BASM Automated TA Evaluation Report Generator
* Reads data from the connected Form Responses and outputs a formatted Google Doc dossier.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Report Tools')
.addItem('📄 Generate Google Doc for Selected TA', 'generateTADocReport')
.addToUi();
}
function generateTADocReport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var dashboardSheet = ss.getSheetByName('TA Dashboard'); // Your dashboard tab
var responseSheet = ss.getSheetByName('Form Responses 1'); // The tab with form data
var ui = SpreadsheetApp.getUi();
if (!dashboardSheet || !responseSheet) {
ui.alert('Error', 'Make sure your tabs are named "TA Dashboard" and "Form Responses 1".', ui.ButtonSet.OK);
return;
}
// Get the TA selected from your Dropdown in A1
var selectedTA = dashboardSheet.getRange('A1').getValue();
if (!selectedTA || selectedTA === "") {
ui.alert('Missing Selection', 'Please select a TA from the dropdown in cell A1 first.', ui.ButtonSet.OK);
return;
}
// Fetch all response data
var data = responseSheet.getDataRange().getValues();
var headers = data[0];
// Dynamically find which columns contain the TA name and Teacher Name
var taCol = -1;
var teacherCol = -1;
for(var i = 0; i < headers.length; i++) {
var headerText = headers[i].toString().toLowerCase();
if(headerText.indexOf('teaching assistant (ta) name') > -1) taCol = i;
if(headerText.indexOf('teacher name') > -1) teacherCol = i;
}
if (taCol === -1) {
ui.alert('Column Error', 'Could not find the "Teaching Assistant (TA) Name" column in the Form Responses tab.', ui.ButtonSet.OK);
return;
}
// Filter the data to ONLY include the selected TA
var taData = data.filter(function(row, index) {
return index > 0 && row[taCol] === selectedTA; // index > 0 skips headers
});
if (taData.length === 0) {
ui.alert('No Data', 'No evaluations have been submitted yet for ' + selectedTA + '.', ui.ButtonSet.OK);
return;
}
// ---------------------------------------------------------
// GOOGLE DOC GENERATION ENGINE
// ---------------------------------------------------------
// Create a new Google Doc in your Drive
var docName = 'SLT Confidential Report: ' + selectedTA;
var doc = DocumentApp.create(docName);
var body = doc.getBody();
// Document Header
body.insertParagraph(0, 'British Academy School Marrakech').setHeading(DocumentApp.ParagraphHeading.HEADING2).setAlignment(DocumentApp.HorizontalAlignment.CENTER);
body.appendParagraph('Teaching Assistant Evaluation Dossier').setHeading(DocumentApp.ParagraphHeading.TITLE).setAlignment(DocumentApp.HorizontalAlignment.CENTER);
body.appendParagraph('Subject: ' + selectedTA).setHeading(DocumentApp.ParagraphHeading.HEADING1);
body.appendParagraph('Total Evaluations Received: ' + taData.length).setItalic(true);
body.appendParagraph('Date Generated: ' + new Date().toLocaleDateString());
body.appendHorizontalRule();
// Loop through each teacher's evaluation and build the report
taData.forEach(function(row) {
var evaluator = teacherCol !== -1 ? row[teacherCol] : 'Unknown Teacher';
var sectionHeader = body.appendParagraph('Evaluated by: ' + evaluator);
sectionHeader.setHeading(DocumentApp.ParagraphHeading.HEADING2);
// Loop through every question and answer for this specific teacher
for(var j = 0; j < headers.length; j++) {
// Skip Timestamp, Teacher Name, and TA Name columns to keep report clean
if(j !== taCol && j !== teacherCol && j !== 0 && row[j] !== '') {
var questionText = body.appendParagraph(headers[j]);
questionText.setBold(true);
var answerText = body.appendParagraph(row[j].toString() + '\n');
answerText.setBold(false);
// Highlight critical negative responses in red text
if (row[j].toString().indexOf('Rarely / Never') > -1 || row[j].toString().indexOf('Do Not Renew') > -1 || row[j].toString().indexOf('Liability') > -1) {
answerText.setForegroundColor('#cc0000'); // Deep Red
answerText.setBold(true);
}
}
}
body.appendHorizontalRule();
});
// Save and close the document to ensure URL is ready
doc.saveAndClose();
// Alert the Head of School with success message and link
var htmlOutput = HtmlService
.createHtmlOutput('<p>Report generated successfully!</p><p><a href="' + doc.getUrl() + '" target="_blank" style="font-size: 16px; color: #1a73e8; font-weight: bold;">Click here to open the Google Doc Report</a></p>')
.setWidth(350)
.setHeight(150);
ui.showModalDialog(htmlOutput, '✅ Dossier Created');
}