I have a large database of our employees that lists the last time they took the required trainings. We send regular reports to supervisors with the status of their employees, and I’m wanting to change this to become a “self-service” feature on our SharePoint site. I created a dashboard where the supervisor types in their email, and it populates a table with all the employees they supervise (this column uses the Filter feature in excel). There are two other columns that use xlookup to pull the date the employee completed the training or the date the employee is enrolled in the training.

These filters/xlookup features show all of a supervisor’s employees, so I created two buttons. One button applies an autofilter to remove employees who are up to date on their training (so the supervisor can focus on the people who need attention). This formula uses a date in a specific cell (today() – 730) to find dates earlier than 2 years ago, and then returns the data for these employees. The other button just clears the filter to allow the supervisor to start over.

It was all working great on Excel for Desktop, but fell apart when I tried to use the file on SharePoint, because Excel for the Web does not support VBA click boxes.

I created the “Clear Filter” box simply enough using OfficeJS, but I can’t figure out how to apply the autofilter to return the employees with an expired date (>2 years ago) and the people with no date in that column, because they haven’t taken the training yet (e.g., new employee, etc.).

Can someone look at my code to see where I might be messing things up?

I will say that the code works up to the “Clear Filter” command, because if I run the script when the data is already filtered, it does remove the filter. I get the error message “AutoFilter apply: The argument is invalid or missing or has an incorrect format.”

I think the problem is how I’m creating the two criteria (“” and “<=(today-2 years”) that the filter should be using. Any ideas?

function main(workbook: ExcelScript.Workbook) {
//let selectedCell = workbook.getActiveCell();
let selectedSheet = workbook.getActiveWorksheet();

let range = selectedSheet.getRange("A13:C160");

//const table = workbook.getActiveWorksheet().getTables()[0]; //I was messing around with tables on    the spreadsheet, but because I am using the Filter function in excel, I can't define the area as a formal table.

let autoFilter = selectedSheet.getAutoFilter();

if (autoFilter) {
    selectedSheet.getAutoFilter().clearCriteria();
}

//I know the program works up to here. After this comment, it’s up for grabs…

if (!autoFilter) {
    autoFilter = selectedSheet.getAutoFilter();
}

//let today = new Date("I2"); //I don't need to define today's date for any part later in the script.
let expireDate = new Date("J2");
//let formattedDate = expireDate.toLocaleDateString("en-US");  //I don't know if I need this, because the expireDate comes from a cell in excel formatted to date x/xx/xxxx

const filterCriteria: ExcelScript.FilterCriteria = {
  filterOn: ExcelScript.FilterOn.custom,
  criterion1: "<==$expireDate",
  criterion2: "",
  operator: ExcelScript.FilterOperator.or
}
autoFilter.apply(range, 0, filterCriteria)

}