Business Requirement

One of my customers needed a weekly report containing all the pending approvals. The report should be sent via email to the approver attaching the list of records pending approvals; this report should be sent every Friday at noon as a reminder for the approver to acknowledge that they have pending tasks to be approved or rejected.

The Solution

1. You need to create a report to query the Approval [sysapproval_approver] table where State is "Requested" and "Approver is (dynamic) Me". Make sure to publish the report Public or share it with everyone.

2. Now you will need a Scheduled Job [sysauto_script] with the following details:

• Name: Weekly Pending Approvals Report
• Run: Weekly
• Day: Friday
• Time: 12:00
• Script:

execute();

function execute() {
    /* query approval table with GlideAggregate*/
    var approval = new GlideAggregate('sysapproval_approver');
    /* select only requested records */
    approval.addEncodedQuery('state=requested');
    /* group by approver in orer to get unique values (one distinct user at a time) */
    approval.groupBy("approver");
    approval.query();

    while (approval.next()) {
        /* Create a unique Scheduled Report */
        createScheduleReport(approval.approver);
    }
}

function createScheduleReport(user) {
    /* set the appropiate sys_id of your 'Weekly Pending Approvals Report' report */
    var TARGET_REPORT = "9b32139c1b898410a3faddbcdd4bcb69";

    /* create the scheduled report */
    var scheduled_report = new GlideRecord('sysauto_report');
    scheduled_report.initialize();

    /* set all the values */
    scheduled_report.active = true;
    scheduled_report.name = "WeeklyAutoApprovalReminder";
    scheduled_report.run_as = user.getValue("sys_id");
    /* this will replace "is (dynamic) Me" with the real name of the user */
    scheduled_report.user_list = user.getValue("sys_id");
    /* the scheduled report it's unique, hence it should run only once */
    scheduled_report.run_type = 'once';
    scheduled_report.report_title = "Your Weekly Pending Approvals Report";
    scheduled_report.report_body = "Find attached the your weekly Pending Approvals Report";
    scheduled_report.report = TARGET_REPORT;
    scheduled_report.omit_if_no_records = true;
    /* it will attach the records using a spreadsheet attached to the email notification; check the choice list to see other options */
    scheduled_report.output_type = "XLSX";
    var answer = scheduled_report.insert();
}

run_type = 'once'

The scheduled job (script above) will create a unique Scheduled Report for each user that has pending approvals [state='requested'] by the current week. This means that every Friday at noon, dozens or hundreds of Scheduled Reports might get created and all of them will run immediately querying all the pending approvals per user, this is why in the Report of step #1 you have to use the operator "Approver is (dynamic) Me". When the Scheduled Report is created immediately replaces the "is (dynamic) Me" with the name of the dynamic user in question.

Clean up

I recommend to create another scheduled job to delete the used Scheduled Reports generated in the past, remember that they are unique and they run one time only.