Backlog reports related mail send to group DL along with attachement.

[Manohararuna]

Hello Everyone,

i have one requirement .Send a mail to all assignment group email ,if groups not having the email address then send a mail to group managers along with excel sheet with excel sheet  related to one month back created and still active  backlog data .

and the mail content is like below -

Hello Team,

Hope you are doing well.

Below provided is the list of long pending Incidents which are greater than 30 Days in Assignment Group Name assignment group. Kindly ensure that the incidents are closed on time or update the work logs.

Please close/add work log on the below incidents and update us the status.

Appreciate your support.

Count of Incidents: .

But based on my below scheduled job i want to created excel sheet and attached to the mail  and mail content need to populate the records data like number and ,description,opened information.please tell me where i have to update my below scheduled job script:

(function () {

    gs.log("=== Backlog Job Started ===");

    var DAYS = 30;

    var taskGR = new GlideRecord("change_task");

    taskGR.addQuery("active", true);

    taskGR.addQuery("sys_created_on", "<=", gs.daysAgoStart(DAYS));

    taskGR.addNotNullQuery("assignment_group");

    taskGR.query();

    var groupMap = {};

    while (taskGR.next()) {

        var grp = taskGR.getValue("assignment_group");

        if (!groupMap[grp])

            groupMap[grp] = [];

        groupMap[grp].push({

            number: taskGR.getValue("number"),

            desc: taskGR.getValue("short_description"),

            state: taskGR.getDisplayValue("state"),

            priority: taskGR.getDisplayValue("priority"),

            created: taskGR.getDisplayValue("sys_created_on")

        });

    }

    gs.log("Total Groups Found: " + Object.keys(groupMap).length);

    // ---------------- Process Each Group ----------------

    for (var grpId in groupMap) {

        try {

            var grpRec = new GlideRecord("sys_user_group");

            if (!grpRec.get(grpId))

                continue;

            var recipientEmail = "";

            // FIRST preference → Group Email

            if (grpRec.email) {

                recipientEmail = grpRec.email.toString();

                gs.log("Sending to Group Email: " + grpRec.name);

            } else if (grpRec.manager) {

                // SECOND preference → Manager Email

                var mgr = new GlideRecord("sys_user");

                if (mgr.get(grpRec.manager) && mgr.email) {

                    recipientEmail = mgr.email.toString();

                    gs.log("Group Email missing. Sending to Manager: " + mgr.name);

                }

            }

            if (!recipientEmail) {

                gs.log("Skipped - No Email Found for Group: " + grpRec.name);

                continue;

            }

            var records = groupMap[grpId];

            // ---------------- Build REAL Excel (.xlsx) ----------------

            var builder = new sn_impex.GlideExcelBuilder();

            builder.setFileName(grpRec.name + "_Backlog.xlsx");

            builder.addSheet("Backlog Report");

            builder.addRow(["Number", "Description", "State", "Priority", "Created"]);

            for (var i = 0; i < records.length; i++) {

                builder.addRow([

                    records[i].number,

                    records[i].desc,

                    records[i].state,

                    records[i].priority,

                    records[i].created

                ]);

            }

            // Attach Excel to Email

            builder.build("sys_email", emailSysId);

        } catch (ex) {

            gs.error("Error Processing Group: " + grpId + " -> " + ex.message);

        }

    }

})();