Attach queried table data as a link in notification email

[Ravivarman Saee]

i am displaying a table of data(queried from attestation table) in email using email script. i want the same data to be attached as a link in the same email. how to do so?

my email script is below.

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {
	var profiles=[];
	var func=[];
	var subf=[];
	var conr=[];
	var ids=event.parm2; // arr of ids for ia of uniq recipient 8 record
	
	//get all pending attestations
	var gr=new GlideRecord('sn_grc_m2m_item_assessment');
	gr.addQuery('sys_id','IN',ids);
	gr.query();
	while(gr.next())
	{
		profiles.push(gr.item.profile.name.toString()); 
		func.push(gr.item.type.name.toString());   
		subf.push(gr.item.u_sub_function.toString());
		conr.push(gr.item.owner.name.toString());
	}
	//consider unique profiles and functions  
	var unique_array=new ArrayUtil();
	var pf=unique_array.unique(profiles); 
	var fn=unique_array.unique(func); 
	var sfn=unique_array.unique(subf);
	var onr=unique_array.unique(conr);

	var z=1;
	//create table for combination of profiles and functions and count of each combination
	template.print('<table border="1"><tr><td>#No</td><td>Control Owner</td><td>Profile</td><td>Function</td><td>Sub Function</td><td>#Pending Attestation</td></tr>');
	for(var i=0; i<pf.length; i++) //p1
	{
// 		var z=1;
		for(var j=0; j<fn.length; j++) //f1
		{
			for(var k=0; k<sfn.length; k++) //sf1
			{
				for(var l=0; l<onr.length; l++) //co1
				{
					var rec=new GlideRecord('sn_grc_m2m_item_assessment');
					rec.addEncodedQuery('sys_idIN'+ids+'^item.owner.name='+onr[l]+'^item.type.name='+fn[j]+'^item.u_sub_function='+sfn[k]+'^item.profile.name='+pf[i]);
					rec.query();
					var count=rec.getRowCount();
					if(count)
					{
						template.print('<tr><td>' + z + '</td><td>'+ onr[l] +'</td><td>' + pf[i] + '</td><td>' + fn[j] + '</td><td>' + sfn[k] + '</td><td>'+ count +'</td></tr>');
						z++;
					}
				}
			}
		}
	} 
	template.print('</table>');
          // Add your code here

})(current, template, email, email_action, event);

[aman_sharma_07]

Hi @Ravivarman Saee ,

I guess one way could be transforming the same data into csv attachment and provide its link.

But the word of caution is that user accessing this link must have sufficient privilege to download it.

var myScript = {
convertJSONToCSV : function(jsonData) {
    var data = JSON.parse(jsonData);
    var csvRows = [];
    // Extract the headers from the JSON object
    var headers = Object.keys(data[0]);
    csvRows.push(headers.join(','));
    // Extract the values from each JSON object and create a CSV row
    data.forEach(function(object) {
      var values = headers.map(function(header){
        var escapedValue = object[header].toString().replace(/"/g, '\\"');
        return escapedValue;
      });
      csvRows.push(values.join(','));
      });
    return csvRows.join('\n');
  }
};
// Example JSON data
var jsonData = '[{"Name":"John","Age":30,"City":"New York"},{"Name":"Jane","Age":25,"City":"London"}]';
// Convert JSON to CSV
var csvData = myScript.convertJSONToCSV(jsonData);
gs.log(csvData);

Let me know if my response was helpful for you.

[Ravivarman Saee]

I am attaching the queried table link. thanks

[aman_sharma_07]

Hi @Ravivarman Saee ,

I'm glad to know I was able to resolve your query, I would appreciate if you could mark my answers as accepted and helpful, so that any other user facing the same challenge could go through the thread and resolve their query quickly.

Thanks in advance! 😊