Weekly Cover Sheet

Create a Google Sheet, copy its Sheet ID from the URL, open Extensions → Apps Script, paste this script, replace PASTE_YOUR_SHEET_ID_HERE, then deploy as a Web App. This writes the spreadsheet in the cover-sheet format: weekdays across the top, Additional events above Lesson 1, and lesson/duty rows down the side. Manual edits in the Additional events, Duties, ASP, and Homework club rows are kept during updates.

const SPREADSHEET_ID = 'PASTE_YOUR_SHEET_ID_HERE';
const SCRIPT_VERSION = 'cover-grid-v5-preserve-manual-rows';

function doGet() {
  return jsonOutput({ ok: true, version: SCRIPT_VERSION, message: 'Cover Sheet script is live' });
}

function doPost(e) {
  try {
    const payload = JSON.parse(e.postData.contents || '{}');
    const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
    const sheetName = 'Week ' + payload.week;
    const oldSheet = ss.getSheetByName(sheetName);
    const preservedManualRows = oldSheet ? readManualRowsToPreserve(oldSheet) : {};

    // Recreate the Week tab each time so old row-based formatting cannot remain.
    if (oldSheet) ss.deleteSheet(oldSheet);
    const sheet = ss.insertSheet(sheetName);

    const rows = mergePreservedManualRows(normaliseCoverGrid(payload), preservedManualRows);
    sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);

    formatCoverSheet(sheet, rows.length, rows[0].length);
    boldNamesInCoverCells(sheet, rows.length, rows[0].length);

    return jsonOutput({ ok: true, version: SCRIPT_VERSION, week: payload.week, layout: 'days-as-columns-lessons-as-rows', namesBold: true, preservesManualRows: true });
  } catch (err) {
    return jsonOutput({ ok: false, version: SCRIPT_VERSION, error: String(err) });
  }
}

function normaliseCoverGrid(payload) {
  const days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday'];
  const expectedLabels = ['Additional events', 'Lesson 1', 'Lesson 2', 'Lesson 3', 'Lesson 4', 'Lesson 5', 'Lesson 6', 'Lesson 7', 'Duties', 'ASP', 'Homework club'];

  // New app payload already sends the correct spreadsheet grid.
  if (Array.isArray(payload.table) && payload.table.length) {
    const table = payload.table.map(row => row.map(cell => cell == null ? '' : String(cell)));
    if (table[0] && table[0].length === 6 && days.every((day, i) => table[0][i + 1] === day)) {
      return table;
    }
  }

  // Fallback for older app payloads: rebuild the spreadsheet grid from flat rows.
  const flatRows = Array.isArray(payload.rows) ? payload.rows : [];
  const rows = [['', ...days]];
  expectedLabels.forEach(label => {
    if (!label.startsWith('Lesson ')) {
      rows.push([label, '', '', '', '', '']);
      return;
    }

    const periodNumber = label.replace('Lesson ', '');
    rows.push([
      label,
      ...days.map(day => flatRows
        .filter(r => r.day === day && String(r.period || '').replace('P', 'Lesson ') === 'Lesson ' + periodNumber)
        .map(r => `${r.absentTeacher || ''} absent — ${r.lesson || ''}\nCover: ${r.coverName || 'not assigned'}${r.coverNote ? ' (' + r.coverNote + ')' : ''}`)
        .join('\n\n'))
    ]);
  });
  return rows;
}

function readManualRowsToPreserve(sheet) {
  const labelsToPreserve = ['Additional events', 'Duties', 'ASP', 'Homework club'];
  const values = sheet.getDataRange().getValues();
  const preserved = {};

  values.forEach(row => {
    const label = String(row[0] || '').trim();
    if (labelsToPreserve.includes(label)) {
      preserved[label] = row.slice(1, 6).map(cell => cell == null ? '' : String(cell));
    }
  });

  return preserved;
}

function mergePreservedManualRows(rows, preservedManualRows) {
  return rows.map(row => {
    const label = String(row[0] || '').trim();
    if (!preservedManualRows[label]) return row;

    const merged = row.slice();
    preservedManualRows[label].forEach((value, index) => {
      if (value) merged[index + 1] = value;
    });
    return merged;
  });
}

function formatCoverSheet(sheet, rowCount, columnCount) {
  sheet.clearFormats();
  sheet.setFrozenRows(1);
  sheet.setFrozenColumns(1);

  const all = sheet.getRange(1, 1, rowCount, columnCount);
  all
    .setWrap(true)
    .setVerticalAlignment('top')
    .setFontFamily('Arial')
    .setFontSize(10)
    .setBorder(true, true, true, true, true, true, '#d0d7de', SpreadsheetApp.BorderStyle.SOLID);

  sheet.getRange(1, 1, 1, columnCount)
    .setFontWeight('bold')
    .setBackground('#285c4d')
    .setFontColor('#ffffff')
    .setHorizontalAlignment('center');

  sheet.getRange(1, 1, rowCount, 1)
    .setFontWeight('bold')
    .setBackground('#efe8da')
    .setHorizontalAlignment('center');

  sheet.setColumnWidth(1, 130);
  for (let col = 2; col <= columnCount; col++) sheet.setColumnWidth(col, 240);
  sheet.setRowHeight(1, 34);
  for (let row = 2; row <= rowCount; row++) sheet.setRowHeight(row, 92);
}

function boldNamesInCoverCells(sheet, rowCount, columnCount) {
  if (rowCount < 2 || columnCount < 2) return;

  const range = sheet.getRange(2, 2, rowCount - 1, columnCount - 1);
  const values = range.getValues();
  const boldStyle = SpreadsheetApp.newTextStyle().setBold(true).build();

  const richValues = values.map(row => row.map(value => {
    const text = String(value || '');
    const builder = SpreadsheetApp.newRichTextValue().setText(text);
    if (!text) return builder.build();

    boldAbsentNames(builder, text, boldStyle);
    boldCoverNames(builder, text, boldStyle);

    return builder.build();
  }));

  range.setRichTextValues(richValues);
}

function boldAbsentNames(builder, text, boldStyle) {
  const absentMarker = ' absent';
  let searchFrom = 0;
  while (true) {
    const markerIndex = text.indexOf(absentMarker, searchFrom);
    if (markerIndex === -1) break;

    const lineStart = text.lastIndexOf('\n', markerIndex) + 1;
    if (markerIndex > lineStart) builder.setTextStyle(lineStart, markerIndex, boldStyle);
    searchFrom = markerIndex + absentMarker.length;
  }
}

function boldCoverNames(builder, text, boldStyle) {
  const coverMarker = 'Cover: ';
  let searchFrom = 0;
  while (true) {
    const start = text.indexOf(coverMarker, searchFrom);
    if (start === -1) break;

    const nameStart = start + coverMarker.length;
    const lineEndIndex = text.indexOf('\n', nameStart);
    const bracketIndex = text.indexOf(' (', nameStart);
    const possibleEnds = [lineEndIndex, bracketIndex].filter(index => index !== -1);
    const nameEnd = possibleEnds.length ? Math.min(...possibleEnds) : text.length;

    if (nameEnd > nameStart && text.slice(nameStart, nameEnd) !== 'not assigned') {
      builder.setTextStyle(nameStart, nameEnd, boldStyle);
    }
    searchFrom = nameEnd;
  }
}

function jsonOutput(obj) {
  return ContentService
    .createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}