Mail Merge con Allegati — Google Apps Script

const CONFIG_SHEET_NAME = "⚙️ Configurazione";

const KEYS = {
  FOLDER_ID:     "folder_id",
  FOLDER_NAME:   "folder_name",
  NOME_MITTENTE: "nome_mittente",
  CC:            "cc",
  OGGETTO:       "oggetto",
  CORPO:         "corpo",
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("🔧 Mail Merge")
    .addItem("▶  Invia email", "inviaEmail")
    .addItem("👁  Anteprima prima riga non inviata", "anteprimaEmail")
    .addSeparator()
    .addItem("📁  Seleziona cartella Drive…", "apriSelettoreCartella")
    .addItem("✏️  Modifica testo email…", "apriEditorEmail")
    .addItem("⚙️  Apri foglio configurazione", "apriConfigSheet")
    .addSeparator()
    .addItem("↺  Resetta stato invio", "resettaStato")
    .addToUi();
}

function getOrCreateConfigSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(CONFIG_SHEET_NAME);
  if (sheet) return sheet;

  sheet = ss.insertSheet(CONFIG_SHEET_NAME);

  sheet.getRange("A1:B1").setValues([["Impostazione", "Valore"]]);
  sheet.getRange("A1:B1").setFontWeight("bold").setBackground("#4a86e8").setFontColor("white");
  sheet.setColumnWidth(1, 220);
  sheet.setColumnWidth(2, 480);

  const defaults = [
    [KEYS.FOLDER_ID,     ""],
    [KEYS.FOLDER_NAME,   "(nessuna cartella selezionata — usa il menu Seleziona cartella Drive)"],
    [KEYS.NOME_MITTENTE, ""],
    [KEYS.CC,            ""],
    [KEYS.OGGETTO,       "Il tuo certificato — {{Nome}} {{Cognome}}"],
    [KEYS.CORPO,         "Gentile {{Nome}} {{Cognome}},\n\nin allegato trovi il tuo certificato.\n\nCordiali saluti,\nProf."],
  ];
  sheet.getRange(2, 1, defaults.length, 2).setValues(defaults);
  sheet.getRange("A2:A7").setFontColor("#555555").setFontStyle("italic");
  sheet.getRange("B7").setWrap(true);
  sheet.setRowHeight(7, 130);

  const noteRow = defaults.length + 3;
  sheet.getRange(noteRow, 1).setValue(
    "💡 Usa il menu 🔧 Mail Merge per modificare le impostazioni tramite finestre di dialogo."
  );
  sheet.getRange(noteRow, 1, 1, 2).merge()
    .setFontStyle("italic").setFontColor("#888888");

  return sheet;
}

function readConfig() {
  const sheet = getOrCreateConfigSheet();
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return {};
  const data = sheet.getRange(2, 1, lastRow - 1, 2).getValues();
  const config = {};
  data.forEach(([key, val]) => { if (key) config[String(key).trim()] = val; });
  return config;
}

function writeConfig(key, value) {
  const sheet = getOrCreateConfigSheet();
  const lastRow = sheet.getLastRow();
  const keys = sheet.getRange(2, 1, lastRow - 1, 1).getValues().flat();
  const rowIdx = keys.findIndex(k => k === key);
  if (rowIdx !== -1) {
    sheet.getRange(rowIdx + 2, 2).setValue(value);
  }
}

function apriConfigSheet() {
  const sheet = getOrCreateConfigSheet();
  SpreadsheetApp.getActiveSpreadsheet().setActiveSheet(sheet);
}

function apriSelettoreCartella() {
  const html = HtmlService.createHtmlOutputFromFile("FolderPicker")
    .setWidth(500).setHeight(300);
  SpreadsheetApp.getUi().showModalDialog(html, "📁 Seleziona cartella Drive");
}

function validaCartella(folderId) {
  try {
    const folder = DriveApp.getFolderById(folderId);
    return { ok: true, name: folder.getName() };
  } catch (e) {
    return { ok: false, error: e.message };
  }
}

