How to attach CSV file from notification email script via scheduled report?

MR Carvalho — Mon, 25 Nov 2024 21:09:44 GMT

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);

Re: How to attach CSV file from notification email script via scheduled report?

Uncle Rob — Mon, 25 Nov 2024 21:43:04 GMT

Isn't that what you've done by specifying CSV as the attachment type?

Re: How to attach CSV file from notification email script via scheduled report?

MR Carvalho — Mon, 25 Nov 2024 22:39:46 GMT

No, this option is to send report List as excel, not notification email script.

question How to attach CSV file from notification email script via scheduled report? in Community Central forum

How to attach CSV file from notification email script via scheduled report?

Re: How to attach CSV file from notification email script via scheduled report?

Re: How to attach CSV file from notification email script via scheduled report?