Mail Builder – Telering

Telering Email Template Builder

Kirim email lebih cepat dengan template yang tersimpan rapi.

Simpan kategori penerima, isi, gunakan template subjek dan badan email, tambahkan signature rich text, lalu catat otomatis riwayat pengiriman ke Google Sheets.

0Kategori email

0Total penerima

0Template email

0Signature

Pengiriman Email

Pilih kategori penerima, gunakan template, lalu edit subjek dan isi email sebelum dikirim.

Email pengirim aktif

Belum dicek

Email dikirim melalui akun Google yang menjalankan Apps Script backend, bukan langsung dari browser.

Active user / akun pengirim -

Sisa kuota MailApp -

Kategori penerima

Setelah kategori dipilih, Anda bisa mencentang semua atau beberapa penerima saja.

Template email

Subjek dan badan email akan terisi otomatis, lalu tetap bisa diedit manual.

Tambahan email manual opsional

Pisahkan dengan baris baru, koma, atau format Nama <email@domain.com>.

Subjek email

Badan email

Gunakan signature

Pilih signature

Attachment email opsional

Drop file di sini atau klik untuk upload attachment Anda bisa melampirkan berbagai jenis file. Semua attachment dikirim bersama email dan dicatat di riwayat.

Tambah / Edit Kategori Email

Simpan kategori seperti “Mentee LPDP”, “Awardee”, “Partner”, atau kategori lain.

Nama kategori

Daftar email

Bisa menggunakan format email saja, Nama <email>, atau Nama,email. Email duplikat otomatis dihapus.

Daftar Kategori

Data tersimpan di cache browser dan dapat disinkronkan ke Google Sheets.

Tambah / Edit Template Email

Template menyimpan subjek dan badan email rich text.

Nama template

Subjek template

Badan template

Daftar Template

Pilih template di tab pengiriman untuk mengisi subjek dan badan email otomatis.

Tambah / Edit Signature

Signature dapat berisi teks, gambar, hyperlink, dan format sederhana.

Nama signature

Isi signature

Daftar Signature

Signature dapat dipilih dan dinyalakan saat mengirim email.

Riwayat Pengiriman

Riwayat lokal ditampilkan di sini. Jika backend aktif, riwayat pengiriman juga dicatat otomatis di Google Sheets.

Waktu	Penerima	Subjek	Template	Attachment	Status	Pesan

Pengaturan Koneksi

Masukkan URL Web App Google Apps Script untuk mengirim email dan menyimpan data ke Spreadsheet.

URL Web App Apps Script

Deploy Apps Script sebagai Web App, akses “Anyone”, lalu tempel URL /exec di sini.

Nama pengirim opsional

Nama ini hanya label tampilan pengirim. Email asli tetap akun deployer Apps Script.

Reply-to opsional

Catatan penting Browser tidak dapat mengirim email langsung dari Gmail secara aman. Tombol kirim menggunakan Google Apps Script MailApp, sehingga email dikirim sebagai akun Google yang menjalankan deployment Apps Script.

Backup & Cache Browser

Data juga disimpan di cache browser agar aplikasi cepat dibuka kembali.

Status sinkronisasi Belum ada aktivitas sinkronisasi.

Kode Backend Google Apps Script

Copy kode berikut ke Apps Script, isi Spreadsheet ID, deploy sebagai Web App, lalu tempel URL-nya di atas.

/**
 * TELERING EMAIL TEMPLATE BUILDER - GOOGLE APPS SCRIPT BACKEND
 * Cara pakai:
 * 1. Buat Google Spreadsheet baru.
 * 2. Buka Extensions > Apps Script dari spreadsheet tersebut, lalu paste kode ini.
 * 3. Jika Apps Script dibuat dari spreadsheet, SPREADSHEET_ID boleh dibiarkan kosong/placeholder.
 *    Jika Apps Script dibuat standalone, isi SPREADSHEET_ID dengan ID spreadsheet.
 * 4. Deploy > New deployment > Web app.
 *    Execute as: Me
 *    Who has access: Anyone
 * 5. Copy URL /exec ke Pengaturan HTML.
 */

const SPREADSHEET_ID = 'PASTE_SPREADSHEET_ID_DI_SINI';

function doGet() {
  return jsonOutput({
    ok: true,
    app: 'Telering Email Template Builder',
    message: 'Backend aktif. Gunakan POST dari aplikasi HTML.',
    quota: safeQuota_(),
    sender: senderInfo_(),
    spreadsheetName: ss_().getName(),
    timestamp: new Date().toISOString()
  });
}