function salvaCartella(folderId, folderName) {
  writeConfig(KEYS.FOLDER_ID, folderId);
  writeConfig(KEYS.FOLDER_NAME, folderName || folderId);
}

function apriEditorEmail() {
  const config = readConfig();
  const tpl = HtmlService.createTemplateFromFile("EmailEditor");
  tpl.nome_mittente = config[KEYS.NOME_MITTENTE] || "";
  tpl.cc            = config[KEYS.CC]            || "";
  tpl.oggetto       = config[KEYS.OGGETTO]       || "";
  tpl.corpo         = config[KEYS.CORPO]         || "";
  const html = tpl.evaluate().setWidth(620).setHeight(540);
  SpreadsheetApp.getUi().showModalDialog(html, "✏️ Modifica testo email");
}

function salvaEmailConfig(data) {
  writeConfig(KEYS.NOME_MITTENTE, data.nome_mittente);
  writeConfig(KEYS.CC,            data.cc);
  writeConfig(KEYS.OGGETTO,       data.oggetto);
  writeConfig(KEYS.CORPO,         data.corpo);
}

function getFoglioEIntestazioni() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let foglio = ss.getActiveSheet();
  if (foglio.getName() === CONFIG_SHEET_NAME) {
    foglio = ss.getSheets().find(s => s.getName() !== CONFIG_SHEET_NAME) || foglio;
  }
  const intestazioni = foglio
    .getRange(1, 1, 1, foglio.getLastColumn())
    .getValues()[0]
    .map(h => String(h).trim());
  return { foglio, intestazioni };
}

// Sostituisce {{NomeColonna}} con il valore corrispondente della riga
function sostituisci(testo, intestazioni, riga) {
  let out = String(testo || "");
  intestazioni.forEach((col, i) => {
    if (col) {
      const re = new RegExp(`\\{\\{${col}\\}\\}`, "g");
      out = out.replace(re, riga[i] ?? "");
    }
  });
  return out;
}

function trovaFile(nomeFile, folderId) {
  try {
    const cartella = DriveApp.getFolderById(folderId);
    const iter = cartella.getFilesByName(nomeFile);
    return iter.hasNext() ? iter.next() : null;
  } catch (e) {
    throw new Error(
      `Cartella Drive non accessibile. Usa "Seleziona cartella Drive" dal menu. (${e.message})`
    );
  }
}

function getColonnaStato(foglio, intestazioni) {
  let idx = intestazioni.indexOf("Stato");
  if (idx === -1) {
    idx = intestazioni.length;
    foglio.getRange(1, idx + 1).setValue("Stato");
    intestazioni.push("Stato");
  }
  return idx;
}

