IES Photometric File Quality Control Checklist

// IES QC Checklist - Google Sheets Backend v2
// IMPORTANT: After pasting, Deploy > New Deployment
// Set "Who has access" to "Anyone" (NOT "Anyone with Google Account")

const SHEET_NAME = 'Checklists';

function doGet(e) {
  return handleRequest(e, 'GET');
}

function doPost(e) {
  return handleRequest(e, 'POST');
}

function handleRequest(e, method) {
  try {
    const action = e.parameter.action || 'read';
    let result;

    if (action === 'write' && method === 'POST') {
      let data;
      if (e.parameter.data) {
        data = JSON.parse(e.parameter.data);
      } else if (e.postData && e.postData.contents) {
        data = JSON.parse(e.postData.contents);
      }
      result = writeChecklist(data);
    } else if (action === 'read') {
      result = readAllChecklists();
    } else if (action === 'delete') {
      result = deleteChecklist(e.parameter.id);
    } else if (action === 'test') {
      result = { success: true, message: 'Connected!' };
    } else {
      result = { success: false, error: 'Unknown action' };
    }

    return ContentService
      .createTextOutput(JSON.stringify(result))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService
      .createTextOutput(JSON.stringify({ success: false, error: error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

function getOrCreateSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    sheet = ss.insertSheet(SHEET_NAME);
    const headers = ['ID', 'Timestamp', 'Product Name', 'File Name', 'Source',
      'Optic Type', 'Date Reviewed', 'Reviewed By', 'Disposition', 'Notes', 'Check Items JSON'];
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
    sheet.setFrozenRows(1);
  }
  return sheet;
}

function writeChecklist(data) {
  const sheet = getOrCreateSheet();
  sheet.appendRow([
    String(data.id), data.timestamp, data.productName, data.fileName,
    data.source, data.opticType, data.dateReviewed, data.reviewedBy,
    data.disposition, data.notes, JSON.stringify(data.checkItems || {})
  ]);
  return { success: true };
}

function readAllChecklists() {
  const sheet = getOrCreateSheet();
  const data = sheet.getDataRange().getValues();
  if (data.length <= 1) return { success: true, checklists: [] };

  const checklists = [];
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    let checkItems = {};
    try { checkItems = row[10] ? JSON.parse(row[10]) : {}; } catch(e) {}
    let dateVal = row[6];
    if (dateVal instanceof Date) {
      dateVal = Utilities.formatDate(dateVal, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    }
    checklists.push({
      id: String(row[0]), timestamp: row[1], productName: row[2],
      fileName: row[3], source: row[4], opticType: row[5],
      dateReviewed: dateVal, reviewedBy: row[7], disposition: row[8],
      notes: row[9], checkItems: checkItems
    });
  }
  return { success: true, checklists: checklists };
}

function deleteChecklist(id) {
  const sheet = getOrCreateSheet();
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (String(data[i][0]) === String(id)) {
      sheet.deleteRow(i + 1);
      return { success: true };
    }
  }
  return { success: false, message: 'Not found' };
}