function doPost(e) {
  try {
    const payload = JSON.parse((e.parameter && e.parameter.payload) || '{}');
    const action = payload.action || '';

    if (action === 'ping' || action === 'getSenderInfo') {
      return jsonOutput({
        ok: true,
        quota: safeQuota_(),
        sender: senderInfo_(),
        spreadsheetName: ss_().getName(),
        timestamp: new Date().toISOString()
      });
    }

    if (action === 'getAll') {
      return jsonOutput({
        ok: true,
        data: getAllData_(),
        quota: safeQuota_(),
        sender: senderInfo_(),
        spreadsheetName: ss_().getName()
      });
    }

    if (action === 'saveAll') {
      saveAllData_(payload.data || {});
      return jsonOutput({
        ok: true,
        message: 'Data berhasil disimpan ke Spreadsheet.',
        quota: safeQuota_(),
        sender: senderInfo_(),
        spreadsheetName: ss_().getName(),
        timestamp: new Date().toISOString()
      });
    }

    if (action === 'sendEmailBatch') {
      const result = sendEmailBatch_(payload.mail || {}, payload.clientTime || '', payload.clientTimeText || '', payload.userAgent || '');
      return jsonOutput({ ok: true, result: result, quota: safeQuota_(), sender: senderInfo_(), spreadsheetName: ss_().getName() });
    }

    return jsonOutput({ ok: false, message: 'Action tidak dikenal: ' + action });
  } catch (err) {
    return jsonOutput({ ok: false, message: String(err && err.message ? err.message : err) });
  }
}

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

function ss_() {
  const id = String(SPREADSHEET_ID || '').trim();
  if (id && id !== 'PASTE_SPREADSHEET_ID_DI_SINI') {
    return SpreadsheetApp.openById(id);
  }

  const active = SpreadsheetApp.getActiveSpreadsheet();
  if (active) return active;

  throw new Error('SPREADSHEET_ID belum diisi. Isi ID spreadsheet, atau buat Apps Script dari menu Extensions > Apps Script di dalam Google Spreadsheet.');
}

function sheet_(name, headers) {
  const ss = ss_();
  let sh = ss.getSheetByName(name);
  if (!sh) sh = ss.insertSheet(name);
  if (sh.getLastRow() === 0) sh.setFrozenRows(1);
  sh.getRange(1, 1, 1, headers.length).setValues([headers]);
  sh.setFrozenRows(1);
  return sh;
}

function replaceSheet_(name, headers, rows) {
  const sh = sheet_(name, headers);
  sh.clearContents();
  sh.getRange(1, 1, 1, headers.length).setValues([headers]);
  if (rows.length) sh.getRange(2, 1, rows.length, headers.length).setValues(rows);
  sh.autoResizeColumns(1, Math.min(headers.length, 12));
}

function values_(name, headers) {
  const sh = sheet_(name, headers);
  const lastRow = sh.getLastRow();
  if (lastRow < 2) return [];
  return sh.getRange(2, 1, lastRow - 1, headers.length).getValues();
}

function saveAllData_(data) {
  const now = new Date().toISOString();
  const sender = senderInfo_();
  const quota = safeQuota_();

  const categories = data.categories || [];
  const categoryRows = [];
  categories.forEach(cat => {
    (cat.items || []).forEach(item => {
      categoryRows.push([
        cat.id || '',
        cat.name || '',
        item.id || '',
        item.name || '',
        item.email || '',
        now
      ]);
    });
  });
  replaceSheet_('Categories', ['categoryId','categoryName','contactId','contactName','email','updatedAt'], categoryRows);

  const templates = data.templates || [];
  const templateRows = templates.map(t => [
    t.id || '',
    t.name || '',
    t.subject || '',
    safeCell_(t.body || ''),
    now
  ]);
  replaceSheet_('Templates', ['id','name','subject','bodyHtml','updatedAt'], templateRows);

  const signatures = data.signatures || [];
  const signatureRows = signatures.map(s => [
    s.id || '',
    s.name || '',
    safeCell_(s.body || ''),
    now
  ]);
  replaceSheet_('Signatures', ['id','name','bodyHtml','updatedAt'], signatureRows);

  const settings = data.settings || {};
  const meta = [
    ['lastSyncAt', now],
    ['app', 'Telering Email Template Builder'],
    ['senderName', settings.senderName || ''],
    ['replyTo', settings.replyTo || ''],
    ['activeUser', sender.activeUser || sender.sendingAs || ''],
    ['remainingMailAppQuota', quota === null ? '' : quota],
    ['spreadsheetName', ss_().getName()]
  ];
  replaceSheet_('Settings', ['key','value'], meta);
}

