Time Analyzer

function doGet(e) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var result = { sheets: [] };
  var sheets = spreadsheet.getSheets();

  sheets.forEach(function(sheet) {
    var name = sheet.getName();

    if (name.toLowerCase().includes('summary') ||
        name.toLowerCase().includes('chart') ||
        name.toLowerCase().includes('template') ||
        name.toLowerCase().includes('instruction') ||
        name.toLowerCase().includes('example') ||
        name.toLowerCase().includes('company')) return;

    var data = sheet.getDataRange().getValues();
    if (data.length < 2) return;

    var rows = [];

    for (var i = 1; i < data.length; i++) {
      var row = data[i];

      var activity = String(row[1] || '').trim();
      if (!activity || activity === 'Task' || activity === 'Activity') continue;

      var date = '';
      if (row[0]) {
        try { date = Utilities.formatDate(new Date(row[0]), Session.getScriptTimeZone(), 'MM/dd/yyyy'); }
        catch(err) { date = String(row[0]); }
      }

      var start = '';
      if (row[3]) {
        try { start = Utilities.formatDate(new Date(row[3]), Session.getScriptTimeZone(), 'hh:mm a'); }
        catch(err) { start = String(row[3]); }
      }

      var end = '';
      if (row[4]) {
        try { end = Utilities.formatDate(new Date(row[4]), Session.getScriptTimeZone(), 'hh:mm a'); }
        catch(err) { end = String(row[4]); }
      }

      var mins = 0;
      var rawMins = row[5];
      if (typeof rawMins === 'number') {
        if (rawMins < 0 || rawMins > 1440) {
          if (row[3] && row[4]) {
            var startTime = new Date(row[3]);
            var endTime = new Date(row[4]);
            mins = Math.round((endTime - startTime) / 60000);
            if (mins < 0) mins += 1440;
          }
        } else {
          mins = Math.round(rawMins);
        }
      }

      var dtype = String(row[6] || '').trim();
      var ttype = String(row[7] || '').trim();

      rows.push({
        activity: activity,
        date: date,
        start: start,
        end: end,
        mins: mins,
        dtype: dtype,
        ttype: ttype
      });
    }

    if (rows.length > 0) {
      result.sheets.push({ person: name, entries: rows });
    }
  });

  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}