Send a message to users that have not filled in a sharepoint

[calinpff]

Hi, everyone!

I want to design a scheduled flow that would send a teams message to the users that have not filled in their designated cell in sharepoint. 

As you can see below, both Mark and Steve have not filled in the required cells and a reminder should go out. 

If anyone can help out, please let me know. Looking forward to your answers! 

Regards,

Calin

[StretchFredrik]

Do a get items step and filter on those with empty column, below should work if im not mistaken:

[StretchFredrik]

Then below you could try something like this:

[calinpff]

Hi @StretchFredrik , thank you for replying in such a short notice. 

I encounter the following error while using the 'Get items' step. 

The 'CapTrackTEST' is the file's name within the team channel I have selected, as the 'Site Address' only takes into consideration the Teams channel. Should I use 'Get table' instead? 

Thank you!

[calinpff]

I have added a 'Get table' step between 'Recurrence' and 'Get items'. Now my flow looks like this, and even though it runs successfully, it doesn't send the message to the users. This means that I have wrongly defined the steps. 

Any inputs/suggestions are highly appreciated. Thank you! 🙂

[grantjenkins]

@calinpff Is your data stored in a SharePoint List or in an Excel File that's stored in a SharePoint Library?

If Excel, does the file change each week, or do you just remove the data from the previous week, and start with a blank table?

Also, if Excel, is your data contained in a Table - or is it just data in the spreadsheet?

[calinpff]

Hi, Grant! Thanks for replying! 

It's an Excel file that contains a table, uploaded on a Teams channel. 

The table structure does not suffer any changes, however data is needed to be filled in daily, and deleted afterwards. 

Mark, Steve, and John need to fill in daily the available hours left, and if not filled in (i.e. Mark and Steve), they should receive a reminder via Flow bot in Teams chat at a certain time. 

Their email addresses are there in order for the Flow bot to send the message to the respective users.

[grantjenkins · Accepted solution]

Hopefully this is what you're looking for.

For this example, I'm using the following Excel Table.

See full flow below. I'll go into each of the actions.

Recurrence would be set to daily at the time you want to send the emails out.

List rows present in a table would retrieve all the rows in your Table. Note that we can't apply a filter here since your Available hours header has a space in it.

Filter array will filter out any rows that have Available hours filled in, so we are left with rows that haven't been filled in.

Apply to each will iterate over each of the items in our Filter array (the rows that haven't been filled in).

Post message in a chat or channel uses the following expressions to get the Email and Name.

//Recipient - Email
item()?['Email']

//Message - Name
item()?['Name']

A sample of the message that would be sent is below.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[calinpff]

Hi, Grant! 

I was on leave and didn't have the chance to reply to you sooner, however I've just tested it and it works better than expected! 

Thank you very much for being so helpful and the fact that you took the time to work on a custom solution for my specific issue!