From a meeting recording to tasks in everyone's inbox

/**
 * Meeting -> tasks -> email drafts (free kit, gindiinnovation.com)
 *
 * Bound to a Google Sheet with two tabs (created by setup):
 *   People: name | role | email | aliases (comma separated)
 *   Tasks:  id | meeting date | meeting | task | owner | role | email | due | status | quote | created
 *
 * Script properties (Project Settings > Script properties), all optional:
 *   LANG             he or en, default he (menu, tab names and email texts)
 *   REMIND_OWNERS    true = the daily reminder emails each owner; otherwise it emails only you
 *
 * No AI key here: you run the prompt in the AI tool your organization allows,
 * and paste its JSON. The script only checks it, files the tasks and writes drafts.
 */

var TEXT = {
  he: {
    people: 'אנשים',
    tasks: 'משימות',
    peopleHeaders: ['שם', 'תפקיד', 'אימייל', 'כינויים'],
    taskHeaders: ['מזהה', 'תאריך הפגישה', 'פגישה', 'משימה', 'אחראי', 'תפקיד', 'אימייל', 'יעד', 'סטטוס', 'ציטוט מהפגישה', 'נוצר'],
    menu: 'פגישות',
    menuImport: 'ייבוא משימות מפגישה',
    menuRemind: 'תזכורת עכשיו: משימות שהגיע זמנן',
    menuTrigger: 'הפעלת תזכורת יומית ב-8:00',
    paste: 'הדביקו כאן את ה-JSON שהחזיר כלי ה-AI (שורה אחת)',
    badJson: 'ה-JSON לא תקין: ',
    open: 'פתוח',
    done: 'בוצע',
    unassigned: 'לא שויך',
    noDue: 'לא נקבע',
    subjectOwner: 'המשימות שלך מהפגישה: ',
    subjectAll: 'סיכום פגישה: ',
    subjectRemind: 'תזכורת: משימות פתוחות שהגיע זמנן',
    hello: 'שלום ',
    yourTasks: 'סוכמו בפגישה המשימות האלה באחריותך:',
    due: 'יעד: ',
    summary: 'סיכום',
    decisions: 'החלטות',
    allTasks: 'משימות',
    questions: 'שאלות פתוחות',
    reply: 'אם משהו לא מדויק, ענו למייל הזה ונתקן.',
    remindIntro: 'המשימות האלה פתוחות והיעד שלהן הגיע או עבר:',
    result: function (t, d, u) { return 'נוספו ' + t + ' משימות ונוצרו ' + d + ' טיוטות ב-Gmail. ' + (u ? u + ' משימות לא שויכו לאף אחד: שייכו אותן בגיליון.' : 'כל המשימות שויכו.'); },
    triggerOk: 'מעכשיו תצא תזכורת כל בוקר בסביבות 8:00.',
    setupOk: 'הלשוניות מוכנות. מלאו את לשונית האנשים והתחילו.',
    nothing: 'אין משימות פתוחות שהגיע זמנן.',
  },
  en: {
    people: 'People',
    tasks: 'Tasks',
    peopleHeaders: ['Name', 'Role', 'Email', 'Aliases'],
    taskHeaders: ['ID', 'Meeting date', 'Meeting', 'Task', 'Owner', 'Role', 'Email', 'Due', 'Status', 'Quote from the meeting', 'Created'],
    menu: 'Meetings',
    menuImport: 'Import tasks from a meeting',
    menuRemind: 'Remind now: tasks that are due',
    menuTrigger: 'Turn on a daily reminder at 8:00',
    paste: 'Paste the JSON your AI tool returned (one line)',
    badJson: 'The JSON is not valid: ',
    open: 'Open',
    done: 'Done',
    unassigned: 'Unassigned',
    noDue: 'Not set',
    subjectOwner: 'Your tasks from the meeting: ',
    subjectAll: 'Meeting summary: ',
    subjectRemind: 'Reminder: open tasks that are due',
    hello: 'Hi ',
    yourTasks: 'These tasks were agreed in the meeting and are yours:',
    due: 'Due: ',
    summary: 'Summary',
    decisions: 'Decisions',
    allTasks: 'Tasks',
    questions: 'Open questions',
    reply: 'If anything is off, reply to this email and we will fix it.',
    remindIntro: 'These tasks are open and due today or earlier:',
    result: function (t, d, u) { return t + ' tasks added and ' + d + ' Gmail drafts created. ' + (u ? u + ' tasks have no owner: assign them in the sheet.' : 'Every task has an owner.'); },
    triggerOk: 'A reminder will now go out every morning around 8:00.',
    setupOk: 'The tabs are ready. Fill in the People tab and start.',
    nothing: 'No open tasks are due.',
  },
};

function lang_() {
  var v = String(PropertiesService.getScriptProperties().getProperty('LANG') || 'he').toLowerCase();
  return v === 'en' ? 'en' : 'he';
}
function t_() { return TEXT[lang_()]; }

function onOpen() {
  var t = t_();
  SpreadsheetApp.getUi().createMenu(t.menu)
    .addItem(t.menuImport, 'importMeeting')
    .addItem(t.menuRemind, 'remindOverdue')
    .addItem(t.menuTrigger, 'setupDailyReminder')
    .addToUi();
}