function getAllData_() {
  const categoryRows = values_('Categories', ['categoryId','categoryName','contactId','contactName','email','updatedAt']);
  const map = {};
  categoryRows.forEach(r => {
    const cid = String(r[0] || '');
    if (!cid) return;
    if (!map[cid]) map[cid] = { id: cid, name: String(r[1] || ''), items: [] };
    map[cid].items.push({
      id: String(r[2] || Utilities.getUuid()),
      name: String(r[3] || ''),
      email: String(r[4] || '')
    });
  });

  const templateRows = values_('Templates', ['id','name','subject','bodyHtml','updatedAt']);
  const templates = templateRows.map(r => ({
    id: String(r[0] || Utilities.getUuid()),
    name: String(r[1] || ''),
    subject: String(r[2] || ''),
    body: String(r[3] || '')
  }));

  const signatureRows = values_('Signatures', ['id','name','bodyHtml','updatedAt']);
  const signatures = signatureRows.map(r => ({
    id: String(r[0] || Utilities.getUuid()),
    name: String(r[1] || ''),
    body: String(r[2] || '')
  }));

  const settingRows = values_('Settings', ['key','value']);
  const settings = {};
  settingRows.forEach(r => {
    const key = String(r[0] || '');
    if (key) settings[key] = String(r[1] || '');
  });

  const historyRows = values_('History', historyHeaders_());
  const history = historyRows.slice(-300).map(r => ({
    timestamp: String(r[0] || ''),
    serverTimestamp: String(r[0] || ''),
    clientTimestamp: String(r[1] || ''),
    batchId: String(r[2] || ''),
    senderEmail: String(r[3] || ''),
    email: String(r[4] || ''),
    name: String(r[5] || ''),
    subject: String(r[6] || ''),
    templateName: String(r[8] || ''),
    signatureName: String(r[9] || ''),
    status: String(r[10] || ''),
    message: String(r[11] || ''),
    remainingQuotaAfter: String(r[12] || ''),
    attachmentNames: String(r[15] || ''),
    attachmentLinks: String(r[16] || '')
  }));

  return {
    categories: Object.values(map),
    templates: templates,
    signatures: signatures,
    settings: {
      senderName: settings.senderName || '',
      replyTo: settings.replyTo || ''
    },
    history: history
  };
}

function sendEmailBatch_(mail, clientTime, clientTimeText, userAgent) {
  const recipients = mail.recipients || [];
  const subject = String(mail.subject || '').trim();
  const bodyHtml = String(mail.bodyHtml || '').trim();
  const senderName = String(mail.senderName || '').trim();
  const replyTo = String(mail.replyTo || '').trim();
  const templateName = String(mail.templateName || '').trim();
  const signatureName = String(mail.signatureName || '').trim();
  const batchId = Utilities.getUuid();
  const sender = senderInfo_();
  const senderEmail = sender.sendingAs || sender.activeUser || '';
  const serverTimestamp = new Date().toISOString();
  const clientTimestamp = String(mail.clientTimestamp || clientTime || '');
  const clientLocalTime = String(mail.clientLocalTime || clientTimeText || '');
  const preparedAttachments = prepareEmailAttachments_(mail.attachments || [], batchId);
  const attachmentNames = preparedAttachments.names.join('\n');
  const attachmentLinks = preparedAttachments.links.join('\n');
  const logs = [];

  if (!recipients.length) throw new Error('Tidak ada penerima.');
  if (!subject) throw new Error('Subjek email kosong.');
  if (!stripHtml_(bodyHtml)) throw new Error('Badan email kosong.');

  recipients.forEach(rec => {
    const email = String(rec.email || '').trim();
    const name = String(rec.name || '').trim();
    let status = 'SENT';
    let message = preparedAttachments.warning ? 'Terkirim. Catatan attachment: ' + preparedAttachments.warning : 'Terkirim';

    try {
      if (!isEmail_(email)) throw new Error('Format email tidak valid.');
      const options = {
        to: email,
        subject: subject,
        htmlBody: bodyHtml,
        body: stripHtml_(bodyHtml)
      };
      if (preparedAttachments.blobs.length) options.attachments = preparedAttachments.blobs;
      if (senderName) options.name = senderName;
      if (replyTo && isEmail_(replyTo)) options.replyTo = replyTo;
      MailApp.sendEmail(options);
    } catch (err) {
      status = 'FAILED';
      message = String(err && err.message ? err.message : err);
    }

    const quotaAfter = safeQuota_();
    logs.push([
      serverTimestamp,
      clientTimestamp,
      batchId,
      senderEmail,
      email,
      name,
      subject,
      safeCell_(bodyHtml),
      templateName,
      signatureName,
      status,
      message,
      quotaAfter === null ? '' : quotaAfter,
      clientLocalTime,
      String(userAgent || ''),
      attachmentNames,
      attachmentLinks
    ]);
  });

  const sh = sheet_('History', historyHeaders_());
  if (logs.length) sh.getRange(sh.getLastRow() + 1, 1, logs.length, historyHeaders_().length).setValues(logs);
  sh.autoResizeColumns(1, Math.min(historyHeaders_().length, 12));

  return {
    batchId: batchId,
    sent: logs.filter(r => r[10] === 'SENT').length,
    failed: logs.filter(r => r[10] === 'FAILED').length,
    sender: sender,
    attachmentNames: attachmentNames,
    attachmentLinks: attachmentLinks,
    logs: logs.map(r => ({
      timestamp: r[0],
      serverTimestamp: r[0],
      clientTimestamp: r[1],
      batchId: r[2],
      senderEmail: r[3],
      email: r[4],
      name: r[5],
      subject: r[6],
      templateName: r[8],
      signatureName: r[9],
      status: r[10],
      message: r[11],
      remainingQuotaAfter: r[12],
      clientLocalTime: r[13],
      attachmentNames: r[15],
      attachmentLinks: r[16]
    }))
  };
}

