cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arock1211
New Member

Creating a Flow to Send an Email from Excel to Someone Only at a Specified datetime?

Hello,

 

I am attempting to create a Flow that will read an Excel Table with an individual's Name, Email and Date of Yearly Meeting. I would like the Flow to send an email scheduling the meeting for each individual only during the month that the meeting is required. I have built a flow that used a Delay to parse each row of the Meeting Date column for a datetime number. However, I realized that this flow, when executed, will send an email to everyone who has already had their meetings prior to this date as well, which I would not like.

 

So I am wondering if there is a way to create a Flow that sends an email to an individual only during their specified Month?  

 

Picture1.png

Attached is a screenshot of my Excel sheet.

 

Thanks

1 REPLY 1
sumurthy
Microsoft
Microsoft

I'm sure there are other ways to do this - but a simple Office Script that can read through the rows and output select rows based on your logic (same month and year as run/current date). 

 

See here for more details: https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration

You need Excel for web (online version) to achieve this. 

If you don't see "Automate" tab in Excel online, you can ask your administrator to turn it on (it's being rolled out to users now). 

 

I've included a script that I think will achieve what you are looking for.

It assumes that:

1. There's a sheet called Sheet1 with a table in it (only 1 table in that worksheet). You can change the sheet name your worksheet name. 

2. The table has 4 columns, with the 4th column being the date (Excel date data type). 

 

If you save this script in your Office Scripts (within Excel for web), you can then execute this script against your Excel file using Excel online's Run Script action. The output will be a 2D array with just the data rows from your Excel file. 

You can edit the script to return something else if you like (like an object array). 

Lastly, you can use this output in your next action to send invitation, etc. 

 

 

 

 

function main(workbook: ExcelScript.Workbook): string [][] {
// Get first table in Sheet1.
 let table = workbook.getWorksheet('Sheet1').getTables()[0]; 
 let data = table.getRangeBetweenHeaderAndTotal().getValues();
 let selectedRows = [];
 for (let row of data) {
   let [,,,date] = row;
   if (selectRow(date as number)){
     selectedRows.push(row);
   }
 }
  console.log(selectedRows);
  return selectedRows;
}
function selectRow(eDate: number): boolean { 
  let jsDate = getJsDateFromExcel(eDate);
  let today = new Date();
  if (jsDate.getMonth() === today.getMonth() &&
    jsDate.getFullYear() === today.getFullYear()) {
    return true
  }
  return false
}

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

 

 

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Users online (2,413)