function inviaEmail() {
  const ui = SpreadsheetApp.getUi();
  const config = readConfig();

  if (!config[KEYS.FOLDER_ID]) {
    ui.alert("⚠️ Cartella non configurata",
      "Usa il menu:\n🔧 Mail Merge → Seleziona cartella Drive\nprima di inviare le email.",
      ui.ButtonSet.OK);
    return;
  }

  const risposta = ui.alert("Conferma invio",
    "Vuoi inviare le email a tutti i destinatari\nnon ancora contrassegnati come ✅ Inviato?",
    ui.ButtonSet.YES_NO);
  if (risposta !== ui.Button.YES) return;

  const { foglio, intestazioni } = getFoglioEIntestazioni();
  const colStato = getColonnaStato(foglio, intestazioni);
  const colEmail = intestazioni.indexOf("Email");
  const colFile  = intestazioni.indexOf("NomeFile");

  if (colEmail === -1 || colFile === -1) {
    ui.alert("❌ Colonne mancanti",
      "Il foglio dati deve avere le colonne 'Email' e 'NomeFile' nella riga 1.",
      ui.ButtonSet.OK);
    return;
  }

  const ultimaRiga = foglio.getLastRow();
  if (ultimaRiga < 2) { ui.alert("Il foglio non contiene righe di dati."); return; }

  const dati = foglio
    .getRange(2, 1, ultimaRiga - 1, intestazioni.length)
    .getValues();

  let inviati = 0, saltati = 0, errori = 0;

  dati.forEach((riga, i) => {
    const rigaNum = i + 2;
    if (String(riga[colStato] || "").trim() === "✅ Inviato") { saltati++; return; }

    const email    = String(riga[colEmail] || "").trim();
    const nomeFile = String(riga[colFile]  || "").trim();

    if (!email || !nomeFile) {
      foglio.getRange(rigaNum, colStato + 1).setValue("⚠️ Dati mancanti");
      errori++; return;
    }

    try {
      const file = trovaFile(nomeFile, config[KEYS.FOLDER_ID]);
      if (!file) {
        foglio.getRange(rigaNum, colStato + 1).setValue("❌ File non trovato: " + nomeFile);
        errori++; return;
      }

      const oggetto = sostituisci(config[KEYS.OGGETTO], intestazioni, riga);
      const corpo   = sostituisci(config[KEYS.CORPO],   intestazioni, riga);
      const opzioni = {
        attachments: [file.getBlob()],
        name: config[KEYS.NOME_MITTENTE] || undefined,
      };
      if (config[KEYS.CC]) opzioni.cc = config[KEYS.CC];

      GmailApp.sendEmail(email, oggetto, corpo, opzioni);
      foglio.getRange(rigaNum, colStato + 1).setValue("✅ Inviato");
      Utilities.sleep(300);
      inviati++;
    } catch (e) {
      foglio.getRange(rigaNum, colStato + 1).setValue("❌ Errore: " + e.message);
      errori++;
    }
  });

  ui.alert("Invio completato",
    `✅ Inviati:  ${inviati}\n⏭ Saltati:  ${saltati} (già inviati)\n❌ Errori:   ${errori}`,
    ui.ButtonSet.OK);
}

function anteprimaEmail() {
  const ui = SpreadsheetApp.getUi();
  const config = readConfig();
  const { foglio, intestazioni } = getFoglioEIntestazioni();
  const colStato = getColonnaStato(foglio, intestazioni);
  const ultimaRiga = foglio.getLastRow();
  if (ultimaRiga < 2) { ui.alert("Il foglio non contiene righe di dati."); return; }

  const dati = foglio
    .getRange(2, 1, ultimaRiga - 1, intestazioni.length)
    .getValues();
  const riga = dati.find(r => String(r[colStato] || "").trim() !== "✅ Inviato");
  if (!riga) { ui.alert("Tutte le righe risultano già inviate."); return; }

  const colEmail = intestazioni.indexOf("Email");
  const oggetto  = sostituisci(config[KEYS.OGGETTO] || "", intestazioni, riga);
  const corpo    = sostituisci(config[KEYS.CORPO]   || "", intestazioni, riga);
  const email    = colEmail !== -1 ? riga[colEmail] : "(colonna Email non trovata)";

  ui.alert("👁 Anteprima email",
    `A: ${email}\nCC: ${config[KEYS.CC] || "(nessuno)"}\n\nOGGETTO: ${oggetto}\n\n${"─".repeat(40)}\n${corpo}`,
    ui.ButtonSet.OK);
}