/** Creates the two tabs with their headers, if they are missing. Run once. */
function setup() {
  var t = t_();
  var ss = SpreadsheetApp.getActive();
  [[t.people, t.peopleHeaders], [t.tasks, t.taskHeaders]].forEach(function (s) {
    var sh = ss.getSheetByName(s[0]) || ss.insertSheet(s[0]);
    if (sh.getLastRow() === 0) {
      sh.getRange(1, 1, 1, s[1].length).setValues([s[1]]).setFontWeight('bold');
      sh.setFrozenRows(1);
    }
  });
  onOpen();
  SpreadsheetApp.getUi().alert(t.setupOk);
}

// ---------- Pure helpers (no Google services, testable anywhere) ----------

/** Lowercase, no quotes or geresh, single spaces */
function norm(s) {
  return String(s || '').toLowerCase().replace(/["'`׳״]/g, '').replace(/\s+/g, ' ').trim();
}

/** Accepts the model's answer even with a ```json fence or text around it */
function parseMeetingJson(text) {
  var s = String(text || '').trim();
  var start = s.indexOf('{');
  var end = s.lastIndexOf('}');
  if (start < 0 || end <= start) throw new Error('no { ... }');
  var data = JSON.parse(s.slice(start, end + 1));
  if (!data || !Array.isArray(data.tasks)) throw new Error('missing "tasks"');
  data.meeting = data.meeting || {};
  data.summary = Array.isArray(data.summary) ? data.summary : [];
  data.decisions = Array.isArray(data.decisions) ? data.decisions : [];
  data.open_questions = Array.isArray(data.open_questions) ? data.open_questions : [];
  data.tasks = data.tasks
    .filter(function (x) { return x && String(x.task || '').trim(); })
    .map(function (x) {
      return { task: String(x.task).trim(), owner: String(x.owner || '').trim(), due: normalizeDue(x.due), quote: String(x.quote || '').trim() };
    });
  return data;
}

/** "2026-10-03" stays; anything that is not a real yyyy-mm-dd date becomes "" (no invented dates) */
function normalizeDue(v) {
  var m = String(v || '').trim().match(/^(\d{4})-(\d{2})-(\d{2})$/);
  if (!m) return '';
  var d = new Date(Date.UTC(+m[1], +m[2] - 1, +m[3]));
  return d.getUTCMonth() === +m[2] - 1 && d.getUTCDate() === +m[3] ? m[0] : '';
}

/**
 * Finds the person a task belongs to: full name, then an alias, then the role,
 * then a first name that only one person has. No match = null (the task stays unassigned).
 */
function matchPerson(owner, people) {
  var o = norm(owner);
  if (!o) return null;
  var by = function (fn) { var hits = people.filter(fn); return hits.length === 1 ? hits[0] : null; };
  return (
    by(function (p) { return norm(p.name) === o; }) ||
    by(function (p) { return p.aliases.some(function (a) { return norm(a) === o; }); }) ||
    by(function (p) { return norm(p.role) === o; }) ||
    by(function (p) { return norm(p.name).split(' ')[0] === o; })
  );
}

/** Groups the matched tasks by email: { email: { person, tasks[] } } */
function groupByOwner(tasks, people) {
  var out = {};
  tasks.forEach(function (task) {
    var p = matchPerson(task.owner, people);
    if (!p || !p.email) return;
    (out[p.email] = out[p.email] || { person: p, tasks: [] }).tasks.push(task);
  });
  return out;
}

function ownerEmailBody(t, person, tasks, meetingTitle) {
  var lines = [t.hello + person.name + ',', '', t.yourTasks, ''];
  tasks.forEach(function (x, i) { lines.push((i + 1) + '. ' + x.task + ' (' + t.due + (x.due || t.noDue) + ')'); });
  lines.push('', meetingTitle, '', t.reply);
  return lines.join('\n');
}

function summaryEmailBody(t, data, people) {
  var section = function (title, items) { return items.length ? ['', title + ':'].concat(items.map(function (s) { return '- ' + s; })) : []; };
  var taskLines = data.tasks.map(function (x) {
    var p = matchPerson(x.owner, people);
    return x.task + ' | ' + (p ? p.name : t.unassigned) + ' | ' + t.due + (x.due || t.noDue);
  });
  return [data.meeting.title || '']
    .concat(section(t.summary, data.summary), section(t.decisions, data.decisions), section(t.allTasks, taskLines), section(t.questions, data.open_questions), ['', t.reply])
    .join('\n');
}

// ---------- Google Sheets and Gmail ----------

function readPeople_() {
  var sh = SpreadsheetApp.getActive().getSheetByName(t_().people);
  if (!sh || sh.getLastRow() < 2) return [];
  return sh.getRange(2, 1, sh.getLastRow() - 1, 4).getValues()
    .filter(function (r) { return String(r[0]).trim(); })
    .map(function (r) {
      return { name: String(r[0]).trim(), role: String(r[1]).trim(), email: String(r[2]).trim(), aliases: String(r[3]).split(',').map(function (a) { return a.trim(); }).filter(String) };
    });
}

/** Menu: paste the JSON, file the tasks, write Gmail drafts (nothing is sent) */
function importMeeting() {
  var t = t_();
  var ui = SpreadsheetApp.getUi();
  var res = ui.prompt(t.menuImport, t.paste, ui.ButtonSet.OK_CANCEL);
  if (res.getSelectedButton() !== ui.Button.OK) return;
  var data;
  try { data = parseMeetingJson(res.getResponseText()); } catch (e) { ui.alert(t.badJson + e.message); return; }

  var ss = SpreadsheetApp.getActive();
  var tz = ss.getSpreadsheetTimeZone();
  var sheet = ss.getSheetByName(t.tasks);
  var people = readPeople_();
  var now = new Date();
  var date = normalizeDue(data.meeting.date) || Utilities.formatDate(now, tz, 'yyyy-MM-dd');
  var title = String(data.meeting.title || '').trim() || date;
  var start = Math.max(sheet.getLastRow(), 1);

  var unassigned = 0;
  var rows = data.tasks.map(function (x, i) {
    var p = matchPerson(x.owner, people);
    if (!p) unassigned += 1;
    var id = date.replace(/-/g, '').slice(2) + '-' + (start + i);
    var owner = p ? p.name : x.owner ? t.unassigned + ': ' + x.owner : t.unassigned;
    return [id, date, title, x.task, owner, p ? p.role : '', p ? p.email : '', x.due, t.open, x.quote, now];
  });
  if (rows.length) sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);

  var drafts = 0;
  var groups = groupByOwner(data.tasks, people);
  Object.keys(groups).forEach(function (email) {
    GmailApp.createDraft(email, t.subjectOwner + title, ownerEmailBody(t, groups[email].person, groups[email].tasks, title));
    drafts += 1;
  });
  var participants = (data.meeting.participants || [])
    .map(function (n) { var p = matchPerson(n, people); return p && p.email; })
    .filter(function (e, i, all) { return e && all.indexOf(e) === i; });
  if (participants.length) {
    GmailApp.createDraft(participants.join(','), t.subjectAll + title, summaryEmailBody(t, data, people));
    drafts += 1;
  }
  ui.alert(t.result(rows.length, drafts, unassigned));
}

/** Open tasks due today or earlier: one email to you, or one per owner if REMIND_OWNERS is true */
function remindOverdue() {
  var t = t_();
  var ss = SpreadsheetApp.getActive();
  var tz = ss.getSpreadsheetTimeZone();
  var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  var sh = ss.getSheetByName(t.tasks);
  var rows = sh.getLastRow() < 2 ? [] : sh.getRange(2, 1, sh.getLastRow() - 1, 11).getValues();
  var due = rows.filter(function (r) {
    var d = r[7] instanceof Date ? Utilities.formatDate(r[7], tz, 'yyyy-MM-dd') : normalizeDue(r[7]);
    return d && d <= today && String(r[8]).trim() !== t.done;
  });
  var interactive = false;
  try { SpreadsheetApp.getUi(); interactive = true; } catch (e) { interactive = false; }
  if (!due.length) { if (interactive) SpreadsheetApp.getUi().alert(t.nothing); return; }

  var line = function (r) { return '- ' + r[3] + ' | ' + r[4] + ' | ' + t.due + (r[7] instanceof Date ? Utilities.formatDate(r[7], tz, 'yyyy-MM-dd') : r[7]) + ' | ' + r[2]; };
  var toOwners = String(PropertiesService.getScriptProperties().getProperty('REMIND_OWNERS')).toLowerCase() === 'true';
  if (toOwners) {
    var byEmail = {};
    due.forEach(function (r) { if (r[6]) (byEmail[r[6]] = byEmail[r[6]] || []).push(r); });
    Object.keys(byEmail).forEach(function (email) {
      MailApp.sendEmail(email, t.subjectRemind, [t.remindIntro, ''].concat(byEmail[email].map(line)).join('\n'));
    });
  }
  // You always get the full list, including tasks with no owner
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(), t.subjectRemind, [t.remindIntro, ''].concat(due.map(line)).join('\n'));
}

/** Menu: one daily trigger for remindOverdue, around 8:00 in the sheet's time zone */
function setupDailyReminder() {
  ScriptApp.getProjectTriggers()
    .filter(function (tr) { return tr.getHandlerFunction() === 'remindOverdue'; })
    .forEach(function (tr) { ScriptApp.deleteTrigger(tr); });
  ScriptApp.newTrigger('remindOverdue').timeBased().everyDays(1).atHour(8).create();
  SpreadsheetApp.getUi().alert(t_().triggerOk);
}

// For local tests only (Node). Apps Script has no `module`, so this line does nothing there.
if (typeof module !== 'undefined') {
  module.exports = { TEXT: TEXT, norm: norm, parseMeetingJson: parseMeetingJson, normalizeDue: normalizeDue, matchPerson: matchPerson, groupByOwner: groupByOwner, ownerEmailBody: ownerEmailBody, summaryEmailBody: summaryEmailBody };
}