Mail script to create excel like table/report with all boarders

[Edwin Fuller]

I have an email script that adds change request records into a table like structure, see screen shot below.

What I would like to do is have all of the borders show, similar to the screen shot below.

Below is my mail script used to generate the table. Can I get help on adding all boarders?

(function runMailScript(current, template, email, email_action, event) {

    // 0=Sunday,1=Monday,2=Tuesday,3=Wednesday,4=Thursday,5=Friday,6=Saturday
	var d = new Date();
    var dayOfWeek = d.getDay();
	var vQuery = '';
	
	if(dayOfWeek == 2 || dayOfWeek == 3 || dayOfWeek == 4 || dayOfWeek == 5) {
	vQuery = "end_dateONYesterday@javascript:gs.beginningOfYesterday()@javascript:gs.endOfYesterday()";
	}

	if(dayOfWeek == 1) {
	vQuery = "end_dateONYesterday@javascript&colon;gs.beginningOfYesterday()@javascript&colon;gs.endOfYesterday()^NQend_dateRELATIVEGT@dayofweek@ago@3^end_date<=javascript&colon;gs.endOfToday()";
	}
	
	var asset = new GlideRecord("change_request");
	asset.addEncodedQuery(vQuery);
	asset.query ();
	
	var body = '<h4></h4><table style="min-width: 50%">';
	body += "<tr><th>Number</th><th>Short Description</th><th>State</th><th>Type</th><th>Assignment Group</th><th>Assigned to</th><th>Planned Start Date</th><th>Planned End Date</th><th>Change Result</th></tr>";
	var rowTemplate = '<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td><td style="text-align:center">{8}</td></tr>';
	
	if (!asset.hasNext()) {
		body = '<tr><td colspan="4"><center><b>No Changes Scheduled For Yesterday</b></center></td></tr>';

	} else {
		while (asset.next()) {
			body += gs.getMessage(rowTemplate, [asset.number, asset.short_description, asset.state.getDisplayValue(), asset.type.getDisplayValue(), asset.assignment_group.getDisplayValue(), asset.assigned_to.getDisplayValue(), asset.start_date, asset.end_date, asset.close_code]);

		}
	}	
	body += '</table>';
	body += '<style>table tr th { border-bottom: 1px solid black } table tr td, table tr th { padding: 5px }</style>';
	
	template.print (body);

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

[Iraj Shaikh · Accepted solution]

Hi @Edwin Fuller 

To ensure that all borders of the table, including the cells, are visible in your email, you need to add CSS styles that define the borders for both the table and the individual table cells (`<td>` and `<th>` elements). You can do this by adding a `<style>` tag with the appropriate CSS rules.

Here's how you can modify the script to include borders for all cells:

(function runMailScript(current, template, email, email_action, event) {

    // 0=Sunday,1=Monday,2=Tuesday,3=Wednesday,4=Thursday,5=Friday,6=Saturday
    var d = new Date();
    var dayOfWeek = d.getDay();
    var vQuery = '';
    
    if(dayOfWeek == 2 || dayOfWeek == 3 || dayOfWeek == 4 || dayOfWeek == 5) {
        vQuery = "end_dateONYesterday@javascript:gs.beginningOfYesterday()@javascript:gs.endOfYesterday()";
    }

    if(dayOfWeek == 1) {
        vQuery = "end_dateONYesterday@javascript&colon;gs.beginningOfYesterday()@javascript&colon;gs.endOfYesterday()^NQend_dateRELATIVEGT@dayofweek@ago@3^end_date<=javascript&colon;gs.endOfToday()";
    }
    
    var asset = new GlideRecord("change_request");
    asset.addEncodedQuery(vQuery);
    asset.query ();
    
    var body = '<h4></h4><table style="min-width: 50%; border-collapse: collapse;">';
    body += "<tr><th>Number</th><th>Short Description</th><th>State</th><th>Type</th><th>Assignment Group</th><th>Assigned to</th><th>Planned Start Date</th><th>Planned End Date</th><th>Change Result</th></tr>";
    var rowTemplate = '<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td><td style="text-align:center">{8}</td></tr>';
    
    if (!asset.hasNext()) {
        body += '<tr><td colspan="9"><center><b>No Changes Scheduled For Yesterday</b></center></td></tr>';
    } else {
        while (asset.next()) {
            body += gs.getMessage(rowTemplate, [asset.number, asset.short_description, asset.state.getDisplayValue(), asset.type.getDisplayValue(), asset.assignment_group.getDisplayValue(), asset.assigned_to.getDisplayValue(), asset.start_date.getDisplayValue(), asset.end_date.getDisplayValue(), asset.close_code.getDisplayValue()]);
        }
    }   
    body += '</table>';
    body += '<style>table, table tr th, table tr td { border: 1px solid black; } table tr th, table tr td { padding: 5px; }</style>';
    
    template.print(body);

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

Here's what was changed:

1. Added `border-collapse: collapse;` to the `<table>` style attribute to ensure that the borders between cells are not doubled.
2. Updated the `<style>` tag to include `border: 1px solid black;` for `table`, `table tr th`, and `table tr td` to apply borders around the entire table and each cell.
3. Corrected the colspan in the "No Changes Scheduled For Yesterday" row to span all 9 columns instead of 4.

With these changes, your table should now display with borders around all cells when the email is rendered.

Please mark this response as correct or helpful if it assisted you with your question.

[Edwin Fuller]

This is beautiful, thank you for the quick response! 

[Edwin Fuller]

When previewing from the notification preview, it renders as below

However, when the same mail script is used in the Introduction message of a scheduled report. The notification renders as below. 

Any ideas why and how to fix?

[Amit Verma]

Hi @Edwin Fuller 

Use the below code to fix this :

(function runMailScript(current, template, email, email_action, event) {

    // 0=Sunday,1=Monday,2=Tuesday,3=Wednesday,4=Thursday,5=Friday,6=Saturday
    var d = new Date();
    var dayOfWeek = d.getDay();
    var vQuery = '';
    
    if(dayOfWeek == 2 || dayOfWeek == 3 || dayOfWeek == 4 || dayOfWeek == 5) {
        vQuery = "end_dateONYesterday@javascript:gs.beginningOfYesterday()@javascript:gs.endOfYesterday()";
    }

    if(dayOfWeek == 1) {
        vQuery = "end_dateONYesterday@javascript&colon;gs.beginningOfYesterday()@javascript&colon;gs.endOfYesterday()^NQend_dateRELATIVEGT@dayofweek@ago@3^end_date<=javascript&colon;gs.endOfToday()";
    }
    
    var asset = new GlideRecord("change_request");
    asset.addEncodedQuery(vQuery);
    asset.query ();
    
    var body = "<h4></h4><table border='1' width='100%' cellspacing='0' cellpadding='0'>";
    body += "<tr><th>Number</th><th>Short Description</th><th>State</th><th>Type</th><th>Assignment Group</th><th>Assigned to</th><th>Planned Start Date</th><th>Planned End Date</th><th>Change Result</th></tr>";
    var rowTemplate = '<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td><td>{5}</td><td>{6}</td><td>{7}</td><td style="text-align:center">{8}</td></tr>';
    
    if (!asset.hasNext()) {
        body += '<tr><td colspan="9"><center><b>No Changes Scheduled For Yesterday</b></center></td></tr>';
    } else {
        while (asset.next()) {
            body += gs.getMessage(rowTemplate, [asset.number, asset.short_description, asset.state.getDisplayValue(), asset.type.getDisplayValue(), asset.assignment_group.getDisplayValue(), asset.assigned_to.getDisplayValue(), asset.start_date.getDisplayValue(), asset.end_date.getDisplayValue(), asset.close_code.getDisplayValue()]);
        }
    }   
    body += '</table>';
    body += '<p><style> table,table tr th, table tr td {border: 1px solid black;} table tr th, table tr td { padding: 5px; }</style></p>';
    
    template.print(body);

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

Just replaced 

var body = '<h4></h4><table style="min-width: 50%; border-collapse: collapse;">';

 with 

var body = "<h4></h4><table border='1' width='100%' cellspacing='0' cellpadding='0'>";

Thanks & Regards

Amit Verma