/**
* @OnlyCurrentDoc
* Script com Triggers Automáticos - Funciona com planilha fechada
*/
// Configurações
const RECIPIENT_COL = "Email";
const EMAIL_SENT_COL = "Email Sent";
const DRAFT_SUBJECT = "Uma dúvida sobre a {{empresa}}";
// Configurações do envio automático
const DAILY_BATCH_SIZE = 95; // Emails por lote
const INTERVAL_MINUTES = 1450; // Intervalo entre lotes (1450 min = 24h10min)
/**
* Instala o trigger automático com intervalo personalizado
*/
function installDailyTrigger() {
try {
// Remove triggers antigos
const triggers = ScriptApp.getProjectTriggers();
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === 'sendScheduledBatch') {
ScriptApp.deleteTrigger(trigger);
}
});
// Marca como ativo
const properties = PropertiesService.getScriptProperties();
properties.setProperties({
'AUTO_SENDING_ACTIVE': 'true',
'BATCH_SIZE': DAILY_BATCH_SIZE.toString(),
'INTERVAL_MINUTES': INTERVAL_MINUTES.toString(),
'TOTAL_SENT': '0'
});
// EXECUTA IMEDIATAMENTE o primeiro lote
console.log('Executando primeiro lote imediatamente...');
const result = sendEmailsBatch(DAILY_BATCH_SIZE);
// Atualiza contador
properties.setProperties({
'TOTAL_SENT': result.sent.toString(),
'LAST_EXECUTION': new Date().toISOString()
});
const hours = Math.floor(INTERVAL_MINUTES / 60);
const minutes = INTERVAL_MINUTES % 60;
// Se ainda há emails, agenda próximo lote
if (result.hasMore) {
const nextRun = new Date(Date.now() + (INTERVAL_MINUTES * 60 * 1000));
ScriptApp.newTrigger('sendScheduledBatch')
.timeBased()
.at(nextRun)
.create();
SpreadsheetApp.getUi().alert(
'Automação Iniciada!',
`✅ Primeiro lote executado AGORA!\n\n` +
`• Emails enviados: ${result.sent}/${DAILY_BATCH_SIZE}\n` +
`• Próximo lote: ${Utilities.formatDate(nextRun, 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss')}\n` +
`• Intervalo: ${hours}h${minutes}min\n` +
`• Emails restantes: Sim\n` +
`• Funciona com planilha fechada\n\n` +
`Para pausar, use "Remover Trigger Automático"`,
SpreadsheetApp.getUi().ButtonSet.OK
);
} else {
// Não há mais emails
properties.setProperty('AUTO_SENDING_ACTIVE', 'false');
SpreadsheetApp.getUi().alert(
'Envio Concluído!',
`✅ Todos os emails foram enviados!\n\n` +
`• Emails enviados: ${result.sent}\n` +
`• Não há mais emails na lista\n\n` +
`Processo finalizado.`,
SpreadsheetApp.getUi().ButtonSet.OK
);
}
} catch (error) {
console.error('Erro ao instalar trigger:', error);
SpreadsheetApp.getUi().alert('Erro', `Erro: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
}
}
/**
* Remove o trigger automático
*/
function removeDailyTrigger() {
try {
const triggers = ScriptApp.getProjectTriggers();
let removed = 0;
triggers.forEach(trigger => {
if (trigger.getHandlerFunction() === 'sendScheduledBatch') {
ScriptApp.deleteTrigger(trigger);
removed++;
}
});
// Marca como inativo
const properties = PropertiesService.getScriptProperties();
properties.setProperty('AUTO_SENDING_ACTIVE', 'false');
const totalSent = properties.getProperty('TOTAL_SENT') || '0';
SpreadsheetApp.getUi().alert(
'Automação Pausada',
`🛑 Automação pausada!\n\n` +
`• ${removed} trigger(s) removido(s)\n` +
`• Total enviado: ${totalSent} emails\n\n` +
`Use "Iniciar Automação (AGORA)" para reativar.`,
SpreadsheetApp.getUi().ButtonSet.OK
);
} catch (error) {
console.error('Erro ao remover trigger:', error);
SpreadsheetApp.getUi().alert('Erro', `Erro: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
}
}
/**
* Função executada automaticamente a cada intervalo
*/
function sendScheduledBatch() {
try {
console.log('Executando lote automático...');
const properties = PropertiesService.getScriptProperties();
const isActive = properties.getProperty('AUTO_SENDING_ACTIVE') === 'true';
if (!isActive) {
console.log('Envio automático foi pausado, parando execução');
return;
}
const result = sendEmailsBatch(DAILY_BATCH_SIZE);
// Atualiza estatísticas
const currentTotal = parseInt(properties.getProperty('TOTAL_SENT') || '0');
const newTotal = currentTotal + result.sent;
properties.setProperties({
'TOTAL_SENT': newTotal.toString(),
'LAST_EXECUTION': new Date().toISOString()
});
console.log(`Lote concluído: ${result.sent} emails enviados. Total: ${newTotal}`);
// Se ainda há emails, agenda próximo lote
if (result.hasMore) {
// Agenda próxima execução
const nextRun = new Date(Date.now() + (INTERVAL_MINUTES * 60 * 1000));
ScriptApp.newTrigger('sendScheduledBatch')
.timeBased()
.at(nextRun)
.create();
console.log(`Próximo lote agendado para: ${Utilities.formatDate(nextRun, 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss')}`);
// NOTIFICAÇÃO POR EMAIL (SEM POPUP)
try {
GmailApp.sendEmail(
Session.getActiveUser().getEmail(),
'Lote de Emails Enviado - Automação Ativa',
`✅ Lote executado com sucesso!\n\n` +
`• Emails enviados neste lote: ${result.sent}\n` +
`• Total enviado até agora: ${newTotal}\n` +
`• Próximo lote: ${Utilities.formatDate(nextRun, 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss')}\n` +
`• Planilha: ${SpreadsheetApp.getActiveSpreadsheet().getName()}\n\n` +
`A automação continua rodando em segundo plano.\n` +
`Para parar, acesse a planilha e use "Pausar Automação".`
);
} catch (e) {
console.error('Erro ao enviar notificação de lote:', e);
}
} else {
// Não há mais emails, finaliza
console.log('Todos os emails foram enviados! Finalizando automação...');
properties.setProperty('AUTO_SENDING_ACTIVE', 'false');
// NOTIFICAÇÃO FINAL POR EMAIL (SEM POPUP)
try {
GmailApp.sendEmail(
Session.getActiveUser().getEmail(),
'🎉 Automação Finalizada - Todos os Emails Enviados!',
`✅ MISSÃO CUMPRIDA!\n\n` +
`• Total de emails enviados: ${newTotal}\n` +
`• Último lote: ${result.sent} emails\n` +
`• Planilha: ${SpreadsheetApp.getActiveSpreadsheet().getName()}\n` +
`• Finalizado em: ${Utilities.formatDate(new Date(), 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss')}\n\n` +
`🎯 Todos os emails da sua lista foram enviados com sucesso!\n` +
`A automação foi finalizada automaticamente.\n\n` +
`Parabéns! 🚀`
);
} catch (e) {
console.error('Erro ao enviar notificação final:', e);
}
}
} catch (error) {
console.error('Erro no lote automático:', error);
// Pausa automação em caso de erro
const properties = PropertiesService.getScriptProperties();
properties.setProperty('AUTO_SENDING_ACTIVE', 'false');
// NOTIFICAÇÃO DE ERRO POR EMAIL (SEM POPUP)
try {
GmailApp.sendEmail(
Session.getActiveUser().getEmail(),
'❌ ERRO na Automação de Emails',
`❌ A automação encontrou um erro e foi pausada:\n\n` +
`ERRO: ${error.message}\n\n` +
`• Planilha: ${SpreadsheetApp.getActiveSpreadsheet().getName()}\n` +
`• Erro em: ${Utilities.formatDate(new Date(), 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss')}\n\n` +
`🔧 AÇÃO NECESSÁRIA:\n` +
`1. Verifique a planilha\n` +
`2. Corrija o problema\n` +
`3. Reative a automação se necessário\n\n` +
`A automação foi pausada para evitar mais erros.`
);
} catch (e) {
console.error('Erro ao enviar notificação de erro:', e);
}
}
}
/**
* Envia lote de emails (versão otimizada)
*/
function sendEmailsBatch(batchSize) {
const sheet = SpreadsheetApp.getActiveSheet();
const dataRange = sheet.getDataRange();
const data = dataRange.getDisplayValues();
const heads = data.shift();
const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
if (emailSentColIdx === -1) {
throw new Error(`Coluna '${EMAIL_SENT_COL}' não encontrada.`);
}
const emailTemplate = getGmailTemplateFromDrafts_(DRAFT_SUBJECT);
const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));
let emailsSent = 0;
let hasMore = false;
const updates = [];
// Processa apenas emails não enviados
for (let rowIdx = 0; rowIdx < obj.length && emailsSent < batchSize; rowIdx++) {
const row = obj[rowIdx];
if (row[EMAIL_SENT_COL] === '') {
try {
const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
name: 'Rodrigo Dias',
attachments: emailTemplate.attachments,
inlineImages: emailTemplate.inlineImages
});
updates.push({
row: rowIdx + 2,
value: new Date()
});
emailsSent++;
console.log(`Email enviado para: ${row[RECIPIENT_COL]} (${emailsSent}/${batchSize})`);
// Delay para evitar rate limiting
if (emailsSent < batchSize) {
Utilities.sleep(1000); // 1 segundo
}
} catch (e) {
console.error(`Erro ao enviar email para ${row[RECIPIENT_COL]}:`, e);
updates.push({
row: rowIdx + 2,
value: `Erro: ${e.message}`
});
}
}
}
// Atualiza planilha em lote
updates.forEach(update => {
sheet.getRange(update.row, emailSentColIdx + 1).setValue(update.value);
});
// Verifica se ainda há emails
for (let rowIdx = 0; rowIdx < obj.length; rowIdx++) {
if (obj[rowIdx][EMAIL_SENT_COL] === '') {
hasMore = true;
break;
}
}
return {
sent: emailsSent,
hasMore: hasMore
};
}
/**
* Verifica status dos triggers
*/
function checkTriggerStatus() {
const properties = PropertiesService.getScriptProperties();
const isActive = properties.getProperty('AUTO_SENDING_ACTIVE') === 'true';
const totalSent = properties.getProperty('TOTAL_SENT') || '0';
const lastExecution = properties.getProperty('LAST_EXECUTION');
const triggers = ScriptApp.getProjectTriggers();
const activeTriggers = triggers.filter(t => t.getHandlerFunction() === 'sendScheduledBatch');
if (!isActive || activeTriggers.length === 0) {
SpreadsheetApp.getUi().alert(
'Status: Inativo',
`🔴 Automação inativa\n\n` +
`• Total enviado: ${totalSent} emails\n` +
`• Última execução: ${lastExecution ? Utilities.formatDate(new Date(lastExecution), 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss') : 'Nunca'}\n\n` +
`Use "Iniciar Automação (AGORA)" para reativar.`,
SpreadsheetApp.getUi().ButtonSet.OK
);
} else {
const trigger = activeTriggers[0];
const nextRun = new Date(trigger.getTriggerSource().getNextRun());
const hours = Math.floor(INTERVAL_MINUTES / 60);
const minutes = INTERVAL_MINUTES % 60;
SpreadsheetApp.getUi().alert(
'Status: Ativo',
`🟢 Automação ativa!\n\n` +
`• Próxima execução: ${Utilities.formatDate(nextRun, 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss')}\n` +
`• Lote: ${DAILY_BATCH_SIZE} emails\n` +
`• Intervalo: ${hours}h${minutes}min\n` +
`• Total enviado: ${totalSent} emails\n` +
`• Última execução: ${lastExecution ? Utilities.formatDate(new Date(lastExecution), 'America/Sao_Paulo', 'dd/MM/yyyy HH:mm:ss') : 'Nunca'}`,
SpreadsheetApp.getUi().ButtonSet.OK
);
}
}
/**
* Menu atualizado
*/
function onOpen() {
try {
const ui = SpreadsheetApp.getUi();
ui.createMenu('📧 Mail Merge')
.addItem('Enviar Emails', 'sendEmailsAuto')
.addItem('Enviar Emails (Título Manual)', 'sendEmailsManual')
.addSeparator()
.addSubMenu(ui.createMenu('🤖 Envio Automático')
.addItem('🚀 Iniciar Automação (AGORA)', 'installDailyTrigger')
.addItem('🛑 Pausar Automação', 'removeDailyTrigger')
.addItem('📊 Verificar Status', 'checkTriggerStatus')
.addItem('🧪 Teste Manual (5 emails)', 'testBatch'))
.addSeparator()
.addItem('Reinstalar Menu', 'reinstallMenu')
.addToUi();
ui.createMenu('🧹 Limpar Dados')
.addItem('Limpar e Preparar para Colar', 'clearSheetAndPrepare')
.addItem('Limpar Colunas Específicas', 'clearSpecificColumnsAndPrepare')
.addToUi();
console.log('Menus criados com sucesso');
} catch (error) {
console.error('Erro ao criar menus:', error);
}
}
/**
* Teste manual com 5 emails
*/
function testBatch() {
try {
const result = sendEmailsBatch(5);
SpreadsheetApp.getUi().alert(
'Teste Concluído',
`✅ Teste manual finalizado!\n\n` +
`• Emails enviados: ${result.sent}\n` +
`• Mais emails na fila: ${result.hasMore ? 'Sim' : 'Não'}`,
SpreadsheetApp.getUi().ButtonSet.OK
);
} catch (error) {
SpreadsheetApp.getUi().alert('Erro', `Erro no teste: ${error.message}`, SpreadsheetApp.getUi().ButtonSet.OK);
}
}
// Funções originais mantidas...
function sendEmailsAuto() {
if (!DRAFT_SUBJECT || DRAFT_SUBJECT === "Seu Título do Rascunho Aqui") {
SpreadsheetApp.getUi().alert(
'Erro de Configuração',
'Por favor, defina o título do rascunho na variável DRAFT_SUBJECT no início do script.',
SpreadsheetApp.getUi().ButtonSet.OK
);
return;
}
sendEmails(DRAFT_SUBJECT);
}
function sendEmailsManual() {
const subjectLine = Browser.inputBox("Mail Merge",
"Digite ou cole o assunto do rascunho do Gmail:",
Browser.Buttons.OK_CANCEL);
if (subjectLine === "cancel" || subjectLine == ""){
return;
}
sendEmails(subjectLine);
}
function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {
try {
const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
const dataRange = sheet.getDataRange();
const data = dataRange.getDisplayValues();
const heads = data.shift();
const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
if (emailSentColIdx === -1) {
throw new Error(`Coluna '${EMAIL_SENT_COL}' não encontrada.`);
}
const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));
const out = [];
let emailsSent = 0;
obj.forEach(function(row, rowIdx){
if (row[EMAIL_SENT_COL] == ''){
try {
const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);
GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
htmlBody: msgObj.html,
name: 'Rodrigo Dias',
attachments: emailTemplate.attachments,
inlineImages: emailTemplate.inlineImages
});
out.push([new Date()]);
emailsSent++;
} catch(e) {
console.error('Erro ao enviar email:', e);
out.push([`Erro: ${e.message}`]);
}
} else {
out.push([row[EMAIL_SENT_COL]]);
}
});
if (out.length > 0) {
sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);
}
SpreadsheetApp.getUi().alert(`Processo concluído! ${emailsSent} email(s) enviado(s).`);
} catch (error) {
console.error('Erro no processo de envio:', error);
SpreadsheetApp.getUi().alert(`Erro: ${error.message}`);
}
}
function getGmailTemplateFromDrafts_(subject_line){
try {
const drafts = GmailApp.getDrafts();
const draft = drafts.filter(subjectFilter_(subject_line))[0];
if (!draft) {
throw new Error(`Rascunho com assunto "${subject_line}" não encontrado.`);
}
const msg = draft.getMessage();
const allInlineImages = msg.getAttachments({includeInlineImages: true, includeAttachments: false});
const attachments = msg.getAttachments({includeInlineImages: false});
const htmlBody = msg.getBody();
const img_obj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj), {});
const imgexp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
const matches = [...htmlBody.matchAll(imgexp)];
const inlineImagesObj = {};
matches.forEach(match => inlineImagesObj[match[1]] = img_obj[match[2]]);
return {
message: {subject: subject_line, text: msg.getPlainBody(), html: htmlBody},
attachments: attachments,
inlineImages: inlineImagesObj
};
} catch(e) {
throw new Error(`Erro ao buscar rascunho: ${e.message}`);
}
function subjectFilter_(subject_line){
return function(element) {
if (element.getMessage().getSubject() === subject_line) {
return element;
}
}
}
}
function fillInTemplateFromObject_(template, data) {
let template_string = JSON.stringify(template);
template_string = template_string.replace(/{{[^{}]+}}/g, key => {
return escapeData_(data[key.replace(/[{}]+/g, "")] || "");
});
return JSON.parse(template_string);
}
function escapeData_(str) {
return str
.replace(/[\\]/g, '\\\\')
.replace(/[\"]/g, '\\\"')
.replace(/[\/]/g, '\\/')
.replace(/[\b]/g, '\\b')
.replace(/[\f]/g, '\\f')
.replace(/[\n]/g, '\\n')
.replace(/[\r]/g, '\\r')
.replace(/[\t]/g, '\\t');
}
function reinstallMenu() {
onOpen();
SpreadsheetApp.getUi().alert('Menus reinstalados com sucesso!');
}
function clearSheetAndPrepare() {
const sheet = SpreadsheetApp.getActiveSheet();
const ui = SpreadsheetApp.getUi();
try {
const frozenRows = sheet.getFrozenRows();
const headerRows = frozenRows > 0 ? frozenRows : 1;
const lastRow = sheet.getLastRow();
const lastColumn = sheet.getLastColumn();
const dataStartRow = headerRows + 1;
if (lastRow > headerRows && lastColumn > 0) {
const rowsToDelete = lastRow - headerRows;
const rangeToClear = sheet.getRange(dataStartRow, 1, rowsToDelete, lastColumn);
rangeToClear.clearContent();
}
const targetCell = sheet.getRange(dataStartRow, 1);
targetCell.activate();
SpreadsheetApp.flush();
SpreadsheetApp.getActiveSpreadsheet().toast(
'Dados limpos! Agora pressione Ctrl+V para colar os novos dados.',
'Pronto para colar',
3
);
} catch (error) {
ui.alert('Erro', `Erro ao limpar a planilha: ${error.message}`, ui.ButtonSet.OK);
console.error('Erro ao limpar planilha:', error);
}
}
function clearSpecificColumnsAndPrepare() {
const sheet = SpreadsheetApp.getActiveSheet();
const ui = SpreadsheetApp.getUi();
const columnsInput = ui.prompt(
'Limpar Colunas Específicas',
'Quantas colunas você quer limpar? (Ex: 3 para colunas A, B e C)',
ui.ButtonSet.OK_CANCEL
);
if (columnsInput.getSelectedButton() !== ui.Button.OK) {
return;
}
const COLUMNS_TO_CLEAR = parseInt(columnsInput.getResponseText()) || 2;
try {
const frozenRows = sheet.getFrozenRows();
const headerRows = frozenRows > 0 ? frozenRows : 1;
const lastRow = sheet.getLastRow();
const dataStartRow = headerRows + 1;
if (lastRow > headerRows) {
const rowsToDelete = lastRow - headerRows;
const rangeToClear = sheet.getRange(dataStartRow, 1, rowsToDelete, COLUMNS_TO_CLEAR);
rangeToClear.clearContent();
}
const targetCell = sheet.getRange(dataStartRow, 1);
targetCell.activate();
SpreadsheetApp.flush();
SpreadsheetApp.getActiveSpreadsheet().toast(
`Primeiras ${COLUMNS_TO_CLEAR} colunas limpas! Pressione Ctrl+V para colar.`,
'Pronto para colar',
3
);
} catch (error) {
ui.alert('Erro', `Erro: ${error.message}`, ui.ButtonSet.OK);
console.error('Erro ao limpar colunas específicas:', error);
}
}