function prepareEmailAttachments_(attachments, batchId) {
  const list = Array.isArray(attachments) ? attachments : [];
  const result = { blobs: [], names: [], links: [], warning: '' };
  if (!list.length) return result;

  let folder = null;
  try {
    folder = attachmentFolder_();
  } catch (err) {
    result.warning = 'Drive log gagal dibuat: ' + String(err && err.message ? err.message : err);
  }

  list.forEach((att, index) => {
    const name = String(att.name || ('attachment-' + (index + 1))).trim();
    const mimeType = String(att.mimeType || 'application/octet-stream').trim();
    const base64 = String(att.base64 || '');
    if (!base64) return;

    const bytes = Utilities.base64Decode(base64);
    const blob = Utilities.newBlob(bytes, mimeType, name);
    result.blobs.push(blob);
    result.names.push(name);

    if (folder) {
      try {
        const safeName = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyyMMdd-HHmmss') + '_' + batchId + '_' + name;
        const file = folder.createFile(blob.copyBlob().setName(safeName));
        result.links.push(name + ': ' + file.getUrl());
      } catch (err) {
        result.links.push(name + ': Drive upload gagal - ' + String(err && err.message ? err.message : err));
      }
    }
  });

  return result;
}

function attachmentFolder_() {
  const folderName = 'Telering Email Template Builder Attachments';
  const folders = DriveApp.getFoldersByName(folderName);
  if (folders.hasNext()) return folders.next();
  return DriveApp.createFolder(folderName);
}

function historyHeaders_() {
  return [
    'serverTimestamp',
    'clientTimestamp',
    'batchId',
    'senderEmail',
    'recipientEmail',
    'recipientName',
    'subject',
    'bodyHtml',
    'templateName',
    'signatureName',
    'status',
    'message',
    'remainingMailAppQuotaAfter',
    'clientLocalTime',
    'browserUserAgent',
    'attachmentNames',
    'attachmentDriveLinks'
  ];
}

function senderInfo_() {
  let effectiveUser = '';
  let activeUserRaw = '';

  try { effectiveUser = Session.getEffectiveUser().getEmail() || ''; } catch (err) {}
  try { activeUserRaw = Session.getActiveUser().getEmail() || ''; } catch (err) {}

  const email = effectiveUser || activeUserRaw || '';
  return {
    sendingAs: email,
    activeUser: email,
    note: 'Email dikirim oleh akun Google yang menjalankan Apps Script Web App. Pada deployment Execute as: Me, ini adalah akun deployer.'
  };
}

function safeCell_(value) {
  const text = String(value || '');
  return text.length > 45000 ? text.slice(0, 45000) + '\n...[dipotong karena terlalu panjang]' : text;
}

function stripHtml_(html) {
  return String(html || '')
    .replace(/<style[\s\S]*?<\/style>/gi, ' ')
    .replace(/<script[\s\S]*?<\/script>/gi, ' ')
    .replace(/<[^>]+>/g, ' ')
    .replace(/ /g, ' ')
    .replace(/\s+/g, ' ')
    .trim();
}

function isEmail_(email) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(String(email || '').trim());
}

function safeQuota_() {
  try { return MailApp.getRemainingDailyQuota(); }
  catch (err) { return null; }
}

Memproses...

Mohon tunggu sebentar.