Hi all,
I created a notification email script (code below), I would like to export the result to an excel file (csv or xlsx), then I would like to send the result (excel) via scheduled report? if is not possible, is there another way?
Thanks in advance.
Below is my code of notification email script and my scheduled report (figure)
(function runMailScript(current, template, email, email_action, event) {
var print_t = ''; // Início da tabela
// Estilo da tabela e cabeçalho
print_t += '<table style="border-collapse: collapse; font-family: Arial; width: 100%; border: 1px solid black;">';
print_t += '<tr>';
print_t += '<th style="border: 1px solid black;">Número</th>';
print_t += '<th style="border: 1px solid black;">CNPJ</th>';
print_t += '<th style="border: 1px solid black;">Corban</th>';
print_t += '<th style="border: 1px solid black;">Cód. Débito</th>';
print_t += '<th style="border: 1px solid black;">Valor</th>'; // Nova coluna
print_t += '<th style="border: 1px solid black;">Descrição</th>';
print_t += '</tr>';
var groupedData = {}; // Objeto para agrupar os dados
// Primeira consulta - Saldo devedor
var gr1 = new GlideRecord("sc_task");
gr1.addEncodedQuery("assignment_group=b1c800da87a95290c57a85140cbb351d^request_item.cat_item=956507bb1b4aca5859868590f54bcb18^variables.d4600f7233f70a50a9f2b532cd5c7b10!=true^variables.1aae3aba33b70a50a9f2b532cd5c7b2a=false");
gr1.orderBy("number");
gr1.query();
while (gr1.next()) {
var number = gr1.getDisplayValue('number'); // Número
var cnpj = gr1.getDisplayValue('variables.d6ae757b33d74210a9f2b532cd5c7b02'); // CNPJ
var corban = gr1.getDisplayValue('variables.18b6775a1bf24a5459868590f54bcb09'); // Corban
var cod_debito = gr1.getDisplayValue('variables.42a180993b259e9052e60237f4e45aa2'); // Cód. Débito
var valor = gr1.getDisplayValue('variables.8f23d4ea1bf64a5459868590f54bcbd8'); // Valor
var variableValue = gr1.getDisplayValue('variables.709c49953b6d12d052e60237f4e45a82'); // Descrição
if (!groupedData[number]) {
groupedData[number] = {
cnpj: cnpj,
cod_debito: cod_debito,
corban: corban,
valor: valor,
cnpj_lista: [],
codigo_corban: [],
codigo_debito: [],
valores: [],
descriptions: []
};
}
groupedData[number].cnpj_lista.push(cnpj);
groupedData[number].codigo_corban.push(corban);
groupedData[number].codigo_debito.push(cod_debito);
groupedData[number].valores.push(valor);
groupedData[number].descriptions.push(variableValue);
}
// Segunda consulta - Custas
var gr2 = new GlideRecord("sc_task");
gr2.addEncodedQuery("assignment_group=b1c800da87a95290c57a85140cbb351d^request_item.cat_item=956507bb1b4aca5859868590f54bcb18^variables.d4600f7233f70a50a9f2b532cd5c7b10!=true^variables.43effe3e33b70a50a9f2b532cd5c7bfa=false");
gr2.orderBy("number");
gr2.query();
while (gr2.next()) {
var number = gr2.getDisplayValue('number'); // Número
var cnpj = gr2.getDisplayValue('variables.d6ae757b33d74210a9f2b532cd5c7b02'); // CNPJ
var corban = gr2.getDisplayValue('variables.18b6775a1bf24a5459868590f54bcb09'); // Corban
var cod_debito = gr2.getDisplayValue('variables.6496e2513b821e1052e60237f4e45ae3'); // Cód. Débito
var valor = gr2.getDisplayValue('variables.7a03986a1bf64a5459868590f54bcba5'); // Valor
var variableValue = gr2.getDisplayValue('variables.4c10a0143b3d565052e60237f4e45a7a'); // Descrição
if (!groupedData[number]) {
groupedData[number] = {
cnpj: cnpj,
cod_debito: cod_debito,
corban: corban,
valor: valor,
cnpj_lista: [],
codigo_corban: [],
codigo_debito: [],
valores: [],
descriptions: []
};
}
groupedData[number].cnpj_lista.push(cnpj);
groupedData[number].codigo_corban.push(corban);
groupedData[number].codigo_debito.push(cod_debito);
groupedData[number].valores.push(valor);
groupedData[number].descriptions.push(variableValue);
}
// Construindo as linhas da tabela
for (var key in groupedData) {
var isFirstRow = true; // Controla se é a primeira linha para o número atual
var cnpj_lista = groupedData[key].cnpj_lista; // Recupera CNPJ
var codigo_corban = groupedData[key].codigo_corban; // Recupera Corban
var codigo_debito = groupedData[key].codigo_debito; // Recupera Cód. Débito
var valores = groupedData[key].valores; // Recupera Valor
var descriptions = groupedData[key].descriptions; // Recupera as Descrições
for (var i = 0; i < descriptions.length; i++) {
print_t += '<tr>';
if (isFirstRow) {
print_t += '<td style="border: 1px solid black;" rowspan="' + descriptions.length + '">' + key + '</td>'; // Número
isFirstRow = false;
}
print_t += '<td style="border: 1px solid black;">' + cnpj_lista[i] + '</td>'; // CNPJ
print_t += '<td style="border: 1px solid black;">' + codigo_corban[i] + '</td>'; + corban + '</td>'; // Corban
print_t += '<td style="border: 1px solid black;">' + codigo_debito[i] + '</td>'; // Cód. Débito
print_t += '<td style="border: 1px solid black;">' + valores[i] + '</td>'; // Valor
print_t += '<td style="border: 1px solid black;">' + descriptions[i] + '</td>'; // Descrição
print_t += '</tr>';
}
}
print_t += '</table>'; // Fecha a tabela
// Imprime o conteúdo no template
template.print(print_t);
})(current, template, email, email_action, event);
Isn't that what you've done by specifying CSV as the attachment type?
