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

Edwin Fuller · ‎01-11-2024

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&colon;gs.beginningOfYesterday()@javascript&colon;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 · ‎01-11-2024

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&colon;gs.beginningOfYesterday()@javascript&colon;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.

View solution in original post

Edwin Fuller · ‎01-12-2024

Thank you, that fixed it 🙂

Amit Verma · ‎01-14-2024

Hi @Edwin Fuller

Glad to be helpful. Can you please mark my answer as correct so it could be helpful for others as well in future.

Thanks & Regards

Amit Verma

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

Amit Verma · ‎01-11-2024

Hi @Edwin Fuller

You just need to modify the below line in your code

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

with :

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

Thanks & Regards

Amit Verma

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