cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos

Select overdue items from Excel worksheet

This run-script action available on Excel online allows filtering rows within a worksheet based on due date and pass an array of objects containing the rows that are past due. 

 

The following flow uses run-script action by invoking Office Scripts against the following data. It selects items that are past due and not in 'Done' status. You can then use the returned array of objects to connect to any flow you wish. 

 

Input sample dataInput sample data

 

 

Run Script

Run scriptRun script

 

You can now connect the result to any other flow such as send-email.

 

Result:

Result.png

 

 

 

 

 

[
  {
    "contact": "Contact3@mail.com",
    "task": "Send monthly report ",
    "status": "",
    "dueDate": "10/17/2020"
  },
  {
    "contact": "Contact2@mail.com",
    "task": "Confirm MBR event location",
    "status": "",
    "dueDate": "10/2/2020"
  }
]

 

 

 

 

 

Script screenshot: 

in-excel-web.png

Script (called Date):  

 

 

 

 

 

 

function main(workbook: ExcelScript.Workbook): LateEntry[] {
// IF the data is in a table, do this....
  // Get first table in Sheet1.
  let table = workbook.getWorksheet('Sheet1').getTables()[0]; 
  let data = table.getRangeBetweenHeaderAndTotal().getValues();
// If the data happens to be in a worksheet without a table then do this..
  // let data = workbook.getWorksheet('Sheet1').getUsedRange().getValues();

 let selectedRows = [];
 for (let row of data) {
   let [contact, task, status,dueDate] = row;
   if (status !== 'Done' && compreDatesAndSelect(dueDate as number)){
     selectedRows.push({
       contact,
       task,
       status,
       dueDate: getJsDateFromExcel(dueDate).toLocaleDateString()
     });
   }
 }
  console.log(selectedRows);
  return selectedRows;
}
/**
 * Compare the argument date against today's date. If Today < argument date, return true; else false
 */
function compreDatesAndSelect(eDate: number): boolean { 
  let jsDate = getJsDateFromExcel(eDate);
  let today = new Date();
  if (jsDate.getDate() < today.getDate() &&
    jsDate.getMonth() <= today.getMonth() &&
    jsDate.getFullYear() <= today.getFullYear()) {
    return true
  }
  return false
}

function getJsDateFromExcel(excelDate: number): Date {
  return new Date((excelDate - (25567 + 1)) * 86400 * 1000);
}

interface LateEntry {
  contact: string
  task: string
  status: string
  dueDate: string
}

 

 

 

 

 

 

 

Status: New
Comments
Microsoft

Sorry, this was supposed to be in the cookbook (not an idea). Will have to find a way to move it there.