function resettaStato() {
  const ui = SpreadsheetApp.getUi();
  const risposta = ui.alert("Conferma reset",
    "Cancellare tutti gli stati di invio?\nLe email già inviate non verranno reinviate automaticamente.",
    ui.ButtonSet.YES_NO);
  if (risposta !== ui.Button.YES) return;

  const { foglio, intestazioni } = getFoglioEIntestazioni();
  const colStato = intestazioni.indexOf("Stato");
  if (colStato === -1) { ui.alert("Nessuna colonna 'Stato' trovata."); return; }
  const numRighe = foglio.getLastRow() - 1;
  if (numRighe > 0) {
    foglio.getRange(2, colStato + 1, numRighe, 1).clearContent();
    ui.alert("✅ Stato resettato per " + numRighe + " righe.");
  }
}

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    * { box-sizing: border-box; }
    body { font-family: "Google Sans", Arial, sans-serif; padding: 24px; font-size: 14px; background: #fff; }
    h3 { margin: 0 0 16px; font-size: 16px; color: #202124; }
    label { display: block; margin-bottom: 6px; font-weight: 600; color: #3c4043; }
    input {
      width: 100%; padding: 10px 12px; border: 1px solid #dadce0;
      border-radius: 6px; font-size: 14px; color: #202124; transition: border-color .2s;
    }
    input:focus { outline: none; border-color: #1a73e8; box-shadow: 0 0 0 2px #e8f0fe; }
    .hint { font-size: 12px; color: #80868b; margin: 6px 0 20px; }
    .hint code { background: #f1f3f4; padding: 1px 5px; border-radius: 3px; }
    .actions { display: flex; gap: 10px; margin-top: 8px; }
    .btn {
      padding: 9px 20px; border: none; border-radius: 6px;
      cursor: pointer; font-size: 14px; font-weight: 500; transition: background .15s;
    }
    .btn-primary { background: #1a73e8; color: #fff; }
    .btn-primary:hover { background: #1557b0; }
    .btn-secondary { background: #f1f3f4; color: #3c4043; }
    .btn-secondary:hover { background: #e0e0e0; }
    .status {
      margin-top: 16px; padding: 12px 14px; border-radius: 6px;
      font-size: 13px; display: none;
    }
    .ok   { background: #e6f4ea; color: #137333; border: 1px solid #b7dfbc; }
    .err  { background: #fce8e6; color: #c5221f; border: 1px solid #f5c6c2; }
    .info { background: #e8f0fe; color: #1a73e8; border: 1px solid #c5d8fb; }
  </style>
</head>
<body>
  <h3>📁 Seleziona cartella Google Drive</h3>

  <label for="urlInput">URL o ID della cartella</label>
  <input type="text" id="urlInput"
    placeholder="https://drive.google.com/drive/folders/1AbCdEf…" />
  <p class="hint">
    Apri la cartella su Drive, copia l'URL dalla barra del browser e incollalo qui.<br>
    Puoi incollare anche solo l'ID: la parte dopo <code>/folders/</code>
  </p>

  <div class="actions">
    <button id="btnValida" class="btn btn-primary">🔍 Verifica e salva</button>
    <button id="btnAnnulla" class="btn btn-secondary">Annulla</button>
  </div>

  <div id="status" class="status"></div>

  <script>
    function estraiId(input) {
      input = (input || "").trim();
      const m = input.match(/\/folders\/([a-zA-Z0-9_-]+)/);
      if (m) return m[1];
      if (/^[a-zA-Z0-9_-]{10,}$/.test(input)) return input;
      return null;
    }

    function setStatus(msg, tipo) {
      const el = document.getElementById("status");
      el.className = "status " + tipo;
      el.textContent = msg;
      el.style.display = "block";
    }

    function valida() {
      const raw = document.getElementById("urlInput").value;
      const id = estraiId(raw);
      if (!id) {
        setStatus("❌ Formato non riconosciuto. Incolla l'URL completo della cartella Drive.", "err");
        return;
      }
      setStatus("⏳ Verifica della cartella in corso…", "info");
      google.script.run
        .withSuccessHandler(function(res) {
          if (res.ok) {
            setStatus("✅ Cartella trovata: "" + res.name + "" — salvataggio…", "ok");
            google.script.run
              .withSuccessHandler(function() {
                setStatus("✅ Cartella salvata: "" + res.name + """, "ok");
                setTimeout(() => google.script.host.close(), 1400);
              })
              .salvaCartella(id, res.name);
          } else {
            setStatus("❌ Cartella non accessibile. Verifica i permessi e che l'URL sia corretto.", "err");
          }
        })
        .withFailureHandler(function(e) {
          setStatus("❌ Errore: " + e.message, "err");
        })
        .validaCartella(id);
    }

    document.addEventListener("DOMContentLoaded", function() {
      document.getElementById("btnValida").addEventListener("click", valida);
      document.getElementById("btnAnnulla").addEventListener("click", function() {
        google.script.host.close();
      });
      document.getElementById("urlInput").addEventListener("keydown", function(e) {
        if (e.key === "Enter") valida();
      });
    });
  </script>
</body>
</html>

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    * { box-sizing: border-box; }
    body { font-family: "Google Sans", Arial, sans-serif; padding: 24px; font-size: 14px; background: #fff; }
    h3 { margin: 0 0 16px; font-size: 16px; color: #202124; }
    label { display: block; margin: 14px 0 5px; font-weight: 600; color: #3c4043; }
    input, textarea {
      width: 100%; padding: 9px 12px; border: 1px solid #dadce0;
      border-radius: 6px; font-size: 14px; color: #202124; transition: border-color .2s;
    }
    input:focus, textarea:focus {
      outline: none; border-color: #1a73e8; box-shadow: 0 0 0 2px #e8f0fe;
    }
    textarea { height: 150px; resize: vertical; font-family: monospace; font-size: 13px; line-height: 1.5; }
    .hint { font-size: 12px; color: #80868b; margin: 4px 0 0; }
    .row { display: grid; grid-template-columns: 1fr 1fr; gap: 12px; }
    .footer {
      margin-top: 20px; padding-top: 14px; border-top: 1px solid #e8eaed;
      display: flex; align-items: center; gap: 10px;
    }
    .btn { padding: 9px 22px; border: none; border-radius: 6px; cursor: pointer; font-size: 14px; font-weight: 500; }
    .btn-primary { background: #1a73e8; color: #fff; }
    .btn-primary:hover { background: #1557b0; }
    .btn-secondary { background: #f1f3f4; color: #3c4043; }
    .btn-secondary:hover { background: #e0e0e0; }
    .saved { font-size: 13px; color: #137333; display: none; }
  </style>
</head>
<body>
  <h3>✏️ Modifica testo email</h3>

  <div class="row">
    <div>
      <label for="nome_mittente">Nome mittente</label>
      <input type="text" id="nome_mittente" value="<?= nome_mittente ?>"
        placeholder="Es: ITIS Volta — Segreteria" />
    </div>
    <div>
      <label for="cc">CC statico</label>
      <input type="text" id="cc" value="<?= cc ?>"
        placeholder="Es: segreteria@scuola.it" />
      <p class="hint">Più indirizzi separati da virgola</p>
    </div>
  </div>

  <label for="oggetto">Oggetto</label>
  <input type="text" id="oggetto" value="<?= oggetto ?>" />
  <p class="hint">Usa <code>{{Nome}}</code>, <code>{{Cognome}}</code> e qualsiasi colonna del foglio</p>

  <label for="corpo">Corpo email</label>
  <textarea id="corpo"><?= corpo ?></textarea>
  <p class="hint">Usa <code>{{Nome}}</code>, <code>{{Cognome}}</code> e qualsiasi altra colonna. Invio = a capo.</p>

  <div class="footer">
    <button id="btnSalva" class="btn btn-primary">💾 Salva</button>
    <button id="btnAnnullaEmail" class="btn btn-secondary">Annulla</button>
    <span id="saved" class="saved">✅ Salvato!</span>
  </div>

  <script>
    document.addEventListener("DOMContentLoaded", function() {
      document.getElementById("btnSalva").addEventListener("click", salva);
      document.getElementById("btnAnnullaEmail").addEventListener("click", function() {
        google.script.host.close();
      });
    });

    function salva() {
      const data = {
        nome_mittente: document.getElementById("nome_mittente").value,
        cc:            document.getElementById("cc").value,
        oggetto:       document.getElementById("oggetto").value,
        corpo:         document.getElementById("corpo").value,
      };
      google.script.run
        .withSuccessHandler(function() {
          document.getElementById("saved").style.display = "inline";
          setTimeout(() => google.script.host.close(), 1000);
        })
        .withFailureHandler(function(e) {
          alert("Errore nel salvataggio: " + e.message);
        })
        .salvaEmailConfig(data);
    }
  </script>
</body>
</html>